Querying many to many table in EF Core/LINQ

Question

I have three tables: Posts, Tags and PostTags (link table between Post and Tag). How can I write a query to get all Posts by a TagId?

DB structure:

public class Post {
    public string Id {get;set;}
    public string Content {get;set;}
    public List<PostTag> PostTags {get;set;}
}

public class Tag {
    public string Id {get;set;}
    public string Name {get;set;}
    public List<PostTag> PostTags {get;set;}
}

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

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

Relationships:

builder.Entity<PostTag>()
    .HasKey(x => new { x.PostId, x.TagId });

builder.Entity<PostTag>()
    .HasOne(st => st.Post)
    .WithMany(s => s.PostTags)
    .HasForeignKey(st => st.PostId);

builder.Entity<PostTag>()
    .HasOne(st => st.Tag)
    .WithMany(s => s.PostTags)
    .HasForeignKey(st => st.TagId);

See https://stackoverflow.com/a/36725681/861716 – Gert Arnold Jul 12 '20 at 07:52 — Gert Arnold, Jul 12 '20 at 07:52
Thanks that lead me to what I was looking for. – blankface Jul 14 '20 at 07:00 — blankface, Jul 14 '20 at 07:00

score 1 · Answer 1 · answered Jul 11 '20 at 13:55

If you've followed the entity framework code first conventions, there are two methods to query "Posts with their Tags"

The easy way: Use the virtual ICollection<Tag> to get the tags of each post.
Do the (group-)join yourself.

Use the irtual ICollection

Your classes will be similar to the following:

class Post
{
    public int Id {get; set;}
    ... // other properties

    // every Post has zero or more Tags (many-to-many)
    public virtual ICollection<Tag> Tags {get; set;}
}

class Tag
{
    public int Id {get; set;}
    ... // other properties

    // every Tag is used by zero or more Posts (many-to-many)
    public virtual ICollection<Post> Posts {get; set;}
}

This is all that entity framework needs to know the many-to-many relation between Posts and Tags. You even don't have to mention the junction table, entity framework will create a standard table for you, and use it whenever needed. Only if you want non-standard names for tables and or columns, you need Attributes or fluent API.

In entity framework, the columns of the tables are represented by the non-virtual properties; the virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)

To get all (or some) Posts, each with all (or some of) their Tables, you can use the virtual ICollection:

var postsWithTheirTags = dbContext.Posts
    // only if you don't want all Posts:
    .Where(post => ...)

    .Select(post => new
    {
        // Select only the Post properties that you plan to use:
        Id = post.Id,
        Author = post.Author,
        ...

        Tags = post.Tags.Select(tag => new
        {
             // again: only the properties that you plan to use
             Id = tag.Id,
             Text = tag.Text,
             ...
        })
        .ToList(),
    });

Entity framework knows your relation and will automatically create a Group-join for you using the proper junction table.

This solutions seems to me the most natural one.

Do the GroupJoin yourself

For this you need to have access to the junction table, you'll have to mention it in your dbContext, and use fluent API to tell entity framework that this is the junction table for the many-to-many relation between Posts and Tags.

var postsWithTheirTags = dbContext.Posts.GroupJoin(dbContext.PostTags,

    post => post.Id,              // from every Post take the primary key
    postTag => postTag.PostId     // from every PostTag take the foreign key to Post

    (post, postTagsOfThisPost) => new
    {
        // Post properties:
        Id = post.Id,
        Title = post.Title,
        ...

        Tags = dbContext.Tags.Join(postTagsOfThisPost,

        tag => tag.Id                // from every Tag take the primary key
        postTag => postTag.TagId     // from every postTagOfThisPost take the foreign key

        (tag, postTagfThisPostAndThisTag) => new
        {
            Id = tag.Id,
            Text = tag.Text,
            ...
        })
        .ToList(),
    });

The Post object doesn't have a navigation property to Tags, so I'm not sure how `post.Tags` would work. — blankface, Jul 12 '20 at 07:37
Somehow, someone in your team was able to convince your project leader that it would be a good idea to deviate from EF's standard method of implementing a many-to-many relation. Either convert your project leader the importance of reverting the code to the proper implementation, or stick with the deviated code. — Harald Coppoolse, Jul 13 '20 at 17:47
The example in the official doc also follows the same convention as I did, ie, Post and Tag classes only have references to the link table PostTag https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#many-to-many — blankface, Jul 14 '20 at 05:14
Well maybe you're still in time to propose to follow the conventions, if there are no reasons to deviate from them. If no can do, consider creating an extension functions to query "Posts with their Tags" and "Tags with their Posts", that hide your internal data structure. If your return `IQueryable<...>`, then others can use these methods in their LINQ queries. — Harald Coppoolse, Jul 14 '20 at 06:57
Still not sure where the topic of convention came from, since I did follow the convention. But thanks for the extension method idea, I can see it being useful. — blankface, Jul 14 '20 at 06:59
[EF6 Code First Conventions](https://www.entityframeworktutorial.net/code-first/code-first-conventions.aspx)
And [Many-to-Many Relationship by Following Conventions](https://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx) The latter advises to add a `virtual ICollection<...>` on both sides of the Many-To-Many — Harald Coppoolse, Jul 14 '20 at 08:29

xxramm · Answer 2 · 2020-07-11T14:04:24.433

0

You can try this:

public List<Posts> GetPosts(string needTagID)
{
    var dataQuery = from tags in _db.Tags
                    where needTagID == tags.Id
                    join postTags in _db.PostTags on tags.Id equals postTags.TagId
                    join posts in _db.Posts on postTags.PostId equals posts.Id
                    select posts;
    
    var data = dataQuery.ToList();
}

edited Jul 11 '20 at 14:04

answered Jul 11 '20 at 13:23

xxramm

149
8

I'm not querying on a list of tag Ids `tagsIDsList` but a single one. I want all Posts that have the provided TagId. – blankface Jul 11 '20 at 13:28
Ok i corrected. – xxramm Jul 11 '20 at 13:30
Can you give an explanation? – blankface Jul 11 '20 at 13:31
SQL will perform two join operations. This will get all table joins. Next, the necessary condition needTagID == tags.TagID will be satisfied. – xxramm Jul 11 '20 at 13:34
Post doesn't have reference to TagId so `posts.TagID` will not work – blankface Jul 11 '20 at 13:44
You did not upload the database structure. But there should be PostID. excuse me – xxramm Jul 11 '20 at 13:47
Sorry, updated original post with DB structure. – blankface Jul 11 '20 at 13:54
Why string Id and Guid PostId? Guid TagId and string Id? – xxramm Jul 11 '20 at 14:05
Apologies, posted in a hurry. Fixed now. – blankface Jul 12 '20 at 07:41

Querying many to many table in EF Core/LINQ

2 Answers2

Use the irtual ICollection

Do the GroupJoin yourself