How to force LINQ to SQL to evaluate the whole query in the database?

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:

I have a query (this is a really simplified version, which still does not work as expected):

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

It will generates a lot of SQL queries:

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]

...

However, if I append another Select() which just copies all members:

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

It will compile it into a single SQL statement:

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]

Any clues why? How to force the LINQ to SQL to generate a single query more generically (without the second copying Select())?

NOTE: I've updated to query to make it really simple.

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

Edit Report

You can write the query as follows to get the desired result:

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

As far as i'm aware, it's due to how LINQ projects the query. I think when it see's the nested Select, it will not project that into multiple sub-queries, as essentially that would be what would be needed, as IIRC you cannot use multiple return columns from a sub-query in SQL, so LINQ changes this to a query-per-row. FirstOrDefault with a column accessor seems to be a direct translation to what would happen in SQL and therefore LINQ-SQL knows it can write a sub-query.

The second Select must project the query similar to how I have written it above. It would be hard to confirm without digging into a reflector. Generally, if I need to select many columns, I would use a let statement like below:

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

answered 2011-09-13T09:12:28.790

How to force LINQ to SQL to evaluate the whole query in the database?

1 Answer

Your Answer