CODEWITHSUNDEEP
c#entity-frameworkentity-framework-core
Wheel Builder
Wheel Builder

Reputation: 3785

Many-to-many query in Entity Framework 7

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.

Upvotes: 5

Views: 5556

Answers (4)

nammadhu
nammadhu

Reputation: 1

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

Upvotes: 0

ocuenca
ocuenca

Reputation: 39386

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.

Upvotes: 11

esmoore68
esmoore68

Reputation: 1296

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

Upvotes: 3

Igor
Igor

Reputation: 62318

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

Upvotes: 0

Related Questions