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
– Juan Carlos Oropeza, Commented Apr 5, 2017 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
– Ivan Stoev, Commented Apr 5, 2017 at 16:52

Community · Accepted Answer · 2017-05-23 10:30:45Z

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, 2017 at 10:30

CommunityBot

11 silver badge

answered Apr 5, 2017 at 16:29

Jamie Ide

49.4k18 gold badges85 silver badges118 bronze badges

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

Comments

Juan Carlos Oropeza · Accepted Answer · 2017-04-05 16:18:25Z

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 5, 2017 at 16:18

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

2 Comments

Tim Over a year ago

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.

Juan Carlos Oropeza Over a year ago

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.

Collectives™ on Stack Overflow

Strange results using dates in linq queries using EF Core

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related