Strange results using dates in linq queries using EF Core

Question

I'm getting strange results using trying to do a simple query against a date column using Linq and EF Core.

If I run the query using a date from a list of DateTime I get no results. If I substitute DateTime.Now and add a negative number of days so that if matches the date in the list of DateTimes then the query returns results as expected.

So what is the difference between DateTime.Now and another DateTime object?

In practice, why would this work (rewinding now by 30 days in the first example gives the same date as datesToCheck[0] in the second):

 var reports = from r 
               in db.DailyReports
                    .Where(r => r.UserId.Equals(currentuser.Identity.Name) 
                             && r.Date > DateTime.Now.AddDays(-30)) 
               select r;

But not this:

var reports = from r 
              in db.DailyReports
                   .Where(r => r.UserId.Equals(currentuser.Identity.Name) 
                            && r.Date > datesToCheck[0]) 
              select r;

The database is SQL Server 2017, the column is a non-nullable smalldatetime

The datesToCheck list is generated thus:

var datesToCheck = new List<DateTime>();

var startDate =  DateTime.Now;
//get Monday date three weeks ago
if (DateTime.Now.DayOfWeek != DayOfWeek.Monday)
{
    while (startDate.DayOfWeek != DayOfWeek.Monday)
        {
            startDate = startDate.AddDays(-1);
        }
}

startDate = startDate.AddDays(-21);
while (startDate < DateTime.Now)
{
    if (startDate.DayOfWeek != DayOfWeek.Saturday || startDate.DayOfWeek != DayOfWeek.Sunday)
    {
        datesToCheck.Add(startDate);
        startDate = startDate.AddDays(1);
    }       
}

Doesnt make sense, can you show us `datesToCheck[0]` declaration and values? — Juan Carlos Oropeza, Apr 05 '17 at 16:15
Doesn't make sense, both predicates will be evaluated in memory (first because of `AddDays` function, second because of array/list indexer). As soon as `datesToCheck[0]` contains `DateTime.Now.AddDays(-30)`, both queries should return one and the same result. — Ivan Stoev, Apr 05 '17 at 16:52

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

The same behavior exists in EF6 and, as far as I know, all versions of EF. Basically, the compiler isn't clever enough to decide if datesToCheck[0] should be evaluated or converted to SQL. The query will work if you store the value in a variable and then use the variable in the LINQ query. See also: Why can't we use arrays in Entity Framework queries?

edited May 23 '17 at 10:30

Community

1
1

answered Apr 05 '17 at 16:29

Jamie Ide

48,427
16
81
117

score 0 · Answer 2 · answered Apr 05 '17 at 16:18

0

You probably have some datatype issue, Try:

DateTime datesToCheck = DateTime.Now.AddDays(-30);
var reports = from r 
              in db.DailyReports
                   .Where(r => r.UserId.Equals(currentuser.Identity.Name) 
                            && r.Date > datesToCheck ) 
              select r;

answered Apr 05 '17 at 16:18

Juan Carlos Oropeza

47,252
12
78
118

Thanks Juan Carlos - that works the same as placing DateTime.Now.AddDays(-30) inside the query. It's only when I try to use a value from the list of DateTime that it fails. Even if I create a new DateTime variable and assign the value from the list to it it still returns no results. – Tim Apr 05 '17 at 16:25
As I said, the problem is with the `datesToCheck[0]` variable did you debug the value? I still checking your code, but dont see anything yet. – Juan Carlos Oropeza Apr 05 '17 at 16:26

Strange results using dates in linq queries using EF Core

2 Answers2