drneel February 2016

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

Answers


Ivan Stoev February 2016

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

Post Status

Asked in February 2016
Viewed 3,379 times
Voted 13
Answered 1 times

Search




Leave an answer