CODEWITHSUNDEEP
c#sql-serverlinqdatetimelinq-to-sql
drneel
drneel

Reputation: 2907

Entity Framework DateTIme Query

I'm having an issue with a linq subquery return invalid data when adding in datetime checks as part of the where clause.

This is the original query and it is returning 0; because the result set is null

var subquery = 
    (from item in g
     from e in item.Entry
    where e.Type == 1 
       && e.EntryType == 2
       && item.StartDate >= priorMonthStartOfDay
       && item.EndDate <= startOfDayQueryParam
   select e.Amount).Sum() ?? 0M;

I modified the query to see what the data was; here is that query and the resulting dataset.

var subquery = 
    (from item in g
     from e in item.Entry
    where e.Type == 1 
       && e.EntryType == 2
   select new 
          {
               Amount = e.Amount,
               SD = item.StartDate,
               ED = item.EndDate,
               QD = priorMonthStartOfDay
          };

no date restriction results

So then I added in the start date comparison and the results are below. The priorMonthStartOfDay is a DateTime with a value of 12/1/2015 12:00:00 AM

var subquery = 
    (from item in g
     from e in item.Entry
    where e.Type == 1 
       && e.EntryType == 2
       && item.StartDate >= priorMonthStartOfDay
   select new 
          {
               Amount = e.Amount,
               SD = item.StartDate,
               ED = item.EndDate,
               QD = priorMonthStartOfDay
          };

start date restriction

Why is the date comparison not behaving as I would expected? Given the value of priorMonthStartOfDay, I would expect the result set to be the same for the last two queries. I'm guessing it has something to do with the time equal comparison because if I subtract a second from the priorMonthStartOfDay then the result sets match up again.

Upvotes: 0

Views: 3613

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205779

The only logical explanation could be that your priorMonthStartOfDay and/or startOfDayQueryParam variables contain time part not shown in the debugger. Note that by default milliseconds part is not shown, not to mention ticks.

To be 100% sure you are comparing against dates, change the date part of the criteria to

&& item.StartDate >= priorMonthStartOfDay.Date
&& item.EndDate <= startOfDayQueryParam.Date

Upvotes: 1

Related Questions