CODEWITHSUNDEEP
vb.netlinqperformance
spacemonkeys
spacemonkeys

Reputation: 1749

LINQ Count .. best method

My company has just started using LINQ and I still am having a little trouble with the abstractness (if thats a word) of the LINQ command and the SQL, my question is

  Dim query = (From o In data.Addresses _
                    Select o.Name).Count

In the above in my mind, the SQL is returning all rows and the does a count on the number rows in the IQueryable result, so I would be better with 

    Dim lstring = Aggregate o In data.Addresses _
    Into Count()

Or am I over thinking the way LINQ works ? Using VB Express at home so I can't see the actual SQL that is being sent to the database (I think) as I don't have access to the SQL profiler

Upvotes: 3

Views: 15174

Answers (4)

salgo60
salgo60

Reputation: 967

I think you are missing the point as Linq with SQL has late binding the search is done when you need it so when you say I need the count number then a Query is created.

Before that Linq for SQL creates Expression trees that will be "translated" in to SQL when you need it....

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx http://msdn.microsoft.com/en-us/netframework/aa904594.aspx

How to debug see Scott http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx alt text
(source: scottgu.com)

Upvotes: 0

Jon Hanna
Jon Hanna

Reputation: 113392

An important thing here, is that the code you give will work with a wide variety of data sources. It will hopefully do so in a very efficient way, though that can't be fully guaranteed. It certainly will be done in an efficient way with a SQL source (being converted into a SELECT COUNT(*) SQL query. It will be done efficiently if the source was an in-memory collection (it gets converted to calling the Count property). It isn't done very efficiently if the source is an enumerable that is not a collection (in this case it does read everything and count as it goes), but in that case there really isn't a more efficient way of doing this.

In each case it has done the same conceptual operation, in the most efficient manner possible, without you having to worry about the details. No big deal with counting, but a bigger deal in more complex cases.

To a certain extent, you are right when you say "in my mind, the SQL is returning all rows and the does a count on the number rows". Conceptually that is what is happening in that query, but the implementation may differ. Compare with how the real query in SQL may not match the literal interpretation of the SQL command, to allow the most efficient approach to be picked.

Upvotes: 1

Marc Bollinger
Marc Bollinger

Reputation: 3119

As mentioned, these are functionally equivalent, one just uses query syntax.

As mentioned in my comment, if you evaluate the following as a VB Statement(s) in LINQPad:

Dim lstring = Aggregate o In Test _
    Into Count()

You get this in the generated SQL output window:

SELECT COUNT(*) AS [value]
FROM [Test] AS [t0]

Which is the same as the following VB LINQ expression as evaluated:

(From o In Test_
    Select o.Symbol).Count

You get the exact same result.

Upvotes: 7

Mark Canlas
Mark Canlas

Reputation: 9583

I'm not familiar with Visual Basic, but based on

http://msdn.microsoft.com/en-us/library/bb546138.aspx

Those two approaches are the same. One uses method syntax and the other uses query syntax.

You can find out for sure by using SQL Profiler as the queries run.

PS - The "point" of LINQ is you can easily do query operations without leaving code/VB-land.

Upvotes: 2

Related Questions