Complex DateTime query in Entity Framework

Question

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?

mreyeros · Answer 1 · 2015-01-07T16:28:55.140

1

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;
};

edited Jan 07 '15 at 16:28

answered Jan 07 '15 at 16:05

mreyeros

4,359
20
24

SQLFunctions is for use with LINQ to EF, not sure why the down vote?? – mreyeros Jan 07 '15 at 16:18
Yes, but your `Func` doesn't. – Gert Arnold Jan 07 '15 at 21:22

score 0 · Accepted Answer · edited May 23 '17 at 12:30

0

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);

edited May 23 '17 at 12:30

Community

1
1

answered Jan 07 '15 at 16:12

ilans

2,537
1
28
29

it's really genius answer, awesome ^_^ – Quoc Khanh Jan 08 '15 at 02:55
Well, in this code I don't see how you can use a reference date that depends on day number. – Gert Arnold Jan 08 '15 at 13:22

Complex DateTime query in Entity Framework

2 Answers2