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

i have table product. product related productdescription in one-to-many relationship. productdescription can have more 1 row each product. have multiple rows if there multiple translations description of product. product has many-to-one relationship language. language has language code (en, es, etc.) languageid (found in productdescription well).

i want give users ability request product , further tell application return descriptions in specific language.

i having bear of time accomplishing in linq entities. want generate sql this:

select * product p join productdescription pd on p.productid = pd.productid join (select * language alternatecode = 'es') l on pd.languageid = l.languageid 

(alternatecode field name language code)

does know how this? i'm left right pulling down languages, filtering them out linq objects less ideal sure. language codes per product in loop require multiple sql round trips don't want.

appreciate help!

use projection, not eager loading.

var q = p in context.product         select new productpresentation         {            id = p.id,            // etc.            description = new productdescriptionpresentation            {                language = (from l in p.productdescription.languages                            l.alternatecode.equals("es", stringcomparison.ordinalignorecase)                            select l).firstordefault(),                // etc.            }         };