EF多对多关系中如何利用关系表（EF4.1 Many to Many involves junction table details）

请先看dudu的文章http://www.cnblogs.com/dudu/archive/2011/07/09/entity_framework_many_to_many.html

一般的多对多关系数据库表：

Mapping代码：

Code Snippet

modelBuilder.Entity<BlogPost>()
    .HasMany(b => b.Categories)
    .WithMany(c => c.CategoryBlogPosts)
    .Map
    (
        m =>
        {
            m.MapLeftKey("BlogPostID");
            m.MapRightKey("CategoryID");
            m.ToTable("BlogPost_Category");
        }
    );

问题：如果以上满足不了我们的需求，比如我需要再关系表中增加对当前关系的其它信息，比如isactive,只是个举例，实际博客分类中也许并不需要这个字段。那么在CategoryA.CategoryBlogPosts中我想选取IsActive为true的文章就很痛苦了。

根本原因在于这里的多对多映射叫做direct multi to multi直接隐藏了关系表，我自然想到了用两个一对多来实现，本来多对多就是这样来的。

那么映射就变成下面这样了：

Code Snippet

modelBuilder.Entity<BlogPost_Category>()
    .HasKey(it => it.BlogPostId)
    .HasKey(it => it.CategoryId);
modelBuilder.Entity<BlogPost_Category>()
    .HasRequired(it => it.BlogPost)
    .WithMany(it=>it.BlogPostCategories)
    .HasForeignKey(it => it.BlogPostId).WillCascadeOnDelete(false);
modelBuilder.Entity<BlogPost_Category>()
    .HasRequired(it => it.Category)
    .WithMany(c => c.BlogPost_Categorys)
    .HasForeignKey(it => it.CategoryId).WillCascadeOnDelete(false);

这里修改了之间关联的属性：

Code Snippet

public virtual ICollection<BlogPost_Category> BlogPostCategories { get; set; }
 
public virtual IEnumerable<Category> Categories
{
    get { return BlogPostCategories.Where(it => it.IsActive == true).Select(it => it.Category); }
}

Code Snippet

public virtual ICollection<BlogPost_Category> BlogPost_Categorys { get; set; }
 
public virtual IEnumerable<BlogPost> CategoryBlogPosts
{
    get
    {
        return BlogPost_Categorys
            .Where(it => it.IsActive == true)
            .Select(it => it.BlogPost);
    }
}

测试代码：

Code Snippet

[TestMethod]
public void GetBlogPostsActivedInCategory_Test()
{
    var cat = _aggBlogSiteService.GetCategory(2);
    cat.CategoryBlogPosts.
        ToList()
        .ForEach(p =>
                {
                    Console.WriteLine(string.Format("ID:{0}\tTitle:{1}", p.ID,
                                                    p.Title));
                    p.Categories.ToList().ForEach(c => Console.WriteLine(
                        string.Format("Categories:\ttitle:{0}\tisActive:{1}"
                                      , c.Title
                                      , c.BlogPost_Categorys
                                            .Single(pc => pc.BlogPostId == p.ID && pc.CategoryId == c.ID).IsActive)));
                });
}

测试数据：

结果测试编译不通过，查了下资料，原来当BlogPostID和CategoryID一起做主键时就无法再作为外键，感觉没道理啊，相信将来Ef会改进，原因也不清楚，有时间得好好研究，另外推荐一本书：http://www.ppurl.com/2011/06/entity-framework-4-in-action.html

可惜我是忠实的CodeFirst Fans，等下个版本了。

新建另一个键当主键，不过显然是多余的，无所谓了（第三幅图中的ID就此由来~）。

测试结果：

最重要的是SQL脚本：

exec sp_executesql N'SELECT

[Project2].[ID] AS [ID],

[Project2].[Title] AS [Title],

[Project2].[C1] AS [C1],

[Project2].[ID1] AS [ID1],

[Project2].[BlogPostId] AS [BlogPostId],

[Project2].[CategoryId] AS [CategoryId],

[Project2].[IsActive] AS [IsActive]

FROM ( SELECT

	[Limit1].[ID] AS [ID],

	[Limit1].[Title] AS [Title],

	[Extent2].[ID] AS [ID1],

	[Extent2].[BlogPostId] AS [BlogPostId],

	[Extent2].[CategoryId] AS [CategoryId],

	[Extent2].[IsActive] AS [IsActive],

	CASE WHEN ([Extent2].[ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]

	FROM (SELECT TOP (1)

 [Extent1].[ID] AS [ID],

 [Extent1].[Title] AS [Title]

 FROM [dbo].[Category] AS [Extent1]

 WHERE [Extent1].[ID] = @p__linq__0 ) AS [Limit1]

	LEFT OUTER JOIN [dbo].[BlogPost_Category] AS [Extent2] ON [Limit1].[ID] = [Extent2].[CategoryId]

) AS [Project2]

ORDER BY [Project2].[ID] ASC, [Project2].[C1] ASC',N'@p__linq__0 int',@p__linq__0=2