Question

I have a query which is fully translatable to SQL. For unknown reasons LINQ decides the last Select() to execute in .NET (not in the database), which causes to run a lot of additional SQL queries (per each item) against database.

Actually, I found a strange way to force the full translation to SQL:

我有点问(这是一份真正简化的文本,但并未按预期运作):

MainCategories.Select(e => new
{
    PlacementId = e.CatalogPlacementId, 
    Translation = Translations.Select(t => new
    {
        Name = t.Name,
        // ...
    }).FirstOrDefault()
})

该科将提出许多问题:

SELECT [t0].[CatalogPlacementId] AS [PlacementId]
FROM [dbo].[MainCategories] AS [t0]

SELECT TOP (1) [t0].[Name]
FROM [dbo].[Translations] AS [t0]

SELECT TOP (1) [t0].[Name]
FROM [dbo].[Translations] AS [t0]

...

但是,如果我再附上一份<代码>Select(,则仅复制所有成员:

.Select(e => new
{
    PlacementId = e.PlacementId, 
    Translation = new
    {
        Name = e.Translation.Name,
        // ...
    }
})

它将汇编成一份单一的结构说明:

SELECT [t0].[CatalogPlacementId] AS [PlacementId], (
    SELECT [t2].[Name]
    FROM (
        SELECT TOP (1) [t1].[Name]
        FROM [dbo].[Translations] AS [t1]
        ) AS [t2]
    ) AS [Name]
FROM [dbo].[MainCategories] AS [t0]

任何污点为何? How to force the LINQkou to make a one query more generalally (不含第二版Select()?

<>光> 我提出询问,使之真正简单。

PS: Only, idea I get is to post-process/transform queries with similar patterns (to add the another Select()).

Answer 1

当你打电话SingleOrDefault,时,你正在把结果输入客户。

单一Orfault Return IEvidable<T>,不再采用。 IQueryable<T>。此时此刻,你胁迫了它,它将在客户上进行进一步处理,它不能再履行库克的组成。

Answer 2

Not entirely sure what is going on, but I find the way you wrote this query pretty strange . I would write it like this, and suspect this will work:

        var q = from e in MainCategories
                let t = Translations.Where(t => t.Name == "MainCategory" 
                    && t.RowKey == e.Id 
                    && t.Language.Code == "en-US").SingleOrDefault()
                select new TranslatedEntity<Category>
                           {
                               Entity = e,
                               Translation = new TranslationDef
                                                 {
                                                     Language = t.Language.Code,
                                                     Name = t.Name,
                                                     Xml = t.Xml
                                                 }
                           };

我总是试图将<代码>从部分(数据来源的选用)与<代码>部分(预计到您的目标类型)分开。我认为,阅读/理解也比较容易,而且一般也与大多数支线供应商更好地合作。

Answer 3

您可以提出以下询问,以取得预期结果:

MainCategories.Select(e => new
{
    PlacementId = e.CatalogPlacementId, 
    TranslationName = Translations.FirstOrDefault().Name,
})

只要知道,它就是因为LINQ是如何预测问题的。我认为,如果看到封套的<代码>Select,则不会将这种编号变成多个子类,因为国际排雷中心不能使用从一个分局的多个回归栏,因此,准则调整将这一改动改为一个排单。 FirstOrDefault, 有一个栏目检索器,似乎直接翻译了K.的情况,因此,LINQ-SQL知道它可以撰写一个子序列。

第二个<代码>Select必须提出与我上述方式相似的询问。如果不挖掘成反射器,很难确认。一般来说,如果我需要选择许多栏目,我将使用<条码><>。声明如下:

from e in MainCategories
let translation = Translations.FirstOrDefault()
select new
{
    PlacementId = e.CatalogPlacementId, 
    Translation = new {
       translation.Name,
    }
})

友情链接