How to count records (SELECT COUNT) in Linq

Asked

Viewed 5,079 times

7

How to count records that a query would return in Linq (Entity Framework) in C#? The equivalent in SQL is to do a SELECT COUNT(*).

Note that I want to receive the count of records straight away, just as a SELECT COUNT(*) would, and not receive the results and do a count afterwards. Then a .Select().Count() or a .Where().Count() doesn’t solve my situation. Neither does that:

var result = from tbl in tabela
             select tabela;
int contagem = result.Count();

How would it be to perform other operations in a single query, for example: Min, Max, or arithmetic operations between two queries (ex: SELECT(...)/SELECT COUNT(*))?

c# sql entity-framework linq

2 answers

8


Although the commands are on separate lines in C#, only a query will be made in the database at the time you call the method Count().

That code does only one query:

var result = from tbl in tabela
             select tabela; // nenhuma query será executada

int contagem = result.Count(); // a query será executada aqui

  • My question is how the function works .Count() (and similar class methods Enumerable). Does it not operate on variables that already contain data collections (Lists)? Because by the size of the table I’m looking at, I can’t save the results in a variable and then count the number of records for that variable.

  • 1

    When using Linq-to-entities, the methods would not be about the IEnumerable but rather about a IQueryable... that’s why select it with COUNT(*) is only executed when the method Count() is called.

  • 2

    Remembering that a IQueryable is a IEnumerable. The reason why the Count is executed immediately because it has no deferred execution. You receive as direct return the result int, instead of a IQueryable

  • Got it! Thank you both very much!

  • 1

    What I meant was that the method Count() being called is a specific IQueryable. It is an Extension-method in the class System.Linq.Queryable. If you try to use the method System.Linq.Enumerable.Count(enumerable) and pass the object, then the query will be executed bringing all records, and then the count.

  • I mean, don’t do that: (result as IEnumerable<T>).Count()

Show 1 more comment

4

Basically for you to understand, as long as the list you are referring to is a IQueryable and Entity Framework has not executed any database query EF will attempt to convert its query into an SQL expression.

If you are using version 6 of the Entity Framework a good alternative is to always display the SQL queries executed by the framework, so for example:

var db = new DataContext(); //sua instância da implementação DbContext
db.Database.Log = (s) => System.Diagnostics.Debug.Write(s); // essa linha seta que o log gerado pelo EF6 será exibido no Output do Visual Studio
var c = db.Produtos.Sum(m => m.PrecoCusto); // essa consulta será executada conforme sua pergunta, com um SUM(campo) na query SQL

Checking the Database.Log you will be able to understand naturally what they are and when the EF actually executes a query in the database.

Browser other questions tagged c# sql entity-framework linq

You are not signed in. Login or sign up in order to post.