Select Count(Id) in linq

Question

Is there any way to write a linq query to result in :

select Count(Id) from tbl1

because

tbl1.Select(q=>q.Id).Count()

doesn't translate to the result that I want

update :

it returns :

select count(*) from tbl1

Update after answer :

I tested the scenario with more than 21,000,000

Answer 1

Is there any way to write a linq query to result in.

No. First thing is to understad what you need, for sample, in T-SQL, you can use:

• COUNT(*) will counts the rows in your table
• COUNT(column) will counts the entries in a column - ignoring null values.

If you need to count how many rows you have, just use

var total = tbl1.Count();

If you need to see how many entities you have where a specific column is not null, then use a filter overloads of Count method.

var total = tbl1.Count(x => x.Id != null);

No, it is not possible. There is not difference realted with performance using Count(*) or ´Count(Id), even more if yourId` is the primary key.

I did an experiment with a table here with more than one million tuples. See the executioon plan of both queries. The first one is the select count(*) and second one is select count(id). The id is the primary key (sorry the results are in portuguese-brazil):

Answer 2

Using count(field) in sql counts all non-null values. In linq, you can say:

tbl1.Where(q => q.Id != null).Count();

or simply:

tbl1.Count(q => q.Id != null);