How to reuse field filter in LINQ for objects

Question

I use EF and have a database table that has several date time fields that are populated since various operations are performed on the record. I am currently creating a reporting system that includes filtering by these dates, but since the filters (this date is within the range, etc.) have the same behavior in each field, I would like to reuse my filtering logic, so I only write one date filter and use it in each field.

My initial filtering code looks something like this:

DateTime? dateOneIsBefore = null; DateTime? dateOneIsAfter = null; DateTime? dateTwoIsBefore = null; DateTime? dateTwoIsAfter = null; using (var context = new ReusableDataEntities()) { IEnumerable<TestItemTable> result = context.TestItemTables .Where(record => ((!dateOneIsAfter.HasValue || record.DateFieldOne > dateOneIsAfter.Value) && (!dateOneIsBefore.HasValue || record.DateFieldOne < dateOneIsBefore.Value))) .Where(record => ((!dateTwoIsAfter.HasValue || record.DateFieldTwo > dateTwoIsAfter.Value) && (!dateTwoIsBefore.HasValue || record.DateFieldTwo < dateTwoIsBefore.Value))) .ToList(); return result; } 

This works fine, but I would rather reduce the duplicate code in the Where methods, since the filter algorithm is the same for each date field.

I would prefer something similar to the following (I will create a class or structure for filter values ​​later), where I can encapsulate the matching algorithm, using, possibly, the extension method:

 DateTime? dateOneIsBefore = null; DateTime? dateOneIsAfter = null; DateTime? dateTwoIsBefore = null; DateTime? dateTwoIsAfter = null; using (var context = new ReusableDataEntities()) { IEnumerable<TestItemTable> result = context.TestItemTables .WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter) .WhereFilter(record => record.DateFieldTwo, dateTwoIsBefore, dateTwoIsAfter) .ToList(); return result; } 

If the extension method might look something like this:

 internal static IQueryable<TestItemTable> WhereFilter(this IQueryable<TestItemTable> source, Func<TestItemTable, DateTime> fieldData, DateTime? dateIsBefore, DateTime? dateIsAfter) { source = source.Where(record => ((!dateIsAfter.HasValue || fieldData.Invoke(record) > dateIsAfter.Value) && (!dateIsBefore.HasValue || fieldData.Invoke(record) < dateIsBefore.Value))); return source; } 

Using the code above if my filtering code is as follows:

 IEnumerable<TestItemTable> result = context.TestItemTables .WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter) .WhereFilter(record => record.DateFieldTwo, dateTwoIsBefore, dateTwoIsAfter) .ToList(); 

I get the following exception:

 A first chance exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll Additional information: LINQ to Entities does not recognize the method 'System.DateTime Invoke(RAC.Scratch.ReusableDataFilter.FrontEnd.TestItemTable)' method, and this method cannot be translated into a store expression. 

The problem is using Invoke to get the specific field that is being requested, since this method does not solve SQL very well, because if I change my filtering code to the following, it will work without errors:

 IEnumerable<TestItemTable> result = context.TestItemTables .ToList() .AsQueryable() .WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter) .WhereFilter(record => record.DateFieldTwo, dateTwoIsBefore, dateTwoIsAfter) .ToList(); 

The problem is that the code (using ToList in the entire table before filtering using the extension method) retrieves the entire database and queries it as objects instead of querying the base database, so it does not scale.

I also explored using PredicateBuilder from Linqkit, but couldn't find a way to write code without using the Invoke method.

I know there are methods where you can express parts of a query as strings that include field names, but I would prefer to use a more secure way to write this code.

In addition, in an ideal world, I could redesign the database to have multiple “date” records associated with one “item” record, but I have no right to change the database schema this way.

Is there another way to write the extension so that it doesn't use Invoke, or do I need to reuse my filtering code in a different way?

Answer 1

Yes, LinqKit is the way here. But you are missing some fragments in your extension method:

 internal static IQueryable<TestItemTable> WhereFilter(this IQueryable<TestItemTable> source, Expression<Func<TestItemTable, DateTime>> fieldData, DateTime? dateIsBefore, DateTime? dateIsAfter) { source = source.AsExpandable().Where(record => ((!dateIsAfter.HasValue || fieldData.Invoke(record) > dateIsAfter.Value) && (!dateIsBefore.HasValue || fieldData.Invoke(record) < dateIsBefore.Value))); return source; } 

I changed the second parameter to Expression<Func<TestItemTable, DateTime>> and added the missing call to the LinqKit AsExpandable() method. Thus, Invoke() will call LinqKit Invoke() , which can then do its magic.

Using:

 IEnumerable<TestItemTable> result = context.TestItemTables .WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter) .WhereFilter(record => record.DateFieldTwo, dateTwoIsBefore, dateTwoIsAfter) .ToList(); 

Answer 2

There is not much duplication in your case, but still I will show how you can do what you want with raw expressions (as an example):

 internal static class QueryableExtensions { internal static IQueryable<T> WhereFilter<T>(this IQueryable<T> source, Expression<Func<T, DateTime>> fieldData, DateTime? dateIsBefore, DateTime? dateIsAfter) { if (dateIsAfter == null && dateIsBefore == null) return source; // this represents you "record" parameter in lambda var arg = Expression.Parameter(typeof(T), "record"); // this is the name of your field ("DateFieldOne") var dateFieldName = ((MemberExpression)fieldData.Body).Member.Name; // this is expression "c.DateFieldOne" var dateProperty = Expression.Property(arg, typeof(T), dateFieldName); Expression left = null; Expression right = null; // this is "c.DateFieldOne > dateIsAfter" if (dateIsAfter != null) left = Expression.GreaterThan(dateProperty, Expression.Constant(dateIsAfter.Value, typeof(DateTime))); // this is "c.DateFieldOne < dateIsBefore" if (dateIsBefore != null) right = Expression.LessThan(dateProperty, Expression.Constant(dateIsBefore.Value, typeof(DateTime))); // now we either combine with AND or not, depending on values Expression<Func<T, bool>> combined; if (left != null && right != null) combined = Expression.Lambda<Func<T, bool>>(Expression.And(left, right), arg); else if (left != null) combined = Expression.Lambda<Func<T, bool>>(left, arg); else combined = Expression.Lambda<Func<T, bool>>(right, arg); // applying that to where and done. source = source.Where(combined); return source; } } 

The call will be the same as you expect:

 WhereFilter(record => record.DateFieldOne, dateOneIsBefore, dateOneIsAfter) 

Working with expressions may look strange at first, but at least for simple cases there is nothing complicated.