"The specified type member 'Date' is not supported in LINQ to Entities"

Question

I started an asp.net mvc project and I wanna filter requesting data from database with dynamic linq/lambda. Some part of my code is as below:

IQueryable<VehicleMilage> query = null;
if (Fr_DeviceId == null && Fr_ContractId == null && From_Date == null && To_Date == null && From_QueryDate == null && To_QueryDate == null)
{
    query = (db.VehicleMilage).OrderBy(x => x.Id).Skip(skip).Take(rows);
}
else
{
    query = (db.VehicleMilage);

    if (Fr_DeviceId != null)
    {
        int a = int.Parse(Fr_DeviceId);
        query = query.Where(x => x.Fr_DeviceId == a);
    }
    if (Fr_ContractId != null)
    {
        int b = int.Parse(Fr_ContractId);
        query = query.Where(x => x.Fr_ContractId == b);
    }
    if (From_Date != null)
    {
        query = query.Where(x => x.From_Date.Date >= FromDate_ConvertedToDateTime1.Date);
    }
    if (To_Date != null)
    {
        query = query.Where(x => x.To_Date.Date <= ToDate_ConvertedToDateTime1.Date);
    }
    if (From_QueryDate != null)
    {
        query = query.Where(x => x.CreateDate.Date >= FromQueryDate_ConvertedToDateTime1.Date);
    }
    if (To_QueryDate != null)
    {
        query = query.Where(x => x.CreateDate.Date <= ToQueryDate_ConvertedToDateTime1.Date);
    }

    query = query.OrderBy(x => x.Id).Skip(skip).Take(rows);
}

My data in database is at least 2000000 records and I must define the variable query as IQueryable.

In block of else I must filter the query with every parameter that is not null but I encounter the error

The specified type member 'Date' is not supported in LINQ to Entities.

If I define the variable of query as List, then I could put ToList() before Where In every if in block of else that is related to datetime as below but in this project because of heavy data I cannot do this:

query = query.ToList().Where(x => x.From_Date.Date >= FromDate_ConvertedToDateTime1.Date);

You could use the Enity Framework method for truncating time. It's `EntityFunctions.TruncateTime` if you're on EF 6 or later. https://stackoverflow.com/questions/16217016/cant-get-entityfunctions-truncatetime-to-work But avoiding functions like that in your SQL will be more efficient when possible. — juharr, Jun 30 '19 at 13:27
@SalShah I agree with mjwills, It's better to avoid these functions in general. I only mentioned them here for anyone else that lands on this question but has to do the truncation in SQL (like when comparing to table columns instead of one against a passed in value that you control). — juharr, Jun 30 '19 at 13:53

score 3 · Accepted Answer · answered Jun 30 '19 at 13:24

query = query.Where(x => x.From_Date.Date >= FromDate_ConvertedToDateTime1.Date);

could be replaced with:

var fromDate = FromDate_ConvertedToDateTime1.Date;
query = query.Where(x => x.From_Date >= fromDate);

It has the same logical meaning, will generate efficient SQL and (most importantly) it won't experience the error you are seeing.

For less than or equal to, it is slightly more complicated - but not markedly so.

query = query.Where(x => x.To_Date.Date <= ToDate_ConvertedToDateTime1.Date);

could be replaced with:

var dayAfterToDate = ToDate_ConvertedToDateTime1.Date.AddDays(1)
query = query.Where(x => x.To_Date < dateAfterToDate);

That logic might seem odd at first glance - but if it is before the date after the ToDate that is logically equivalent to being on or before the ToDate (ignoring the time component).

Repeat the same process for the other queries - they will follow the exact same pattern.

I changed into ==> query = query.Where(x => EntityFunctions.TruncateTime(x.From_Date) >= FromDate_ConvertedToDateTime1.Date); — SalShah, Jun 30 '19 at 13:45

"The specified type member 'Date' is not supported in LINQ to Entities"

1 Answers1