Many-to-many query in Entity Framework 7

Question

I am following this example which I got from http://ef.readthedocs.org/en/latest/modeling/relationships.html

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

Now my question is how would I construct my query to get posts given TagId? Something like:

public List<Post> GetPostsByTagId(int tagId)
{
    //linq query here
}

Please keep in mind this is EF7.

score 11 · Accepted Answer · edited May 23 '17 at 12:17

My first advice is change your collection properties to ICollection<T> instead of List<T>. You can find a really good explanation in this post.

Now going back to your real problem, this is how I would do your query:

public List<Post> GetPostsByTadId(int tagId)
{
    using(var context=new MyContext())
    {
      return context.PostTags.Include(p=>p.Post)
                             .Where(pt=> pt.TagId == tagId)
                             .Select(pt=>pt.Post)
                             .ToList();
    }
}

You will need to eager load Post navigation property because EF7 doesn't support lazy loading, and also, as @Igor recommended in his solution, you should include PostTags as a DbSet in your context:

 public DbSet<PostTags> PostTags { get; set; }

Explanation:

Your query start in PostTags table because is in that table where you can find all the post related with an specific tag. See the Include like a inner join with Post table. If you apply a join between PostTags and Posts filtering by TagId, you will get the columns that you need. With the Select call you are telling you only need the columns from Post table.

If you remove the Include call, it should still work. With the Include you're telling explicitly that you need to do a join, but with the Select, the Linq provider of EF is smart enough to see it needs to do implicitly a join to get the Posts columns as result.

Not sure how this works, as I am new to EF, but this query created the most efficient SQL statement: `PostTags INNER JOIN Posts...` where other answers resulted in absolutely horrendous nested SELECTs statement: `SELECT from Posts WHERE (SELECT CASE WHEN EXISTS ( SELECT 1 FROM PostTags...` Can you explain what is going on? — Wheel Builder, Apr 20 '16 at 02:27

score 3 · Answer 2 · answered Apr 19 '16 at 17:43

3

db.Posts.Where(post => post.PostTags.Any(pt => pt.TagId == tagId));

answered Apr 19 '16 at 17:43

esmoore68

1,276
1
8
16

This works and is probably the easiest solution, but looking at the sql profiler, this created a rather complicated and inefficient query with the CASE statement and nested selects. Not sure why... – Wheel Builder Apr 20 '16 at 02:09
@WheelBuilder Because EF Core still lacks proper LINQ-to-SQL translation and non-straight forrward queries are not translated to SQL and therefore executed in memory on client side performing N smaller simplier SQL queries. For example same happening with GROUP BY that for now to-SQL translation not supported (it performs in-memory making simple SQL query for every entry in the table) and only expected to be implemented in EF Core v1.1 and not in fully. – Oleksii Vynnychenko Aug 16 '16 at 21:18
Only this solution allowed me to fetch the children objects (Tags) for Posts. But you need to rewrite it with Includes. – Mikhail M Jul 11 '18 at 09:12

score 0 · Answer 3 · answered Apr 19 '16 at 17:41

This is not really specific to EF7.

You could extend your DbContext to include PostTags

class MyContext : DbContext
{
    public DbSet<PostTags> PostTags { get; set; }

Then your query

db.Posts.Where(post => db.PostTags.Any(pt => pt.PostId == post.PostId && pt.TagId == tagId))
.Select(post => post);

score 0 · Answer 4 · answered Feb 09 '19 at 08:43

0

include theinclude both works fine... but only problem is intellisense not identifying or showing the methods just type and then proceed all works fine ...

            var res = await _context.Diseases.Include(x => x.Disease2Symptom)
            .ThenInclude(z => z.Symptom).ToListAsync();

answered Feb 09 '19 at 08:43

Madhusudhan V Indian

352
5
11

This doesn't answer the question. – Gert Arnold Feb 09 '19 at 10:26

Many-to-many query in Entity Framework 7

4 Answers4

Linked

Related