CODEWITHSUNDEEP
sqlsql-serverentity-frameworkdatetime
Quoc Khanh
Quoc Khanh

Reputation: 25

Complex DateTime query in Entity Framework

I would like to ask how to write a complex DateTime query in Entity Framework as below:

I have this code in service:

Func<DateTime, DateTime> calculate24HoursLater = (date) =>
{
    if (date.DayOfWeek == DayOfWeek.Friday)
        return date.AddDays(3);

    if (date.DayOfWeek == DayOfWeek.Saturday)
        return date.AddDays(3).Date;

    if (date.DayOfWeek == DayOfWeek.Sunday)
        return date.AddDays(2).Date;

    return date.AddDays(1);
};

var unactionedEnquiries = 
    dataContext.ContactedBrokerEnquiries
        .Include("ContactedBrokers")
        .Where(
            x => x.ContactedBrokers.All(c => c.Status == (byte)LeadStatus.Rejected) || 
                x.ContactedBrokers.Any(c => c.Status == (byte)LeadStatus.New && calculate24HoursLater(c.CreatedDate) < DateTime.Now)
        ).OrderByDescending(c => c.CreatedDate);

The result unactionedEnquiries, I expect it should be IQueryable. It means SQL server does not execute until my next statement

However, I get exception on calculate24HoursLater(c.CreatedDate) < DateTime.Now) This statment cannot translate into SQL statement. I know the reason but I dont know how to write that rule in Entity Framework query

Important: I dont prefer to push all of data into RAM then filter with that condition. Ideally, it should be in SQL-Server

Could you please let me know how to write them in SQL-EF statement?

Upvotes: 2

Views: 370

Answers (2)

mreyeros
mreyeros

Reputation: 4379

You may want to take a look at possibly using the SQLFunctions Methods that are available for doing date operations in LINQ queries

Sample (Untested) Try replacing your Func definition with the following:

Func<DateTime, DateTime> calculate24HoursLater = (date) =>
{
    if (date.DayOfWeek == DayOfWeek.Friday)
        return SqlFunctions.DateAdd("day", 3, date).Value;

    if (date.DayOfWeek == DayOfWeek.Saturday)
        return SqlFunctions.DateAdd("day", 3, date).Value;

    if (date.DayOfWeek == DayOfWeek.Sunday)
        return SqlFunctions.DateAdd("day", 2, date).Value;

    return SqlFunctions.DateAdd("day", 1, date).Value;
};

Upvotes: 1

ilans
ilans

Reputation: 2727

As I saw here @andrew's genius answer, you can do:

var calcDate = DateTime.Now.AddHours(-24);

var unactionedEnquiries = 
    dataContext.ContactedBrokerEnquiries
        .Include("ContactedBrokers")
        .Where(
            x => x.ContactedBrokers.All(c => c.Status == (byte)LeadStatus.Rejected) || 
                x.ContactedBrokers.Any(c => c.Status == (byte)LeadStatus.New && c.CreatedDate < calcDate)
        ).OrderByDescending(c => c.CreatedDate);

Upvotes: 0

Related Questions