How to user "Where" in "Include" and "Select" query in Entity Framework 6

asp.net-mvc c# entity-framework-6

Question

In my database I have 3 tables, Exams, ExamPlaces, Agents. The Id of each exam in Exams table are foreign key in ExamPlaces table and the Id of each Exam Place are foreign key in Agents table. I want to return all exams from a specific Agent. I tried to return the exams like this:

return _db.Exams
            .Include(e => e.ExamPlaces.Select(a => a.Agents
                .Where(agent => agent.AgentId == supervisorId))).ToList();

But I got below error:

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path

Is it possible to use "Where" clause like how I did with "Include" to retrieved data?

Appreciate

1
0
10/12/2019 7:52:20 PM

Accepted Answer

As you noticed, EF 6 doesn't support to filter in the Include method.

Disclaimer: I'm the owner of the project Entity Framework Plus

The EF+ Query IncludeFilter (free and open source) allows easily filter included entities.

To use it, you simply need to replace all "Filter" by "IncludeFilter". You also at this moment need to use IncludeFilter on every level.

Example:

return _db.Exams
            .IncludeFilter(e => e.ExamPlaces)
            .IncludeFilter(e => e.ExamPlaces.Select(a => a.Agents
                .Where(agent => agent.AgentId == supervisorId))).ToList();
1
10/13/2019 3:56:29 PM

Popular Answer

The Include doesn't affect which records get returned. It enables eager loading of the navigation properties specified. Essentially, it's a way of saying, "Yes, I want some Exams, but I'll also use the corresponding ExamPlaces later on, so go ahead and get them from the database now to save time". It has nothing to do with the Where.

In this particular case, Where is difficult to work with. That has nothing to do with Include. If you really want to start with _db.Exams and use Where, I think you could put a Join somewhere before the Where to get it working.

However, think of the query the other way around. You already have the agent you want. That agent has a collection of ExamPlaces, and the ExamPlaces each has a collection of Exams. You just need to flatten those out into a single list. Something like this should work (not checked for syntax errors):

return _db.Agents
        .Find(supervisorId)
        .ExamPlaces
        .SelectMany(p => p.Exams)
        .Include(e => e.ExamPlaces.Select(p => p.Agents))
        .ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow