linq sql - aggregate count

Question

I have some linq that returns the correct data.

var numEmails = (from row in EmailBatchProposal  
where row.EmailBatchId == emailBatchId
select row.EmailBatchProposalId).Count();

However, if I understand linq correctly, this does not perform optimally. It grabs all the data and then walks through the list and counts the rows. What I'd really like is for linq (in the background) to use like:

Select count(*) from ...

I trust the performance reasons are obvious.

Does anyone know the proper way to do this?

Answer 1

People generally learn best by practicing. I would suggest you get a copy of LinqPad (free), enter in your Linq query, and see what SQL it generates. Then you can modify the Linq query until you get exactly what you want.

Answer 2

Actually, if the linq query is used with a collection that implements IQueryable and supports translation into underlying SQL variant, it is quite a basic functionality to translate the Count function from your example correctly.

Answer 3

You can just use the argument of .Count().

int numEmails = EmailBatchProposal.Count(x => x.EmailBatchId == emailBatchId)

As noted below, this doesn't actually resolve to any different SQL, but I think it's at least a cleaner looking alternative.

Answer 4

Actually, the LINQ-to-SQL is smart enough to know that it should do a count... For example, if I have the following query:

var v = (from u in TblUsers
        select u).Count();

the SQL that actually executes when I run this is:

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

Amazing, huh? Another poster made the really good suggestion of getting LinqPad - it is a WONDERFUL tool. It will show you the exact SQL that gets executed. You can always confirm with SQL profiler too.

Answer 5

Check the Log of the SQL used in the query.

        using (var dbc = new siteDataContext())
        {
            dbc.Log = Console.Out;
            var bd = (from b in dbc.birthdays
                     select b).Count();
            Console.WriteLine("{0}", bd);
        }

gives the query:

SELECT COUNT(*) AS [value]
FROM [dbo].[birthdays] AS [t0]
 -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1