LINQ Count .. best method

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.