Performance of DateTime comparison in LINQ

Question

For example, I have some data source (probably, database) from where I need to extract all records for the last month. I'm using LINQ. There are two possible ways how to achieve this:

First:

var res = from rec in _records
          where rec.RequestDateTime.Date.Year == date.Year &&
                rec.RequestDateTime.Date.Month == date.Month
          select rec;

Second (using direct Date comparison):

var year = date.Year;
var month = date.Month;

var monthStart = new DateTime(year, month, 1);
var monthEnd = new DateTime(year, month, DateTime.DaysInMonth(year, month));

var res = from rec in _records
          where rec.RequestDateTime.Date >= monthStart &&
                rec.RequestDateTime.Date <= monthEnd
          select rec;

I heard that the second code works faster for the most LINQ providers, but can't find any proofs or explanations (or disproofs). So, what way actually better to use for better performance and why?

Answer 1

I'd go further - IMO it will work faster for almost all providers (not least, LINQ-to-Objects). When talking about indexed data - database indexes are usually sorted, so taking a selection based on a range is really really quick and easy. There is no interpretation required by >= and <=, and it can jump directly to the required range very quickly. However, if you are comparing the year and month, it has two choices:

• calculate the year / month (based on the numeric value underpinning a date) and compare for equality - and note it may or may not be able to use the index, needing instead a full scan
• do some very clever thinking to determine that it is a range (making query parse more complex)

For the simplest case (LINQ-to-Objects) the same holds true; >= etc is trivial - it is a numeric compare. Testing month/year requires computing the month / year. That data is not stored explicitly as integers and is not directly on hand. Yes it isn't overly expensive to calculate, but when considering date en-masse, it isn't free either.