Applying a Condition to an Eager Load (Include) Request in Linq to Entities

I have a table Product. Product is related to ProductDescription in a one-to-many relationship. ProductDescription can have more than one row for each product. It will have multiple rows if there are multiple translations for the description of the product. Product has a many-to-one relationship with Language. Language has a language code (en, es, etc.) as well as a LanguageId (found in ProductDescription as well).

I want to give my users the ability to requ开发者_如何学Pythonest a product and further tell the application to only return descriptions in a specific language.

I am having a bear of a time accomplishing this in Linq to Entities. I want to generate SQL like this:

SELECT * FROM Product p
JOIN ProductDescription pd ON p.ProductId = pd.ProductId
JOIN (SELECT * FROM Language WHERE AlternateCode = 'es') AS l ON pd.LanguageId = l.LanguageId

(AlternateCode is the field name for the language code)

Does anyone know how to do this? What I'm left with right now is pulling down all languages, then filtering them out with Linq to Objects which is less than ideal for sure. I could get the language codes per product in a loop but that would require multiple SQL round trips which I also don't want.

appreciate any help!

Use a projection, not eager loading.

var q = from p in Context.Product
        select new ProductPresentation
        {
           Id = p.Id,
           // etc.
           Description = new ProductDescriptionPresentation
           {
               Language = (from l in p.ProductDescription.Languages
                           where l.AlternateCode.Equals("es", StringComparison.OrdinalIgnoreCase)
                           select l).FirstOrDefault(),
               // etc.
           }
        };