繼承支持

LINQ to SQL支持單表映射,其整個繼承層次結構存儲在單個數據庫表中。該表包含整個層次結構的所有可能數據列的平展聯合。(聯合是將兩個表組合成一個表的結果,組合後的表包含任一原始表中存在的行。)每行中不適用於該行所表示的實例類型的列為null。

單表映射策略是最簡單的繼承表示形式,為許多不同類別的查詢提供了良好的性能特徵,如果我們要在LINQ to SQL中實現這種映射,必須在繼承層次結構的根類中指定屬性( Attribute)和屬性(Attribute)的屬性(Property)。我們還可以使用O/R設計器來映射繼承層次結構,它自動生成了代碼。

下面為了演示下面的幾個例子,我們在O/R設計器內設計如下圖所示的類及其繼承關係。

LINQ21  

我們學習的時候還是看看其生成的代碼吧!

具體設置映射繼承層次結構有如下幾步:

1. 根類添加TableAttribute屬性。

2. 為層次結構中的每個類添加InheritanceMappingAttribute屬性,同樣是添加到根類中。每個InheritanceMappingAttribute屬性,定義一個Code屬性和一個Type屬性。Code屬性的值顯示在數據庫表的IsDiscriminator列中,用來指示該行數據所屬的類或子類。Type屬性值指定鍵值所表示的類或子類。

3. 僅在其中一個InheritanceMappingAttribute屬性上,添加一個IsDefault屬性用來在數據庫表中的鑑別器值在繼承映射中不與任何Code值匹配時指定回退映射。

4. 為ColumnAttribute屬性添加一個IsDiscriminator屬性來表示這是保存Code值的列。

下面是這張圖生成的代碼的框架(由於生成的代碼太多,我刪除了很多“枝葉”,僅僅保留了主要的框架用於指出其實質的東西):

[ Table (Name = "dbo.Contacts" )]
[ InheritanceMapping (Code = "Unknown" , Type = typeof ( Contact ),
                    IsDefault = true )]
[ InheritanceMapping (Code = "Employee" , Type = typeof ( EmployeeContact ))]
[ InheritanceMapping (Code = "Supplier" , Type = typeof ( SupplierContact ))]
[ InheritanceMapping (Code = "Customer" , Type = typeof ( CustomerContact ))]
[ InheritanceMapping (Code = "Shipper" , Type = typeof ( ShipperContact ))]
 public partial class Contact :
 INotifyPropertyChanging , INotifyPropertyChanged
 {
    [ Column (Storage = "_ContactID" ,IsPrimaryKey = true ,
    IsDbGenerated = true )]
     public int ContactID{ }
    [ Column (Storage = "_ContactType" ,IsDiscriminator = true )]
     public string ContactType{ }
}
public abstract partial class FullContact : Contact { }
 public partial class EmployeeContact : FullContact { }
 public partial class SupplierContact : FullContact { }
 public partial class CustomerContact : FullContact { }
 public partial class ShipperContact : Contact { }

1.一般形式

日常我們經常寫的形式,對單表查詢。

var cons = from c in db.Contacts                       
            select c;
 foreach ( var con in cons) {
     Console .WriteLine( "Company name: {0}" , con.CompanyName);
     Console .WriteLine( "Phone: {0}" , con.Phone);
     Console .WriteLine( "This is a {0}" , con.GetType());
}

2.OfType形式

這裡我僅僅讓其返回顧客的聯繫方式。

var cons = from c in db.Contacts.OfType< CustomerContact >()
            select c;

初步學習,我們還是看看生成的SQL語句,這樣容易理解。在SQL語句中查詢了ContactType為Customer的聯繫方式。

SELECT [t0].[ContactType], [t0].[ContactName], [t0].[ContactTitle],
[t0].[Address],[t0].[City], [t0].[Region], [t0].[PostalCode], 
[t0].[Country], [t0].[Fax],[t0].[ContactID], [t0].[CompanyName], 
[t0].[Phone] FROM [dbo].[Contacts] AS [t0]
 WHERE ([t0].[ContactType] = @p0) AND ([t0].[ContactType] IS NOT NULL )
 -- @p0: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Customer]

3.IS形式

這個例子查找一下發貨人的聯繫方式。

var cons = from c in db.Contacts
            where c is ShipperContact 
           select c;

生成的SQL語句如下:

查詢了ContactType為Shipper的聯繫方式。大致一看好像很上面的一樣,其實這裡查詢出來的列多了很多。實際上是Contacts表的全部字段。

SELECT [t0].[ContactType], [t0].[ContactID], [t0].[CompanyName],
[t0].[Phone],[t0].[HomePage], [t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City],
[t0].[Region], [t0].[PostalCode], [t0].[Country],
[t0].[Fax],[t0].[PhotoPath], [t0].[Photo], [t0].[Extension]
FROM [dbo].[Contacts] AS [t0] WHERE ([t0].[ContactType] = @p0)
 AND ([t0].[ContactType] IS NOT NULL )
 -- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Shipper]

4.AS形式

這個例子就通吃了,全部查找了一番。

var cons = from c in db.Contacts
            select c as FullContact;

生成SQL語句如下:查詢整個Contacts表。

SELECT [t0].[ContactType], [t0].[HomePage], [t0].[ContactName],
[t0].[ContactTitle],[t0].[Address], [t0].[City],
[t0].[Region], [t0].[PostalCode], [t0].[Country], 
[t0].[Fax], [t0].[ContactID], [t0].[CompanyName], 
[t0].[Phone], [t0].[PhotoPath],[t0].[Photo], [t0].[Extension]
 FROM [dbo].[Contacts] AS [t0]

5.Cast形式

使用Case形式查找出在倫敦的顧客的聯繫方式。

var cons = from c in db.Contacts
            where c.ContactType == "Customer" &&
                     (( CustomerContact )c).City == "London" 
           select c;

生成SQL語句如下,自己可以看懂了。

SELECT [t0].[ContactType], [t0].[ContactID], [t0].[CompanyName],
[t0].[Phone], [t0].[HomePage],[t0].[ContactName],
[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],
[t0].[PostalCode], [t0].[Country], [t0].[Fax], [t0].[PhotoPath],
[t0].[Photo], [t0].[Extension] FROM [dbo].[Contacts] AS [t0]
 WHERE ([t0].[ContactType] = @p0) AND ([t0].[City] = @p1)
 -- @p0: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Customer]
-- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

6.UseAsDefault形式

當插入一條記錄時,使用默認的映射關係了,但是在查詢時,使用繼承的關係了。具體看看生成的SQL語句就直截了當了。

//插入一條數據默認使用正常的映射關係
Contact contact = new Contact ()
{
    ContactType = null ,
    CompanyName = "Unknown Company" ,
    Phone = "333-444-5555"
};
db.Contacts.InsertOnSubmit(contact);
db.SubmitChanges();
//查詢一條數據默認使用繼承映射關係
var con =
    ( from c in db.Contacts
      where c.CompanyName == "Unknown Company" &&
                            c.Phone == "333-444-5555"
      select c).First();

生成SQL語句如下:

INSERT INTO [dbo].[Contacts]([ContactType], [CompanyName],
[Phone]) VALUES (@p0, @p1, @p2)
 SELECT TOP (1) [t0].[ContactType], [t0].[ContactID],
[t0].[CompanyName], [t0].[Phone],[t0].[HomePage],
[t0].[ContactName], [t0].[ContactTitle], [t0].[Address],
[t0].[City],[t0].[Region], [t0].[PostalCode], [t0].[Country],
[t0].[Fax], [t0].[PhotoPath],[t0].[Photo], [t0].[Extension]
FROM [dbo].[Contacts] AS [t0]
 WHERE ([t0].[CompanyName] = @p0) AND ([t0].[Phone] = @p1)
 -- @p0: Input NVarChar (Size = 15; Prec = 0; Scale = 0)
    [Unknown Company]
-- @p1: Input NVarChar (Size = 12; Prec = 0; Scale = 0)
    [333-444-5555]

7.插入新的記錄

這個例子說明如何插入發貨人的聯繫方式的一條記錄。

 //1.在插入之前查詢一下,沒有數據
 var ShipperContacts =
     from sc in db.Contacts.OfType< ShipperContact >()
     where sc.CompanyName == "Northwind Shipper"
     select sc;
 //2.插入數據
 ShipperContact nsc = new ShipperContact ()
 {
    CompanyName = "Northwind Shipper" ,
    Phone = "(123)-456-7890"
 };
 db.Contacts.InsertOnSubmit(nsc);
 db.SubmitChanges();
 //3.查詢數據,有一條記錄
 ShipperContacts =
     from sc in db.Contacts.OfType< ShipperContact >()
     where sc.CompanyName == "Northwind Shipper"
     select sc;
 //4.刪除記錄
 db.Contacts.DeleteOnSubmit( nsc);
 db.SubmitChanges();

生成SQL語句如下:

SELECT COUNT (*) AS [value] FROM [dbo].[Contacts] AS [t0]
WHERE ([t0].[CompanyName] = @p0) AND ([t0].[ContactType] = @p1) 
AND ([ t0].[ContactType] IS NOT NULL )
-- @p0: Input NVarChar [Northwind Shipper]
-- @p1: Input NVarChar [Shipper] 
INSERT INTO [dbo].[Contacts]([ContactType], [CompanyName], [Phone])
VALUES (@p0, @p1, @p2)
-- @p0: Input NVarChar [Shipper]
-- @p1: Input NVarChar [Northwind Shipper]
-- @p2: Input NVarChar [(123)-456-7890] 
SELECT COUNT (*) AS [value] FROM [dbo].[Contacts] AS [t0]
WHERE ([t0].[CompanyName] = @p0) AND ([t0].[ContactType] = @p1)
AND ([t0].[ContactType] IS NOT NULL )
-- @p0: Input NVarChar [Northwind Shipper]
-- @p1: Input NVarChar [Shipper]
DELETE FROM [dbo].[Contacts] WHERE ([ContactID] = @p0) AND
 ([ContactType] = @p1) AND ([CompanyName] = @p2) AND ([Phone] = @p3)
-- @p0: Input Int [159]
-- @p1: Input NVarChar [Shipper]
-- @p2: Input NVarChar [Northwind Shipper]
-- @p3: Input NVarChar [(123)-456-7890]
-- @p4: Input NVarChar [Unknown]
-- @p5: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Supplier]
-- @p6: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Shipper]
-- @p7: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Employee]
-- @p8: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Customer]
文章標籤
創作者介紹

吉米.NET

Jimmy 發表在 痞客邦 PIXNET 留言(0) 人氣()