今天在博问中看到一个关于 EF Core 的提问 ef core 2.0 多对多查询的问题，由于还没使用过 EF Core 的多对多映射，于是参考 EF Core 帮助文档快速写了个 .net core 控制台程序（基于 EF Core In-Memory Database）实验了一下。
实体类的定义：
1）Post
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; } public List<PostTag> PostTags { get; set; }
}
2）Tag
public class Tag
{
public int TagId { get; set; }
public string TagName { get; set; } public List<PostTag> PostTags { get; set; }
}
3）PostTag
public class PostTag
{
public int PostId { get; set; }
public Post Post { get; set; } public int TagId { get; set; }
public Tag Tag { get; set; }
}
DbContext 的定义与映射配置：
public class MyDbContext : DbContext
{
public DbSet<Post> Posts { get; set; }
public DbSet<Tag> Tags { get; set; } public MyDbContext(DbContextOptions<MyDbContext> options)
: base(options)
{ } protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<PostTag>()
.HasKey(t => new { t.PostId, t.TagId }); modelBuilder.Entity<PostTag>()
.HasOne(pt => pt.Post)
.WithMany(p => p.PostTags)
.HasForeignKey(pt => pt.PostId); modelBuilder.Entity<PostTag>()
.HasOne(pt => pt.Tag)
.WithMany(t => t.PostTags)
.HasForeignKey(pt => pt.TagId);
}
}
控制台程序 Main 方法：
class Program
{
static async Task Main(string[] args)
{
IServiceCollection services = new ServiceCollection();
services.AddDbContext<MyDbContext>(options =>
{
options.UseInMemoryDatabase("blog_sample");
}); IServiceProvider sp = services.BuildServiceProvider(); var writeDbContext = sp.GetService<MyDbContext>(); var tag = new Tag
{
TagName = "efcore"
}; var post = new Post
{
Title = "test title",
Content = "test body"
}; var postTag = new PostTag
{
Tag = tag,
Post = post
}; writeDbContext.Add(postTag);
writeDbContext.SaveChanges(); var readDbContext = sp.GetService<MyDbContext>();
var queryPost = await readDbContext.Posts
.Include(p => p.PostTags)
.ThenInclude(pt => pt.Tag)
.FirstOrDefaultAsync();
Console.WriteLine(queryPost.PostTags[].Tag.TagName);
}
}
查询时需要使用 ThenInclude ，但这里使用 ThenInclude 时 VS2017 的智能感知有个bug ，pt.Tag 感知不出来，详见 Include->ThenInclude for a collection 。
如果使用 SQL Server ，会生成下面的 SQL 语句：
SELECT TOP(1) [p].[PostId], [p].[Content], [p].[Title]
FROM [Posts] AS [p]
ORDER BY [p].[PostId]
SELECT [p.PostTags].[PostId], [p.PostTags].[TagId], [p.Tag].[TagId], [p.Tag].[TagName]
FROM [PostTag] AS [p.PostTags]
INNER JOIN [Tags] AS [p.Tag] ON [p.PostTags].[TagId] = [p.Tag].[TagId]
INNER JOIN (
SELECT TOP(1) [p0].[PostId]
FROM [Posts] AS [p0]
ORDER BY [p0].[PostId]
) AS [t] ON [p.PostTags].[PostId] = [t].[PostId]
ORDER BY [t].[PostId]