.NET LINQ to SQL query efficiency

Question

I am studying LINQ, and particularly LINQ to SQL on MSDN and came across this example, which, unless there is some clever magic under the hood, looks like a case of extreme sacrifice of computing resources over code readability.

int highScoreCount =
    (from score in scores
     where score > 80
     select score)
     .Count();

Someone please tell me that this query does not load all the scores in the list just to get their count. Or even not the scores over 80.

And if this is the case for this particular query , is there a was to change it in order to avoid loading the complete list of scores? like "select (count) score".

Thanks.

EDIT: I apologise for being lazy and not looking up a way to view generated SQL first. I found that the easiest way to do that is use LinqPad

OK, it does not load all the scores in the list just to get their count! — DavidG, Aug 04 '15 at 23:22
Seriously though, have you tried looking at the SQL it produces? — DavidG, Aug 04 '15 at 23:22
http://stackoverflow.com/questions/4899974/how-to-view-linq-generated-sql-statements — jleach, Aug 04 '15 at 23:25
OK, I apologise for the lazy question, I should have looked for ways to view the generate SQL. Anyway, I found that the easiest way to see the generated SQL is to run some queries in LinqPad. The SQL generated here is not far from what I'd write by hand. — Paceman, Aug 04 '15 at 23:57

score 5 · Accepted Answer · answered Aug 04 '15 at 23:26

The first part of your statement inside the () actually evaluates an IQueryable that has not yet been executed. When you call the Count() function on it, it compiles and evaluates the query as follows

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[scores] AS [Extent1]
    WHERE [Extent1].[score] > 80
)  AS [GroupBy1]

score 3 · Answer 2 · edited May 23 '17 at 11:50

Someone please tell me that this query does not load all the scores in the list just to get their count. Or even not the scores over 80.

I'm telling you, it will not load any items in to memory, it will produce SQL query equivalent to what you'd probably wrote manually:

SELECT COUNT(*) As Count FROM MyTable WHERE Score > 80;

and return that value to you.

You can verify that yourself by looking into SQL that's being sent to the database. Either by enabling profiling on the database itself, or by using tracing: How to view LINQ Generated SQL statements?

score 2 · Answer 3 · edited May 23 '17 at 12:23

2

LINQ is fairly smart about how it gets data... maybe not quite as smart as a query optimizer, but it can do some things right. You can peek at the SQL generated by it fairly easily. The following post explains:

How to view LINQ Generated SQL statements?

edited May 23 '17 at 12:23

Community

1
1

answered Aug 04 '15 at 23:26

jleach

7,410
3
33
60

.NET LINQ to SQL query efficiency

3 Answers3