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

1. modelBuilder.Entity<BlogPost>()
2.     .HasMany(b => b.Categories)
3.     .WithMany(c => c.CategoryBlogPosts)
4.     .Map
5.     (
6.         m =>
7.         {
8.             m.MapLeftKey("BlogPostID");
9.             m.MapRightKey("CategoryID");
10.             m.ToTable("BlogPost_Category");
11.         }
12.     );

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

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

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

Code Snippet

1. modelBuilder.Entity<BlogPost_Category>()
2.     .HasKey(it => it.BlogPostId)
3.     .HasKey(it => it.CategoryId);
4. modelBuilder.Entity<BlogPost_Category>()
5.     .HasRequired(it => it.BlogPost)
6.     .WithMany(it=>it.BlogPostCategories)
7.     .HasForeignKey(it => it.BlogPostId).WillCascadeOnDelete(false);
8. modelBuilder.Entity<BlogPost_Category>()
9.     .HasRequired(it => it.Category)
10.     .WithMany(c => c.BlogPost_Categorys)
11.     .HasForeignKey(it => it.CategoryId).WillCascadeOnDelete(false);

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

Code Snippet

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

Code Snippet

1. public virtual ICollection<BlogPost_Category> BlogPost_Categorys { get; set; }
2.  
3. public virtual IEnumerable<BlogPost> CategoryBlogPosts
4. {
5.     get
6.     {
7.         return BlogPost_Categorys
8.             .Where(it => it.IsActive == true)
9.             .Select(it => it.BlogPost);
10.     }
11. }

测试代码：

Code Snippet

1. [TestMethod]
2. public void GetBlogPostsActivedInCategory_Test()
3. {
4.     var cat = _aggBlogSiteService.GetCategory(2);
5.     cat.CategoryBlogPosts.
6.         ToList()
7.         .ForEach(p =>
8.                 {
9.                     Console.WriteLine(string.Format("ID:{0}\tTitle:{1}", p.ID,
10.                                                     p.Title));
11.                     p.Categories.ToList().ForEach(c => Console.WriteLine(
12.                         string.Format("Categories:\ttitle:{0}\tisActive:{1}"
13.                                       , c.Title
14.                                       , c.BlogPost_Categorys
15.                                             .Single(pc => pc.BlogPostId == p.ID && pc.CategoryId == c.ID).IsActive)));
16.                 });
17. }

测试数据：

结果测试编译不通过，查了下资料，原来当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

呵呵，跟dudu的一样。。。Done

示例代码