CODEWITHSUNDEEP
c#sql-serverlinq
Nilesh Barai
Nilesh Barai

Reputation: 1322

Optimize C# LINQ Query

I am working on a C# project and using SQL server 2008 R2 express as backend.

I have to find count of bookings where BookingDate's Date component is greater than a tempDate.

I wrote following code:

DateTime tempDate = new DateTime(DateTime.Now.Year, 4, 1); //<= 5ms
var bookings = context.Bookings.Where(x => x.IsDeleted == false).ToList(); //<= 2ms
var count = bookings.Count(x => x.BookingDate.Date >= tempDate); //<= 2,534 ms

This code works and gives me correct count but its taking almost 3 seconds to execute (I found execution time using VS 2015). I want to optimize this code so that the execution time is reduced. Booking Table contains almost 20,000 records.

How do I minimize cost to compute?

Upvotes: 0

Views: 107

Answers (1)

JetpackOperation
JetpackOperation

Reputation: 58

Turns out that my previous answer wasn't that good. I did some test using a table with 10 000 entities and using .ToList() the time went from 280ms to 380ms. Also i found that selecting .Date doesn't work in linq queries so you have to do .ToList(). If you want to select count in the query you can't use DateTime.Date you have to use just DateTime.

2,500ms is quite slow. If you run your app in release mode it will probably be quite a bit faster.

DateTime tempDate = new DateTime(DateTime.Now.Year, 4, 1); 
int count = context.Bookings.Count(x => x.IsDeleted == false && x.BookingDate >= tempDate);

The problem is the .ToList() as it queries all the table rows instead of just the count

Upvotes: 3

Related Questions