每个继承类一个表
1、每个继承一个表场景，适用与主表及主表扩展表情景
主表与扩展表是1：0...1关系。
2.创建表
CREATE TABLE [Chapter2].[Business]( [BusinessId] [int] IDENTITY(1,1) primary key, [Name] [varchar](50) NOT NULL, [LicenseNumber] [varchar](20) NOT NULL ) GO CREATE TABLE [Chapter2].[eCommerce]( [BusinessId] [int] PRIMARY Key , [URL] [varchar](1024) NULL ) ALTER TABLE [Chapter2].[eCommerce] WITH CHECK ADD CONSTRAINT [FK_eCommerce_Business] FOREIGN KEY([BusinessId]) REFERENCES [Chapter2].[Business] ([BusinessId]) GO GO CREATE TABLE [Chapter2].[Retail]( [BusinessId] [int] NOT NULL, [Address] [varchar](50) NOT NULL, [City] [varchar](50) NOT NULL, [State] [varchar](2) NOT NULL, [ZIPCode] [varchar](9) NOT NULL ) GO ALTER TABLE [Chapter2].[Retail] WITH CHECK ADD CONSTRAINT [FK_Retail_Business] FOREIGN KEY([BusinessId]) REFERENCES [Chapter2].[Business] ([BusinessId]) GO
3、创建项目，添加EF引用，注意链接字符串
4、添加类
public class BusinessContext : DbContext { public DbSet<Business> Businesses { get; set; } public BusinessContext() : base("EFRecipesEntities") { } } [Table("Business", Schema = "Chapter2")] public class Business { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int BusinessId { get; set; } public string Name { get; set; } public string LicenseNumber { get; set; } } [Table("eCommerce", Schema = "Chapter2")] public class eCommerce : Business { public string URL { get; set; } } [Table("Retail", Schema = "Chapter2")] public class Retail : Business { public string Address { get; set; } public string City { get; set; } public string State { get; set; } public string ZIPCode { get; set; } }
5、修改Main方法代码如下
static void Main(string[] args) { using (var context = new BusinessContext()) { var business = new Business() { Name = "Corner Dry Cleaning", LicenseNumber = "100x1" }; context.Businesses.Add(business); var retail = new Retail { Name = "Shop and Save", LicenseNumber = "200C", Address = "101 Main", City = "Anytown", State = "TX", ZIPCode = "76106" }; context.Businesses.Add(retail); var web = new eCommerce() { Name = "BuyNow.com", LicenseNumber = "300AB", URL = "www.buynow.com" }; context.Businesses.Add(web); context.SaveChanges(); } using (var context = new BusinessContext()) { Console.WriteLine("\n--- All Businesses ---"); foreach (var b in context.Businesses) { Console.WriteLine($"{b.Name}(#{b.LicenseNumber})"); } Console.WriteLine("\n --- Retail Businesses ---"); foreach (var r in context.Businesses.OfType<Retail>()) { Console.WriteLine($"{r.Name},(#{r.LicenseNumber})"); Console.WriteLine(r.Address); Console.WriteLine($"{r.City},{r.State}{r.ZIPCode}"); } Console.WriteLine("\n--- eCommerce Businesses ---"); foreach (var e in context.Businesses.OfType<eCommerce>()) { Console.WriteLine("{0} (#{1})", e.Name, e.LicenseNumber); Console.WriteLine("Online address is: {0}", e.URL); } Console.ReadKey(); } }
6、运行结果
7、运行时使用SQlProfile 监控语句如下
SELECT [Extent2].[BusinessId] AS [BusinessId], [Extent2].[Address] AS [Address], [Extent2].[City] AS [City], [Extent2].[State] AS [State], [Extent2].[ZIPCode] AS [ZIPCode], cast(1 as bit) AS [C1] FROM [Chapter2].[Retail] AS [Extent2] SELECT [Extent3].[BusinessId] AS [BusinessId], [Extent3].[URL] AS [URL], cast(1 as bit) AS [C1] FROM [Chapter2].[eCommerce] AS [Extent3] SELECT CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN '0X' WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN '0X0X' ELSE '0X1X' END AS [C1], [Extent1].[BusinessId] AS [BusinessId], [Extent1].[Name] AS [Name], [Extent1].[LicenseNumber] AS [LicenseNumber], CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN [Project2].[URL] END AS [C2], CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [Project1].[Address] END AS [C3], CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [Project1].[City] END AS [C4], CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [Project1].[State] END AS [C5], CASE WHEN (( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [Project1].[ZIPCode] END AS [C6] FROM [Chapter2].[Business] AS [Extent1] LEFT OUTER JOIN (SELECT [Extent2].[BusinessId] AS [BusinessId], [Extent2].[Address] AS [Address], [Extent2].[City] AS [City], [Extent2].[State] AS [State], [Extent2].[ZIPCode] AS [ZIPCode], cast(1 as bit) AS [C1] FROM [Chapter2].[Retail] AS [Extent2] ) AS [Project1] ON [Extent1].[BusinessId] = [Project1].[BusinessId] LEFT OUTER JOIN (SELECT [Extent3].[BusinessId] AS [BusinessId], [Extent3].[URL] AS [URL], cast(1 as bit) AS [C1] FROM [Chapter2].[eCommerce] AS [Extent3] ) AS [Project2] ON [Extent1].[BusinessId] = [Project2].[BusinessId]
总结：EF 查询时为扩展表添加bit类型字段，存在时为1，根据是否存在该字段来确定扩展表信息是否存在。
通过Profile监控看到该方法使用了临时表查询，数据量大时会存在性能问题。
可借鉴方法临时表中添加bit字段，通过bit字段是否为null，来决定是否操作此记录。
