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

Question

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 that 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.

Answer 1

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)