我有一个表产品。产品涉及产品描述在一对多的关系。产品描述可以有一个以上的行的每个产品。它将具有多个行，如果有多个的翻译产物的描述。产品具有许多对一与语言的关系。语言有语言的code（恩，胚胎等），以及一个LanguageId（发现产品描述为好）。

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 request a product and further tell the application to only return descriptions in a specific language.

我有一个时间的Linq完成这实体熊。我要生成的SQL是这样的：

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

（备用code是字段名称语言code）

(AlternateCode is the field name for the language code)

有谁知道如何做到这一点？什么我留下了，现在是拉下来的所有语言，然后过滤出来的LINQ到对象是不太理想的肯定。我能得到的语言codeS每个产品的循环但这需要，我也不想多条SQL往返。

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.

AP preciate任何帮助！

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.
           }
        };