Click here to Skip to main content
15,895,084 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900