Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 773
Wheel Builder Avatar asked Apr 19 '16 17:04

Wheel Builder


People also ask

How do I create a many-to-many relationship in Entity Framework?

A many-to-many relationship is defined in code by the inclusion of collection properties in each of the entities - The Categories property in the Book class, and the Books property in the Category class: public class Book. { public int BookId { get; set; }

What is another way to configure a many-to-many relationship in OnModelCreating method?

Step 1 – Add foreign key property to other entities, in the joining entity. Step 2 – Add collection navigation property on the other entities towards the joining entity. Step 3 – Next create DB Context OnModelCreating() method configure both the foreign keys in the joining entity as a composite key using Fluent API.

What is OnModelCreating in Entity Framework?

OnModelCreating(ModelBuilder) Method (Microsoft. EntityFrameworkCore) Override this method to further configure the model that was discovered by convention from the entity types exposed in DbSet<TEntity> properties on your derived context.

How will you create relationship between tables in Entity Framework?

You can create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.


1 Answers

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.

like image 121
octavioccl Avatar answered Sep 29 '22 00:09

octavioccl