How to query many-to-many releationship in EF Core
Solution 1
EF Core won't load related properties automatically, so you'll need to explicitly do this, but something like the following should do the trick:
var result = context.Begrip
.Include(x => x.Categories)
.ThenInclude(x => x.category);
Note, intellisense doesn't always work on .ThenInclude
at the moment, but the code should still compile even if it gets a red underline.
If you're returning this to the view or an API, you'll likely want to map it to a DTO so you don't have to deal with .Categories[0].category.Name
etc.
Solution 2
If you need to filter a Many-to-Many relationship describe below I recomend to use a LinQ Enumerable Any method like this:
return result.Where(x => x.Categories.Any(c => c.category == categoryId));
To return a filtered list of entities related by a specific category.
EntityFrameworkCore Relationship query example
Solution 3
Extending @Richard's answer :
I noticed in Visual Studio 2017 15.5.6 when I do following:
return _context.Begrip
.Include(x => x.Categories)
.ThenInclude(y => y.<nothing typed in here yet>)
IntelliSense at first tells me that y if of type ICollection of BegripCategory presenting methods suitable for collections what is confusing especially that when I start typing "category" (in place of "nothing typed in here yet") IntelliSense changes as if we were dealing with only a single instance instead of ICollection
Just a tiny remark, but I hope it will help to save a few minutes time confusion.
Related videos on Youtube
JessengBijleng
Updated on May 06, 2020Comments
-
JessengBijleng almost 4 years
I'm using .NET Core and EF Core for a web project. I'm struggling how to query a many-to-many releationship. This is what my models look like:
public class Begrip { public int ID { get; set; } public string Name { get; set; } public string Desc { get; set; } [Url] public string URL { get; set; } public ICollection<BegripCategory> Categories { get; set; } } public class Category { public int ID { get; set; } public string Name { get; set; } public ICollection<BegripCategory> Begrippen { get; set; } } public class BegripCategory { public int begripId { get; set; } public Begrip begrip { get; set; } public int categoryId { get; set; } public Category category { get; set; } }
And my Database context:
public class PBBContext : DbContext { public PBBContext (DbContextOptions<PBBContext> options) : base(options) { } public DbSet<PBB.Models.Movie> Movie { get; set; } public DbSet<PBB.Models.Begrip> Begrip { get; set; } public DbSet<PBB.Models.Category> Category { get; set; } public DbSet<PBB.Models.BegripCategory> BegripCategory { get; set; } protected override void OnModelCreating(ModelBuilder modelbuilder) { modelbuilder.Entity<BegripCategory>().HasKey(bc => new { bc.begripId, bc.categoryId }); modelbuilder.Entity<BegripCategory>().HasOne(b => b.begrip).WithMany(bg => bg.Categories).HasForeignKey(bc => bc.begripId); modelbuilder.Entity<BegripCategory>().HasOne(c => c.category).WithMany(ca => ca.Begrippen).HasForeignKey(cc => cc.categoryId); } }
What im trying to do is to return all the "Begrippen" in a JSON result with all the corresponding "Categories", however, I can't figure out how to get the list of "Categories" for them.
Any ideas? Thanks in advance.
-
Leon almost 7 yearsIts called Eager loading
-
MikeT about 5 yearsin EF Core 2.1 using VS 2017 15.9.7 that doesn't compile.
-
MikeT about 5 yearsIn fact, now that I look at your answer,
Begrip
doesn't have aCategories
property, only aBegripCategory
property. Downvoting, please update answer, I'll upvote. -
MikeT about 5 yearsAlso, curious how one would go about iterating through a variable number of
BegripCategory
child entities in aSelect()
statement. Is that possible? If so, could you show how? -
Richard about 5 yearsStill compiles fine for me under those conditions. Begrip does have a Categories property, the BegripCategory property is on the context, not the Begrip class.