初試 Entity Framework Core 的多對多對映

dudu發表於2018-01-27
Framework

今天在博問中看到一個關於 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[0].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]

相關文章