LINQ to Entities - limiting included tables

Question

Using two tables with a one to many relationship (such as Make -> Model), how do I return a Make with limited Model children in an IQueryable function?

When I pass in “camry” as a variable, I want to get back a Toyota Make with only children called “camry”, not all children. Basically, I want to recreate this SQL statement:

SELECT
    MakeName,
    ModelName
FROM
    Make
    LEFT OUTER JOIN Model ON Model.MakeId = Make.MakeId
WHERE
    Model.ModelName = 'camry'

So far the LINQ statement would look like this:

return this.ObjectContext.Make
    .Include("Model")
    .Where(make => make.Model.ModelName.Equals("camry")

This obviously isn’t syntactically correct, because the .Model in the Where clause is an entity collection and doesn’t have a .ModelName property.

How do you limit the included tables? Is this possible in LINQ to Entities?

Answer 1

Note, this is off the top of my head, and I didn't bother to compile so something might be a bit off:

return this.ObjectContext.Make.Include("Model")
    .Where(make => make.Models.Any(model => model.ModelName == "camry"))

Also note that I think the make will have "Models" not "Model"

EDIT:

I didn't understand the question correctly. Here is the query you want:

return this.ObjectContext.Model.Include("Make")
    .Where(model => model.ModelName == "camry")

Answer 2

This should work as well:

from make in ctx.Makes
from model in make.Models
where model.Name == "camry"
select new 
{
   Make = make, 
   Model = model
}