CODEWITHSUNDEEP
c#lambda
Giota B
Giota B

Reputation: 81

Lambda expression to count distinct rows in a table

I'm trying to switch from Linq to lambda expression. I want the count of distinct rows in my table.

Is there a better way to get it that the one below?

 var count = _db.myTable
     .Where(i=>i.DATE.HasValue && i.DATE==DateFrom)
     .AsEnumerable()
     .Select(x=>x.myTable_ID)
     .Distinct()
     .Count();

Upvotes: 0

Views: 3222

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1500963

I'm trying to switch from Linq to lambda expression.

It's not clear what you mean by that, but the lambda form is still using LINQ - if you mean you had a query expression before, it's worth being aware that query expressions are just transformed into non-query expression code by the compiler.

In terms of your actual query, you should get rid of the AsEnumerable() call, as otherwise you'll be performing everything after that in your .NET process rather than asking the database to do it. So simply:

var count = _db.myTable
     .Where(i => i.DATE.HasValue && i.DATE == DateFrom)
     .Select(x => x.myTable_ID)
     .Distinct()
     .Count();

After making sure that still works, you should check the generated SQL - it should be doing all the counting in the database, basically.

You should also see whether you really need the i.DATE.HasValue check, too - I suspect you don't, as if DateFrom is non-nullable, it'll only match non-null values of DATE anyway.

Upvotes: 1

Related Questions