Creating an expression for use with Entity Framework 6

user_522663 0
Viewed 84

I have the following method that starts an IQueryable.

public static IQueryable<StorageDetail> QueryStorageDetails(ApplicationDbContext dbContext, DateRange dateRange)
{
    return from detail in dbContext.StorageDetails
           where (detail.ArrivalDate != null && detail.ArrivalDate <= dateRange.EndDate &&
                (detail.ReleaseDate == null || detail.ReleaseDate >= dateRange.StartDate)) ||
                (detail.TakeOrPayStartDate != null && detail.TakeOrPayStartDate <= dateRange.EndDate &&
                (detail.TakeOrPayEndDate == null || detail.TakeOrPayEndDate >= dateRange.StartDate))
           select detail;
}

I tried to refactor it as follows.

public static bool FilterByDate(StorageDetail detail, DateRange dateRange)
{
    return (detail.ArrivalDate != null && detail.ArrivalDate <= dateRange.EndDate &&
        (detail.ReleaseDate == null || detail.ReleaseDate >= dateRange.StartDate)) ||
        (detail.TakeOrPayStartDate != null && detail.TakeOrPayStartDate <= dateRange.EndDate &&
        (detail.TakeOrPayEndDate == null || detail.TakeOrPayEndDate >= dateRange.StartDate));
}

public static IQueryable<StorageDetail> QueryStorageDetails(ApplicationDbContext dbContext, DateRange dateRange)
{
    return dbContext.StorageDetails
        .Where(d => FilterByDate(d, dateRange));
}

But this produces an error.

The LINQ expression 'DbSet<StorageDetail>
    .Where(s => QueryHelper.FilterByDate(
        detail: s, 
        dateRange: __dateRange_0))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I'm pretty sure there's a way to do this, possibly by creating an Expression but I can't seem to get the syntax right. Does someone know how to do this?

entity-framework-core asp.net-core entity-framework .net c#
1 Answers

FilterByDate should return Expression<Func<StorageDetail, bool>> so EF would be able to translate your code into query. Try something like this:

public static Expression<Func<StorageDetail, bool>> FilterByDate(DateRange dateRange)
{
    return detail => (detail.ArrivalDate != null && detail.ArrivalDate <= dateRange.EndDate &&
        (detail.ReleaseDate == null || detail.ReleaseDate >= dateRange.StartDate)) ||
        (detail.TakeOrPayStartDate != null && detail.TakeOrPayStartDate <= dateRange.EndDate &&
        (detail.TakeOrPayEndDate == null || detail.TakeOrPayEndDate >= dateRange.StartDate));
}

And 

public static IQueryable<StorageDetail> QueryStorageDetails(ApplicationDbContext dbContext, DateRange dateRange)
{
    return dbContext.StorageDetails
        .Where(FilterByDate(dateRange));
}
user_25012790
Related