Entity Framework DateTime Functions

Question

I'm trying to filter by date based on two datetimes. MinTime and MaxTime are both nullable DateTimes in SQL, I want to filter based on these, but I'm getting an error:

This function can only be invoked from LINQ to Entities

public static IEnumerable<ExclusionRule> GetExclusionRules(AppointmentTypes? apptType, string resourceName, TimeSpan? minTime, TimeSpan? maxTime, DayOfWeek? day) {
    using (var db = new BusinessObjectContainer()) {
        db.ExclusionRules.MergeOption = MergeOption.NoTracking;
        var items = db.ExclusionRules;
        int intDay = day.HasValue ? (int) day.Value : -1,
            intApptType = apptType.HasValue ? (int)apptType.Value : -1;
        var filteredApptType = apptType.HasValue ? items.Where(i => !i.t_AppointmentType.HasValue|| i.t_AppointmentType == intApptType) : items;
        var filteredResource = string.IsNullOrWhiteSpace(resourceName) ? filteredApptType : filteredApptType.Where(i => !string.IsNullOrEmpty(i.ResourceName) && resourceName.ToLower().Equals(i.ResourceName.ToLower()));

        IEnumerable<ExclusionRule> filteredMinDate;
        if (minTime.HasValue) {
            filteredMinDate = filteredResource.Where(i => (!i.MinTime.HasValue) || (EntityFunctions.CreateTime(i.MinTime.Value.Hour, i.MinTime.Value.Minute, 0) >= minTime.Value));
        } else {
            filteredMinDate = filteredResource;
        }

        IEnumerable<ExclusionRule> filteredMaxDate;
        if (maxTime.HasValue) {
            // this throws the exception
            filteredMaxDate = filteredMinDate.Where(i => (!i.MaxTime.HasValue) || EntityFunctions.CreateTime(i.MaxTime.Value.Hour, i.MaxTime.Value.Minute, 0) <= maxTime.Value);
        } else {
            filteredMaxDate = filteredMinDate;
        }
        var filteredWeekDay= day.HasValue ? filteredMaxDate.Where(i => !i.t_DayOfWeek.HasValue|| i.t_DayOfWeek == intDay) : filteredMaxDate;
        return filteredWeekDay.ToList();

Gert Arnold · Accepted Answer · 2013-12-20 08:33:04Z

2

You are using EntityFunctions* in a linq statement on an IEnumerable<ExclusionRule>. But you can only use it on an IQueryable that has an Entity Framework query provider. So you should start with IQueryable<ExclusionRule> (from EF) or just create DateTimes in the regular.Net way.

^{*DbFunctions as of Entity Framework 6.}

edited Dec 20, 2013 at 8:33

answered Jan 8, 2013 at 9:54

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

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

Comments

Moriya · Accepted Answer · 2013-01-08 08:58:36Z

0

MinTime.Value.Hour etc. might have to be executed in a Query and it seems to me that you are excecuting them in .Net memory. That's why you get the error.

LINQ to entities: http://msdn.microsoft.com/en-us/library/bb386964.aspx

answered Jan 8, 2013 at 8:58

Moriya

7,9363 gold badges37 silver badges54 bronze badges

Comments

spajce · Accepted Answer · 2013-01-08 10:22:13Z

0

This function can only be invoked from LINQ to Entities

EntityFunctions is applicable is LINQ to Entities queries only.

The EntityFunctions class contains methods that expose canonical functions to use in LINQ to Entities queries. http://msdn.microsoft.com/en-us/library/dd456873.aspx

answered Jan 8, 2013 at 10:22

spajce

7,0905 gold badges31 silver badges46 bronze badges

Comments

Echilon · Accepted Answer · 2013-01-09 14:00:07Z

0

I got around this by calling .ToList() before the DateTime filtering. This pulls everything into .NET so the querying can be done there.

As there are a relatively small number of items in my database this wasn't a problem.

answered Jan 9, 2013 at 14:00

Echilon

10.3k33 gold badges138 silver badges225 bronze badges

Collectives™ on Stack Overflow

Entity Framework DateTime Functions

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related