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.
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; }
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With