CODEWITHSUNDEEP
linq-to-sql
Curtis White
Curtis White

Reputation: 6353

Linq-to-Sql Count

I need to do a count on the items in a joined result set where a condition is true. I thus have a "from join where where" type of expression. This expression must end with a select or groupby. I do not need the column data actually and figure it is thus faster not to select it:

count = (from e in dc.entries select new {}).Count();

I have 2 questions:

  1. Is there a faster way to do this in terms of the db load?
  2. I have to duplicate my entire copy of the query. Is there a way to structure my query where I can have it one place for both counts and for getting say a list with all fields?

Thanks.

Please pay especial attention:

  1. The query is a join and not a simple table thus I must use a select statement.
  2. I will need 2 different query bodies because I do not need to load all the actual fields for the count but will for the list.

I assume when I use the select query it is filling up with data when I use query.Count vs Table.Count. Look forward to those who understand what I'm asking for possible better ways to do this and some detailed knowledge of what actually happens. I need to pull out the logging to look into this deeper.

Upvotes: 4

Views: 5519

Answers (2)

Amy B
Amy B

Reputation: 110221

Queryable.Count

The query behavior that occurs as a result of executing an expression tree that represents calling Count(IQueryable) depends on the implementation of the type of the source parameter. The expected behavior is that it counts the number of items in source.

In fact, if you use LinqToSql or LinqToEntities, Queryable.Count() is sent into the database. No columns are loaded to memory. Check the generated sql to confirm.

I assume when I use the select query it is filling up with data when I use query.Count vs Table.Count

This is not true. Check the generated sql to confirm.

I have to duplicate my entire copy of the query. Is there a way to structure my query where I can have it one place for both counts and for getting say a list with all fields

If you need both the count and the list, get the list and count it.

If you need the count sometimes and other times you need the list... write a method that returns the complex IQueryable, and sometimes call .Count() and other times call .ToList();

I do not need the column data actually and figure it is thus faster not to select it.

This is basically false in your scenario. It can be true in a scenario where an index covers the result columns, but you don't have any result columns.

In your scenario, whatever index is chosen by the query optimizer, that index can be used to make the count.

Sum up: Query optimizer will perform the optimization you desire.

Upvotes: 6

Rami Alshareef
Rami Alshareef

Reputation: 7170

//you can put a where condition here    
var queryEntries = from e in dc.entries select e;
//Get count 
queryEntries.Count();
//Loop through Entries, so you basically returned all entries
foreach(entry en in queryEntries)
{}

Upvotes: 1

Related Questions