首页 新闻 会员 周边 捐助

如何解决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

小蔡的主页 小蔡 | 菜鸟二级 | 园豆:214
提问于:2012-09-28 15:12
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册