Many-to-many query in Entity Framework 7

c# entity-framework entity-framework-core

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.

1
5
10/19/2017 1:18:16 PM

Accepted Answer

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.

10
5/23/2017 12:17:25 PM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow