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

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:

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()).

Answer 1

When you call SingleOrDefault in MyQuery, you are executing the query at that point which is loading the results into the client.

SingleOrDefault returns IEnumerable<T> which is no longer an IQueryable<T>. You have coerced it at this point which will do all further processing on the client - it can no longer perform SQL composition.