如何解决EF5.0下的Find方法生成的SQL语句使用UNION ALL？
0
悬赏园豆：50
[已关闭问题]
关闭于 2012-11-30 09:40
目前使用了EF5.0，发现使用Find方法根据key来获取实体的时候，生成的SQL语句将所有子类表UNION 起来了，查找相关文章微软认为在TPC下UNION所有子类表是正确的，但是不清楚为什么FirstOrDefault方法生成的SQL语句是没有使用UNION的，两者之间有什么区别？要怎么样使用Find方法使生成的语句不带UNION呢（使子类之间松耦合）？
代码如下：
实体类
1 public abstract class AttachmentBase 2 { 3 public int Id { get; set; } 4 public string FileName { get; set; } 5 } 6 7 public class BlogAttachment : AttachmentBase 8 { 9 public int BlogId { get; set; } 10 } 11 12 public class PhotoAttachment : AttachmentBase 13 { 14 public int PhotoId { get; set; } 15 }
EF相关类
1 public class AttachmentBaseMapper : EntityTypeConfiguration<AttachmentBase> 2 { 3 public AttachmentBaseMapper() 4 { 5 6 this.HasKey(n => n.Id); 7 8 this.Property(n => n.Id) 9 .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None) 10 .HasColumnName("Id") 11 .IsRequired(); 12 13 this.Property(n => n.FileName) 14 .HasColumnName("FileName") 15 .IsRequired(); 16 } 17 } 18 19 20 public class BlogAttachmentMapper : EntityTypeConfiguration<BlogAttachment> 21 { 22 public BlogAttachmentMapper() 23 { 24 this.Map(n => 25 { 26 n.ToTable("BlogAttachment"); 27 n.MapInheritedProperties(); 28 }); 29 this.Property(m => m.BlogId).HasColumnName("BlogID").IsRequired(); 30 } 31 } 32 33 public class PhotoAttachmentMapper : EntityTypeConfiguration<PhotoAttachment> 34 { 35 public PhotoAttachmentMapper() 36 { 37 this.Map(n => 38 { 39 n.ToTable("PhotoAttachment"); 40 n.MapInheritedProperties(); 41 }); 42 this.Property(m => m.PhotoId).HasColumnName("PhotoID").IsRequired(); 43 } 44 }
MyDbContext
1 public class MyDbContext : DbContext 2 { 3 public DbSet<BlogAttachment> BlogAttachments { get; set; } 4 public DbSet<PhotoAttachment> PhotoAttachments { get; set; } 5 6 public MyDbContext(string dbName) 7 : base(dbName) 8 { 9 this.Configuration.ProxyCreationEnabled = false; 10 this.Configuration.AutoDetectChangesEnabled = false; 11 this.Configuration.ValidateOnSaveEnabled = false; 12 Database.Initialize(false); 13 this.Configuration.LazyLoadingEnabled = true; 14 } 15 16 //注册实体映射信息 17 protected override void OnModelCreating(DbModelBuilder modelBuilder) 18 { 19 base.OnModelCreating(modelBuilder); 20 21 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); 22 23 modelBuilder.Configurations.Add(new AttachmentBaseMapper()); 24 modelBuilder.Configurations.Add(new BlogAttachmentMapper()); 25 modelBuilder.Configurations.Add(new PhotoAttachmentMapper()); 26 } 27 }
MyDbContext db = new MyDbContext("test"); #region Find db.BlogAttachments.Find(1); /** * * * [Limit1].[C4] AS [C4], [Limit1].[C5] AS [C5] FROM ( SELECT TOP (2) [UnionAll1].[Id] AS [C1], [UnionAll1].[FileName] AS [C2], CASE WHEN ([UnionAll1].[C2] = 1) THEN ''0X0X'' ELSE ''0X1X'' END AS [C3], CASE WHEN ([UnionAll1].[C2] = 1) THEN [UnionAll1].[C1] END AS [C4], CASE WHEN ([UnionAll1].[C2] = 1) THEN CAST(NULL AS int) ELSE [UnionAll1].[PhotoID] END AS [C5] FROM (SELECT [Extent1].[Id] AS [Id], [Extent1].[FileName] AS [FileName], CAST(NULL AS int) AS [C1], [Extent1].[PhotoID] AS [PhotoID], cast(0 as bit) AS [C2] FROM [dbo].[PhotoAttachment] AS [Extent1] UNION ALL SELECT [Extent2].[Id] AS [Id], [Extent2].[FileName] AS [FileName], [Extent2].[BlogID] AS [BlogID], CAST(NULL AS int) AS [C1], cast(1 as bit) AS [C2] FROM [dbo].[BlogAttachment] AS [Extent2]) AS [UnionAll1] WHERE [UnionAll1].[Id] = @p0 ) AS [Limit1]',N'@p0 int',@p0=1 * * * **/ #endregion #region FirstOrDefault db.BlogAttachments.SingleOrDefault(n => n.Id == 1); /** * * * SELECT [Limit1].[C1] AS [C1], [Limit1].[Id] AS [Id], [Limit1].[FileName] AS [FileName], [Limit1].[BlogID] AS [BlogID] FROM ( SELECT TOP (1) [Extent1].[Id] AS [Id], [Extent1].[FileName] AS [FileName], [Extent1].[BlogID] AS [BlogID], '0X0X' AS [C1] FROM [dbo].[BlogAttachment] AS [Extent1] WHERE 1 = [Extent1].[Id] ) AS [Limit1] * * **/ #endregion
可以明显看到两个SQL的区别，不知如何解决。
问题补充：
我把代码打包放到这儿了，下载地址：http://xiaocai.info/ConsoleApplication1.rar
