Use Where Clause on navigation property. Many-to-many relationship
asp.net-core asp.net-core-2.1 c# entity-framework-core linq
Question
I have been looking through other examples on SO and I am still unable to resolve this.
I have the following model structure
public class Event
{
[Key]
public int ID { get; set; }
public ICollection<EventCategory> EventCategories{ get; set; }
}
public class Category
{
[Key]
public int ID { get; set; }
public ICollection<EventCategory> EventCategories{ get; set; }
}
public class EventCategory
{
[Key]
public int ID { get; set; }
public int EventID{ get; set; }
public Event Event{ get; set; }
public int CategoryID{ get; set; }
public Category Category{ get; set; }
}
From my Events controller I am trying to use a LINQ query to only show Events where the CategoryID is equal to 1 but i keep on coming into errors with my WHERE clause I think.
UPDATE:
I have been trying multiple queries but at present it is
var eventsContext = _context.Events
.Include(e => e.EventCategories)
.Include(e=>e.EventCategories.Select(ms => ms.Category))
.Where(e=>e.ID==1)
.Take(15)
.OrderByDescending(o => o.StartDate);
This is the error I get
TIA
Accepted Answer
First, the lambda passed to Include must be a model expression. Specifically, that means you cannot use something like Select. If you're trying to include EventCategories.Category, then you should actually do:
.Include(e => e.EventCategories).ThenInclude(ms => ms.Category)
That will fix your immediate error. The next issue is that the way in which you're attempting to query the category ID is incorrect. The lamdas don't carry over from one clause to the next. In other words, when you're doing Where(e => e.ID == 1), e is Event, not Category. The fact that you just included Category doesn't limit the where clause to that context. Therefore, what you actually need is:
.Where(e => e.EventCategories.Any(c => c.CategoryID == 1))
For what it's worth, you could also write that as:
.Where(e => e.EventCategories.Any(c => c.Category.ID == 1))
Notice the . between Category and ID. Now this where clause requires joins to be made between all of Event, EventCategories, and Category, which then means you don't actually need your Include(...).ThenInclude(...) statement, since all this does is tell EF to make the same JOINs it's already making. I will still usually do the includes explicitly, though, as otherwise, if your where clause were to change in some future iteration, you may end up no longer implicitly including everything you actually want included. Just food for thought.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow