Entity Framework DateTIme Query

Question

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
          };

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
          };

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.

what's the datatype of priorMonthStartOfDay in the last query? Is it a string or a DateTime? — galdin
– galdin, Commented Feb 8, 2016 at 19:40
Can you show how do you set priorMonthStartOfDay? It might not be exactly what you see in the debugger. — Ivan Stoev
– Ivan Stoev, Commented Feb 8, 2016 at 20:06
maybe the en.Amount is confusing it since you haven't defined what en actually is, or you're not showing us the full query — JamieD77
– JamieD77, Commented Feb 8, 2016 at 20:11

Ivan Stoev · Accepted Answer · 2016-02-08 20:21:04Z

1

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

answered Feb 8, 2016 at 20:21

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

drneel Over a year ago

It was the ticks. They were off by 1; I had checked the milliseconds, and I had even looked at the ticks, but apparently not close enough.

Collectives™ on Stack Overflow

Entity Framework DateTIme Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related