Use Where Clause on navigation property. Many-to-many relationship

Question

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

el_M

View more on Stack Overflow

Chris Pratt · Accepted Answer · 8/22/2018 6:51:33 PM

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.

Use Where Clause on navigation property. Many-to-many relationship

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related