What is the difference between these LINQ queries

Question

I tricked some LINQs over entities, and I get weird results, and I would like an explanation ...

Given the following LINQ query,

// Sample # 1 IEnumerable<GroupInformation> groupingInfo; groupingInfo = from a in context.AccountingTransaction group a by a.Type into grp select new GroupInformation() { GroupName = grp.Key, GroupCount = grp.Count() }; 

I get the following SQL query (taken from SQL Profiler):

 SELECT 1 AS [C1], [GroupBy1].[K1] AS [Type], [GroupBy1].[A1] AS [C2] FROM ( SELECT [Extent1].[Type] AS [K1], COUNT(1) AS [A1] FROM [dbo].[AccountingTransaction] AS [Extent1] GROUP BY [Extent1].[Type] ) AS [GroupBy1] 

So far so good.

If I changed my LINQ query to:

 // Sample # 2 groupingInfo = context.AccountingTransaction. GroupBy(a => a.Type). Select(grp => new GroupInformation() { GroupName = grp.Key, GroupCount = grp.Count() }); 

it gives an exact SQL query. It makes sense to me.

Here is the interesting part ... If I changed my LINQ query to:

 // Sample # 3 IEnumerable<AccountingTransaction> accounts; IEnumerable<IGrouping<object, AccountingTransaction>> groups; IEnumerable<GroupInformation> groupingInfo; accounts = context.AccountingTransaction; groups = accounts.GroupBy(a => a.Type); groupingInfo = groups.Select(grp => new GroupInformation() { GroupName = grp.Key, GroupCount = grp.Count() }); 

the following SQL is executed (I deleted several fields from the actual query, but all the fields from the table (~ 15 fields) were included in the query twice):

 SELECT [Project2].[C1] AS [C1], [Project2].[Type] AS [Type], [Project2].[C2] AS [C2], [Project2].[Id] AS [Id], [Project2].[TimeStamp] AS [TimeStamp], -- <snip> FROM ( SELECT [Distinct1].[Type] AS [Type], 1 AS [C1], [Extent2].[Id] AS [Id], [Extent2].[TimeStamp] AS [TimeStamp], -- <snip> CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2] FROM (SELECT DISTINCT [Extent1].[Type] AS [Type] FROM [dbo].[AccountingTransaction] AS [Extent1] ) AS [Distinct1] LEFT OUTER JOIN [dbo].[AccountingTransaction] AS [Extent2] ON [Distinct1].[Type] = [Extent2].[Type] ) AS [Project2] ORDER BY [Project2].[Type] ASC, [Project2].[C2] ASC 

Why are the generated SQL files different from each other? After all, the same code executes, simply that sample # 3 uses intermediate variables to do the same job!

Also, if I do this:

 Console.WriteLine(groupingInfo.ToString()); 

for sample # 1 and sample # 2, I get the same query that was captured by SQL Profiler, but for sample No. 3 I get:

 System.Linq.Enumerable+WhereSelectEnumerableIterator`2[System.Linq.IGrouping`2[System.Object,TestLinq.AccountingTransaction],TestLinq.GroupInformation] 

What is the difference? Why can't I get the SQL query generated by LINQ if I share the LINQ query in several statements?

The purpose of ulitmate is the ability to add statements to the query (Where, OrderBy, etc.) at run time.

By the way, I saw this behavior in EF 4.0 and EF 6.0.

Thank you for your help.

Answer 1

The reason is that in the third attempt, you refer to accounts as IEnumerable<AccountingTransaction> , which will cause the request to be called using Linq-To-Objects ( Enumerable.GroupBy and Enumerable.Select )

On the other hand, in the first and second attempts, the reference to AccountingTransaction saved as IQueryable<AccountingTransaction> , and the query will be executed using Linq-To-Entities , which then converts it to the corresponding SQL statement.