Filtering Entity Framework results on SQL Server

Question

First up: we are not doing TPH (table per hierarchy), that would make this a lot simpler.

I have about 20 POCOs that all have similar properties in some cases. The similar properties I care about are ___.CreatedDate and ___.UpdatedDate. For some of the POCOs, UpdatedDate doesn't make sense, so they don't have that property. CreatedDate will always exist, UpdatedDate may be null. Sometimes there's a third field as well.

The query to retrieve objects from the database always looks the same, regardless of which of the 20 POCOs I'm querying. However, the query was duplicated 20 times, one for each type of the object. I was able to break out the retrieve part to an extension method (off of IDbSet<T>) to do the initial lookup and joins, leaving the date range filtering as an exercise to the consumer. I'm now wanting to consolidate 20 nearly-identical looking date range filters into one, but running into problems with query comprehension.

The date filtering logic, per POCO, is that UpdatedDate should be checked and its value compared to a threshold. If UpdatedDate was null (or the property didn't exist), then CreatedDate should be used instead.

</background>

I started by creating a static property getter on each POCO, which I named "DateFields". It's typed as an IEnumerable<Expression<Func<T, DateTime?>>> that looks like this:

get
{
    yield return x => x.UpdatedDate;
    yield return x => x.CreatedDate;
    yield return x => x.Date;
}

These fields are returned in the order I want them checked, and are unique to each POCO.

I then created a predicate to check each value against a high and low range:

public static bool DatesBetween<T> (T value, IEnumerable<Expression<Func<T, DateTime?>>> dates, DateTime? dateFrom, DateTime? dateTo)
{
    var firstDate = dates
        .Select(expression => expression.Compile())
        .FirstOrDefault(func => func(value) != null);

    if (firstDate == null)
        return false;

    var minDate = dateFrom.GetValueOrDefault(SqlDateTime.MinValue.Value);
    var maxDate = dateTo.GetValueOrDefault(SqlDateTime.MaxValue.Value);

    var actualDate = firstDate(value);

    return (minDate <= actualDate && actualDate <= maxDate);
}

However, as expected, when I try and use this in my IQueryable, I get a runtime exception, because there's no translation to DatesBetween in SQL. I was hoping that by keeping the expression tree, I could ... I dunno ... keep EF happy. The whole thing is used like this:

return Entities
    .GetEntitiesBySomeField(field := "magic value")
    .Where(entity => RepositoryExtensions.DatesBetween(entity, MyPOCO.MyDates, dateFrom, dateTo));

Is what I'm asking clear, and is there a way to accomplish this kind of generic filtering without doing an AsEnumerable () (which does work, but doesn't accomplish my goal of filtering on the DB).

Answer 1

I've done something similiar to this in a project recently. What I ended up doing was implementing the repository pattern and created a method on the repository called "ApplyFilter" which looked something like:

void ApplyFilter(Expression<Func<TEntity, bool>> predicate)
{
    if(this.resultSet == null)
    {
        this.resultSet = this.context.Set<TEntity>().AsQueryable();
    }

    this.resultSet = this.resultSet.Where(predicate);
}

And had a property on my repository called "ResultSet" which just returned the IQueryable<TEntity> resultSet field and forced an enumeration of the results (triggering EF's database call). This way I could apply all the dynamically created expressions in a clean why before enumerating the results.

The real difference between what my code does and what yours is doing is my code appends the boolean predicate to the final Expression tree where as you are trying to use yours within the final expression tree. My way basically creates/appends a WHERE clause to the SQL, yours tries to generate a SQL function call from the "DatesBetween" method.

Here is something to try:

    public static Expression<Func<TEntity, bool>> MakeDateRange<TEntity>(DateTime? dateFrom, DateTime? dateTo)
    {
        var et = typeof(TEntity);
        var param = Expression.Parameter(et, "a");
        var prop = et.GetProperty("UpdatedDate");
        Expression body = null, left = null, right = null;
        if (prop == null)
        {
            prop = et.GetProperty("CreatedDate");
            if (prop == null)
            {
                prop = et.GetProperty("Date");
            }
        }

        if (dateFrom.HasValue)
        {
            left = Expression.GreaterThanOrEqual(Expression.PropertyOrField(param, prop.Name), Expression.Constant(dateFrom.GetValueOrDefault()));
        }

        if (dateTo.HasValue)
        {
            right = Expression.LessThanOrEqual(Expression.PropertyOrField(param, prop.Name), Expression.Constant(dateTo.GetValueOrDefault()));
        }

        if (left != null && right != null)
        {
            body = Expression.AndAlso(left, right);
        }
        else if (left != null)
        {
            body = left;
        }
        else
        {
            body = right;
        }

        return Expression.Lambda<Func<TEntity, bool>>(body, param);
    }

Answer 2

So if I understand, you want to be able to run the same query on multiple tables that have a similar structure?

If that is the case, consider creating an interface that defines those properties and have your POCO's implement that interface. Then have your generic parameter have a restriction of that interface. From there, you should be able to write a generic query that uses the properties on the interface.