Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel