CODEWITHSUNDEEP
c#.netsqllinqlinq-to-sql
TN.
TN.

Reputation: 19840

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

Upvotes: 15

Views: 3137

Answers (3)

Hux
Hux

Reputation: 3122

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,
    }
})

Upvotes: 1

jeroenh
jeroenh

Reputation: 26792

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

I always try to separate the from part (selection of the datasources) from the select part (projection to your target type. I find it also easier to read/understand, and it generally also works better with most linq providers.

Upvotes: 1

Jordan Parmer
Jordan Parmer

Reputation: 37204

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.

Upvotes: 6

Related Questions