繼承支持
LINQ to SQL支持單表映射,其整個繼承層次結構存儲在單個數據庫表中。該表包含整個層次結構的所有可能數據列的平展聯合。(聯合是將兩個表組合成一個表的結果,組合後的表包含任一原始表中存在的行。)每行中不適用於該行所表示的實例類型的列為null。
單表映射策略是最簡單的繼承表示形式,為許多不同類別的查詢提供了良好的性能特徵,如果我們要在LINQ to SQL中實現這種映射,必須在繼承層次結構的根類中指定屬性( Attribute)和屬性(Attribute)的屬性(Property)。我們還可以使用O/R設計器來映射繼承層次結構,它自動生成了代碼。
下面為了演示下面的幾個例子,我們在O/R設計器內設計如下圖所示的類及其繼承關係。
我們學習的時候還是看看其生成的代碼吧!
具體設置映射繼承層次結構有如下幾步:
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]
留言列表