CODEWITHSUNDEEP
.netentity-framework.net-coreentity-framework-core
Chris Becke
Chris Becke

Reputation: 36026

Performing an efficient query on a many to many relationship in EF Core

In Entity Framework Core one can setup a many-to-many relationship via a join table.

I don't know how to query a relationship like this in an efficient way. Given the reference relationships below, I have, a Post entity and the PostTags collection is null. I want to load all the Tags associated with the Post and the best I have so far is something that looks like this:

void GetAllTags(Post somePost)
{
    await dbContext.Entry(somePost)
        .Collection(p => p.PostTags)
        .LoadAsync();
    foreach(var postTag in somePost.PostTags)
    {
        await dbContext.Entry(postTag)
            .Reference(p => p.Tag)
            .LoadAsync();
    }
}

I really would prefer to return an ICollection<Tag> via a single query to the DB.

This is the reference implementation from Relationships - EF Core | Microsoft Docs

class MyContext : DbContext
{
    public DbSet<Post> Posts { get; set; }
    public DbSet<Tag> Tags { get; set; }

    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);
    }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public List<PostTag> PostTags { get; set; }
}

public class Tag
{
    public string TagId { get; set; }

    public List<PostTag> PostTags { get; set; }
}

public class PostTag
{
    public int PostId { get; set; }
    public Post Post { get; set; }

    public string TagId { get; set; }
    public Tag Tag { get; set; }
}

Upvotes: 1

Views: 558

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205589

If all you need is to get a list of the tags associated with a post, you can use simple projection query. Just make sure to start with a DbSet<> (i.e. EF Core IQueryable<>) rather than materialized object / collection, e.g.

ICollection<Tag> GetAllTags(Post somePost)
{
    return await dbContext.Set<PostTag>()
        .Where(pt => pt.PostId == somePost.PostId)
        .Select(pt => pt.Tag)
        .ToListAsync();
}

If you indeed want to load them into the passed Post entity instance, you can use a combination of explicit and eager loading via Query method:

void LoadAllTags(Post somePost)
{
    await dbContext.Entry(somePost)
        .Collection(p => p.PostTags)
        .Query()
        .Include(pt => pt.Tag)
        .LoadAsync();
}

Upvotes: 3

Related Questions