How do I choose entities using a where condition on their entitycollection in Linq to Entities?

eager-loading entitycollection entity-framework linq-to-entities one-to-many

Question

I discovered that the same question was asked again, but it appears that there was never a satisfactory response, even though it should have been very simple (in theory). This is my query:

I have an entity named "Company" with a collection called "Employees" inside of it (one to many). I need to locate every company, but I only need the staff members that are older than 21.

I attempted to

Return context.Companies.Include("Employees").Where(c => c.Employees.Where(e => e.Age > 21).Count() > 0)

This is ineffective since it provides me all of the workers for each firm if there is at least one above the age of 21 (which is really what happens). Any())

I attempted to

Return context.Companies.Include("Employees").Select(c => New Company {  
.Id = c.Id, 
.Employees = c.Employees.Where(Function(e) e.Age > 24)
}).ToList()

Even though it would have been ideal, it didn't work either and I get the following error: It is not possible to build the entity or complex type "MyModel.Company" in a LINQ to Entities query.

How do you choose all of my businesses when the sole requirement for each of them is that the workers be above 21? I now choose all and filter my workers on the client side, but I don't like that approach.

Can somebody assist me?


I appreciate you, Morteza Manavi-Parast; it will work!

I find it difficult to believe me that the Entity framework has not already made provision for accomplishing this in a unique query. It is such a typical circumstance... There are several queries like mine on this site as evidence.

I find it surprising. Possibly for the next release?


Since I am directly tying the results of my query to a datagrid, it should be evident that I need a list of Companies. For your knowledge, a second Grid that is filled with the firm's workers (over 21 years old) drawn from the entityCollection appears when I click on a row of my datagrid, therefore picking a company.

1
2
4/16/2012 1:58:12 PM

Accepted Answer

A "Conditional Eager Loading" with inclusion in LINQ to Entities is not possible. However, there are two workarounds. The first option, Constrained Projection, is the one Justin advised, although it may not always be preferable since it provides a group of objects of the anonymous type.

The second method is known as Two-Tracking Query and it provides you a list of companies with strong kinds whose workers meet a requirement. I think this is what you are searching for. The code is as follows:

var companies = context.Companies.ToList();
var employees = context.Employee.Where(e => e.Age > 21);
foreach (var employee in employees) {
   companies.Single(c => c.CompanyID == employee.CompanyID).Employees.Add(employee);
}

Please see Conditional Thrifty Loading for a different illustration.

2
5/23/2017 12:10:26 PM

Popular Answer

Have you thought about using the type Company instead of the new anonymous type:

Return context.Companies.Include("Employees").Select(c => New With {
    .Id = c.Id,
    .Employees = c.Employees.Where(Function(e) e.Age > 24)
}).ToList()

(I apologize if the syntax is a bit odd; I haven't used LINQ/Anonymous Types in VB.NET in a while.)



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