Click here to Skip to main content
14,239,722 members
Rate this:
Please Sign up or sign in to vote.
See more:
I want to know which operators we have like group by that linq to sql translates them to multiple queries and what can we do to prevent it , I think this is a performance issue.
for example I have this simple linq query :
var productList = products.GroupBy(s => new { s.LastSale }).ToList();
I have 3 groups in my data in Product table in database therefore I see 3 execution of query in sql profiler :
1.exec sp_executesql N'SELECT [t0].[ID], [t0].[Description], [t0].[Discontinued], [t0].[LastSale]
FROM [dbo].[Product] AS [t0]
WHERE @x1 = [t0].[LastSale]',N'@x1 date',@x1='2015-02-07'

2.exec sp_executesql N'SELECT [t0].[ID], [t0].[Description], [t0].[Discontinued], [t0].[LastSale]
FROM [dbo].[Product] AS [t0]
WHERE @x1 = [t0].[LastSale]',N'@x1 date',@x1='2015-04-08'

3.exec sp_executesql N'SELECT [t0].[ID], [t0].[Description], [t0].[Discontinued], [t0].[LastSale]
FROM [dbo].[Product] AS [t0]
WHERE @x1 = [t0].[LastSale]',N'@x1 date',@x1='2015-04-09'

Thank you
Posted
Updated 21-May-15 6:48am
v2
Comments
Maciej Los 16-May-15 16:45pm
   
This question has no sense. GroupBy operator does not "translate" whatever into multiple queries!
maryamm7 21-May-15 12:41pm
   
for example I have a simple query like this : var productList = products.GroupBy(s => new { s.LastSale }).ToList(); for each group that exists in product table one query generate , below is what I see in sql profiler i see 3 seperate query because I have 3 groups in product table :
1.exec sp_executesql N'SELECT [t0].[ID], [t0].[Description], [t0].[Discontinued], [t0].[LastSale]
FROM [dbo].[Product] AS [t0]
WHERE @x1 = [t0].[LastSale]',N'@x1 date',@x1='2015-02-07'

2.exec sp_executesql N'SELECT [t0].[ID], [t0].[Description], [t0].[Discontinued], [t0].[LastSale]
FROM [dbo].[Product] AS [t0]
WHERE @x1 = [t0].[LastSale]',N'@x1 date',@x1='2015-04-08'

3.exec sp_executesql N'SELECT [t0].[ID], [t0].[Description], [t0].[Discontinued], [t0].[LastSale]
FROM [dbo].[Product] AS [t0]
WHERE @x1 = [t0].[LastSale]',N'@x1 date',@x1='2015-04-09'
Richard Deeming 21-May-15 13:48pm
   
I've just tested this in LINQPad, and the results are surprising!

Entity Framework generates a single, sensible query.

LINQ to SQL generates multiple queries - one to read the group keys, and then one for each group to load the items belonging to the group.
maryamm7 22-May-15 14:33pm
   
Thank you , it is like a bug in Linq To Sql!
virusstorm 21-May-15 14:00pm
   
Why not write a stored procedure for this operation and bind it to your EF objects?
maryamm7 22-May-15 14:34pm
   
Thank you , it is a solution but I like a answer in Linq To Sql!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100