Linq to entities - how to select entities with a where condition on their entitycollection?

I found several times people asking for the same question but it seems that the answer was never satisfying altough it should be pretty easy (in theory). Here is my question :

I have an entity called "Company" inside which I have an entityCollection "Employees" (one to many). I need to retrieve all Companies and for each of them, I only want the employees with an Age greater than 21.

I tried :

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

That doesn't work as it gives me all employees for each company if there is at least one above 21 (it is actually the same than .Any() )

I tried :

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

That didn't work either (although it would have been perfect), it gives me the following error : The entity or complex type 'MyModel.Company' cannot be constructed in a LINQ to Entities query.

How can you select all my companies with only, for each of them, the employees being above 21 ? At the moment, I select all and on the client side, I filter my employees but I don't like tha开发者_StackOverflow社区t solution.

Can anybody help me ?

---

Thank you Morteza Manavi-Parast, it will do the work !

Nevertheless, I hardly convince myself that doing so in a unique query has not be implemented in the Entity framework. It is such a relatively common situation ... As a prove, there are numbers of questions like mine on this forum.

I am surprised ... Maybe for the next release ?

---

To be clear, I need a list of Companies as I am directly binding the result of my query to a datagrid. For your information, when I click on a row of my datagrid (so selecting a company), I have a second Grid which is populated with its employees (above 21 years old) coming from the entityCollection.

---

There is no way to have a "Conditional Eager Loading" with include in LINQ to Entities. There are 2 workarounds exist though. The first one is Filtered Projection and it's the one that Justin suggested but might not be desirable in all situations as it gives a collection of anonymous type objects.

The second way is called Two Tracked Queries which gives you a collection of strongly types Companies whose their employees satisfy a condition and I believe that's what you are looking for. Here is the code for it:

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 take a look at Conditional Eager Loading for another example.

---

Instead of using the type Company, have you tried selecting a 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()

(Sorry if the syntax is a little off, it's been a while since I've done LINQ/Anonymous Types in VB.NET)

---

You might be over-thinking this one. If you have the Company => Employee relationship bi-directionally mapped, then just do the select on Employee with the where clause and include company.

Return context.Employee.Include("Company").Where(e => e.Age > 21)