I am trying to create a generic way to find deals that have a date that fall within a list of user-specified date ranges. Here's my code:
var closeDates = new List<Range<DateTime>>
{{ new Range<DateTime>{ Start = DateTime.Now, End = DateTime.Now.AddDays(1) }};
var deals = dbContext.Deals.AsQueryable();
deals = _helperService.ApplyDateRanges(deals, deal => deal.ClosedDate, closeDates);
The _helperService method:
public IQueryable<T> ApplyDateRanges<T>(IQueryable<T> query, Expression<Func<T, DateTime>> dateSelector, IEnumerable<Range<DateTime>> dates)
{
var enumerable = dates as Range<DateTime>[] ?? dates.ToArray();
Expression<Func<T, Range<DateTime>, bool>> matcher =
(row, rangeItem) => (rangeItem.Start == null || dateSelector.Invoke(row) >= rangeItem.Start) &&
(rangeItem.End == null || dateSelector.Invoke(row) < rangeItem.End.Value.AddDays(1));
var expr = PredicateBuilder.False<T>();
foreach (var dt in enumerable)
{
expr = expr.Or(d => matcher.Invoke(d, dt));
}
return query.AsExpandable().Where(expr.Expand());
}
But I'm getting the error: The parameter 'deal' was not bound in the specified LINQ to Entities query expression.
What is causing this?
The exception message indicates that the dateSelector
parameter has not been rebound correctly. Since there is third party package involved, I can't tell what is causing it.
But I guess this is related to your previous question. I was going to suggest you another simple manual expression building based working solution not involving third party packages. And here it is (assuming Range
class Start/End
members are of type DateTime?
):
public static class QueryableExtensions
{
public static IQueryable<T> WhereIn<T>(IQueryable<T> source, Expression<Func<T, DateTime>> dateSelector, IEnumerable<Range<DateTime>> ranges)
{
var filter = ranges == null ? null : ranges.Select(range =>
{
var startFilter = range.Start != null ? Expression.GreaterThanOrEqual(dateSelector.Body, Expression.Constant(range.Start.Value.Date)) : null;
var endFilter = range.End != null ? Expression.LessThan(dateSelector.Body, Expression.Constant(range.End.Value.Date.AddDays(1))) : null;
return startFilter == null ? endFilter : endFilter == null ? startFilter : Expression.AndAlso(startFilter, endFilter);
})
.Where(item => item != null)
.Aggregate(Expression.OrElse);
return filter != null ? source.Where(Expression.Lambda<Func<T, bool>>(filter, dateSelector.Parameters[0])) : source;
}
}
and you can use it simply like this (no helper service instance needed, following LINQ spirit):
var deals = dbContext.Deals.WhereIn(deal => deal.ClosedDate, closeDates);