Why does Linq on date range return null on a combined query, works fine on a seperated query

Question

Issue: while filtering records within a date range & matching a CityID using LINQ, the query succeeds when written in 2 steps; however, it fails when combined as one query!

How can the LINQ query be rewritten so that -- it can perform both filters (i.e. match the CityId & retrieve records in the date range in the same step to improve performance?

I got it to work in two steps fine,

i.e. do a

var Step1 = db.weekRecord.Where(x => x.CityId == CityRecord.Id).ToList();

and then

Step1.Where(x => x.date.Date >= fromDate.Date 
                 && x.date.Date <= toDate.Date)
     .ToList();

it fails when I combine them!!

  // works when done in 2 steps!! 
  var weeklyWeather = db.weekRecord
    .Where(x => x.CityId == CityRecord.Id 
      && (x.date >= weekStarting && x.date <= weekEnding))

  // - when combined results are NULL!??
  var weeklyWeather2 = 
    db.weekRecord(x => x.date.Date >= fromDate.Date && x.date.Date <= toDate.Date)
      .ToList();

After looking up other SO answers, I tried this TruncateTime as well... could not get it to work..

// is this correct, from SO answers, DbFunctions.TruncateTime
var testQueryRecrods = db.weekRecord
  .Where(x => x.CityId == CityRecord.Id)
  .Where(x => 
    DbFunctions.TruncateTime(x.date.Date) >= DbFunctions.TruncateTime(fromDate.Date) 
    && DbFunctions.TruncateTime(x.date.Date) <= DbFunctions.TruncateTime(toDate.Date))
  .ToList();

ERROR:

[NotSupportedException: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.]
System.Data.Entity.Core.Objects.ELinq.MemberAccessTranslator.TypedTranslate(ExpressionConverter parent, MemberExpression linq) +452
System.Data.Entity.Core.Objects.ELinq.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq) +49

Answer 1

The question is confused, but I would assume the problem is the .Date. Unlike linq2sql, entity framework can not translate .Date to sql. But you can rewrite it like

var fromDateDate = fromDate.Date;
var toDateDate = toDate.Date;

var testQueryRecrods = db.weekRecord
                .Where(x => x.CityId == CityRecord.Id)
                .Where(x => DbFunctions.TruncateTime(x.date) >= fromDateDate
                         && DbFunctions.TruncateTime(x.date) <= toDateDate)
                .ToList();

And it would work. To some point. What EF generates is actually totally stupid in this case. Unlike linq2sql, EF generates query, that is not sargable (in my case*). It can run thousands of times slower than necessary. I would recommend to avoid the conversion to date completely:

var fromDateDate = fromDate.Date;
var toDateDate1 = toDate.Date.AddDays(1);

var testQueryRecrods = db.weekRecord
                .Where(x => x.CityId == CityRecord.Id)
                .Where(x => x.date >= fromDateDate 
                         && x.date < toDateDate1)
                .ToList(); 

As @juharr pointed out, when you split the query, you run first half against server and the second half as linq to objects. In that case the .Date works, but you download many more records in the first half than you need.

*the datetime type may be the problem, maybe it would work better with datetime2, I did not test this scenario

Answer 2

An offbest suggestion is to write your own LINQ extensions for this..

public static class ext
{
    //This extension compares one date to another... if you can call from Linq
    public static bool GreaterThan(this DateTime self, DateTime CompareDate
    {
    if (self.Year > CompareDate.Year) return true;

    else if ((self.Year == CompareDate.Year) && (self.Month > CompareDate.Month)
      return true;

    else if ((self.Year == CompareDate.Year) && (self.Month ==  CompareDate.Month)  && (self.Day > CompareDate.Day))
      return true;

    return false;
    }

}

Answer 3

I think, there is no option except using DbFunctions.TruncateTime for Linq to Entities. Because, as SQL Server query Linq to Entities should perform convertion datetime to date and the best method which can be used is DbFunctions.TruncateTime. I just debugged the DbFunctions.TruncateTime convertion and the translated query seems like;

WHERE (convert (datetime2, convert(varchar(255), [Extent1].[CreationDate], 102) ,  102)) > @p__linq__0

As you see, while performing the conversation, there is a redundant string conversation here. However, the EF would convert the datetime to date in SQL just like this 'cast(CreationDate as date)'. But it is not.

So, there are two options here.

1- If you have very huge table which the performance is affected by redundant string conversations, you should build your query manually in SQL as stored procedure or something like and execute it from context.

2- If you don't have performance considerations like that; just use DbFunctions.TruncateTime(x.date)

var testQueryRecrods = db.weekRecord
    .Where(x => x.CityId == CityRecord.Id)
    .Where(x => DbFunctions.TruncateTime(x.date) >= fromDate.Date && DbFunctions.TruncateTime(x.date) <= toDate.Date)
    .ToList();