Is it a good way to get Count on Linq?

Question

public class BuscaDiretaBusiness
{
    PortalIOEntities portalIO = new PortalIOEntities();

    public IQueryable CarregaAlias(out int ItemsCount)
    {
        var query = from a in portalIO.DO_CadernoBuscaDiretaAlias
                    select new
                    {
                        a.CDP_ID,
                        a.CDA_Alias,
                        a.DO_CadernoDeParaBuscaDireta.CDP_NomeCaderno
                    };
        ItemsCount = query.Count();
        return query;
    }
}

Will it make two selects on DataBase?

Answer 1

That method will only hit the DB once, with a query like SELECT COUNT(*) …. But if you try to enumerate the resulting IQueryable in any way (like calling ToArray() on it or using it in a foreach), it will make another DB query.

You should probably only return the IQueryable. If the consumer needs all the items, it will call ToArray() or similar, which will make one DB query and then look at the Length of the result (which of course won't make another query). If the consumer needs only part of the result (e.g. it will call Take(100)) and it also needs the total count, it will call Count() itself. This will mean there will be two queries, but I think it can't be done differently.

Also, you probably should not be returning a collection of an anonymous type from a method, because it's hard to use.

Answer 2

You're fine. It will only make 1 query.

Plus dreza is right, don't pass back "count" through the out parameter. It isn't needed.

Answer 3

Typically that's the correct way to get a count, yes. Linq2sql and Linq2Nh for instance will see the Count operator and create a COUNT(*) query at TSQL level. So it'll hit the db once when .Count() is called on the IQueryable, returning a single row containing the count.

Note that it'd hit the db again if you call .Count() again. Your method appears to have two responsibilities at present - I'd just return the required IQueryable and call the .Count() on it elsewhere. Then you can still easily grab the IQueryable if you actually need to enumerate the results later