Generating a parameter for Linq to handle ranges in dates

Question

I want to achieve the following expression or as near as possible.

IRangePredicate range = new Range(); 

DbContext context = new dbModel();

context.Table1.Where(x => range.IsInRange(x.CreatedAt) && x.type == 1).ToList();

and range will produce a partial expression for the linq query, that can either be resolved as:

CreatedAt >= from && CreatedAt <= to 

Or

CreatedAt >= from

Or

CreatedAt <= To

to be used in the linq query.

Eventually, I would like to extend this method to include the possibilities of less or more without equals as well.

and use it as a sort of "arguement dependency injection". However, my attempts fail to even compile, as either Expression<Func<DateTime, bool>> can't be used as a partial parameter, and I need to define the following query for these special filters. Which I don't want to do. I want it to read as "normal" Linq.

Or I need to simply insert them as Func only. Which might work, but as soon as I try to do that on a Context Linq Query, the thing explodes, because Entity Framework, does not play well if it is not formatted as an Expression

Can anyone guide me in the right direction?

Example of what I tried: (Please note this does not compile, because that is my entire issue :D )

EDIT From here: -I have commented out the line of code that doesn't compile, so you have a compilable example. It just doesn't work if you try to do it on a DbContext set.

 public interface IRangeFunctional
    {
        bool GetRange(DateTime param);
    }

    public interface IRange
    {
        Expression<Func<DateTime, bool>> GetRange(DateTime param);
    }

    public class RangeFunctional : IRangeFunctional
    {
        private DateTime _from;
        private DateTime _to;
        public RangeFunctional(DateTime from, DateTime to)
        {
            _from = from;
            _to = to;
        }

        public bool GetRange(DateTime param)
        {
            return param >= _from && param <= _to;
        }
    }

    public class Range : IRange
    {
        private DateTime _from;
        private DateTime _to;
        public Range(DateTime from, DateTime to)
        {
            _from = from;
            _to = to;
        }

        public Expression<Func<DateTime, bool>> GetRange(DateTime param)
        {
            return (x => param >= _from && param <= _to);
        }
    }

    public class Invoice
    {
        public DateTime CreatedAt { get; set; }
        public int typeId { get; set; }
    }

    [TestClass]
    public class TestRange
    {
        List<Invoice> list = new List<Invoice>()
        {
            new Invoice()
            {
                CreatedAt = new DateTime(2018,1,1,0,0,0), typeId = 1
            },
            new Invoice()
            {
                CreatedAt = new DateTime(2018,1,2,0,0,0), typeId = 1
            },
            new Invoice()
            {
                CreatedAt = new DateTime(2018,1,1,0,0,0), typeId = 2
            },
            new Invoice()
            {
                CreatedAt = new DateTime(2018,1,2,0,0,0), typeId = 2
            }
        };

        [TestMethod]
        public void RangeTest()
        {
            Range r = new Range(new DateTime(2018, 1, 1, 0, 0, 0), new DateTime(2018, 1, 2, 0, 0, 0));
            RangeFunctional rf = new RangeFunctional(new DateTime(2018, 1, 1, 0, 0, 0), new DateTime(2018, 1, 2, 0, 0, 0));
            List<Invoice> partialListFunc = list.Where(x => x.typeId == 2 && rf.GetRange(x.CreatedAt)).ToList();


            //List<Invoice> partialList = list.Where(x => x.typeId == 2 && r.GetRange(x.CreatedAt)).ToList();
            Assert.AreEqual(2, partialListFunc.Count);
        }
    }

Okay, So I added the base method that game me the idea, as a demo example, where I am just using ordinary "bool" to accomplish a pure search by link in generic collections.

However, I want to reuse this logic, or as close as possibe, to allow me to accomplish this towards a DbContext.

I have a base crud controller for any type of table towards the Db, however, I would like to enhance this bit, but letting the programmer to implement a strategy pattern over a partial classes generated from either code first, or db first models in C#.

However, in order to translate the Linq to SQL, I need to convert my "just bool" return type into expressions. I got that far. But How the heck do I make "subsets" of predicates, that can be unified over a single collection? I see some code examples that require you to chain the queries. And that might end up being the solution. This just seems so... ugly.

I just can't get my brain to think up the syntax to do this. And it is frustrating me :D Forgive me if this cannot be done, because I am simply stupid. It just seems sort of intuitive to me that this should be possible.

Answer 1

Here is a sample implementation. It uses an extension method for modifying Expressions to build a new Expression:

public static class ExpressionExt {
    /// <summary>
    /// Replaces a sub-Expression with another Expression inside an Expression
    /// </summary>
    /// <param name="orig">The original Expression.</param>
    /// <param name="from">The from Expression.</param>
    /// <param name="to">The to Expression.</param>
    /// <returns>Expression with all occurrences of from replaced with to</returns>
    public static Expression Replace(this Expression orig, Expression from, Expression to) => new ReplaceVisitor(from, to).Visit(orig);
}

/// <summary>
/// Standard ExpressionVisitor to replace an Expression with another in an Expression.
/// </summary>
public class ReplaceVisitor : ExpressionVisitor {
    readonly Expression from;
    readonly Expression to;

    public ReplaceVisitor(Expression from, Expression to) {
        this.from = from;
        this.to = to;
    }

    public override Expression Visit(Expression node) => node == from ? to : base.Visit(node);
}

Now you can build a filter interface and some implementations to represent differing types of filters and an IQueryable extension that uses it to filter:

public interface IFilter<TMember> {
    Expression<Func<TData, bool>> FilterFn<TData>(Expression<Func<TData, TMember>> memberFn);
}

public class FilterDateTimeRange : IFilter<DateTime?> {
    public DateTime? from;
    public DateTime? to;

    public FilterDateTimeRange(DateTime? fromDT, DateTime? toDT) {
        from = fromDT;
        to = toDT;
    }

    public Expression<Func<T, bool>> FilterFn<T>(Expression<Func<T, DateTime?>> memberFn) {
        Expression<Func<DateTime?, bool>> rangeBodyTemplate;
        if (from.HasValue) {
            if (to.HasValue)
                rangeBodyTemplate = dt => from.Value <= dt && dt <= to.Value;
            else
                rangeBodyTemplate = dt => from.Value <= dt;
        }
        else if (to.HasValue) {
            rangeBodyTemplate = dt => dt <= to.Value;
        }
        else
            rangeBodyTemplate = dt => true;

        return Expression.Lambda<Func<T, bool>>(rangeBodyTemplate.Body.Replace(rangeBodyTemplate.Parameters[0], memberFn.Body), memberFn.Parameters);
    }
}

public class FilterDateRange : IFilter<DateTime?> {
    public DateTime? from;
    public DateTime? to;

    public FilterDateRange(DateTime? fromDT, DateTime? toDT) {
        from = fromDT?.Date;
        to = toDT?.Date;
    }

    public Expression<Func<T, bool>> FilterFn<T>(Expression<Func<T, DateTime?>> memberFn) {
        Expression<Func<DateTime?, bool>> rangeBodyTemplate;
        if (from.HasValue) {
            if (to.HasValue)
                rangeBodyTemplate = dt => from <= (dt == null ? dt : dt.Value.Date) && (dt == null ? dt : dt.Value.Date) <= to;
            else
                rangeBodyTemplate = dt => from.Value <= (dt == null ? dt : dt.Value.Date);
        }
        else if (to.HasValue) {
            rangeBodyTemplate = dt => (dt == null ? dt : dt.Value.Date) <= to.Value;
        }
        else
            rangeBodyTemplate = dt => true;

        return Expression.Lambda<Func<T, bool>>(rangeBodyTemplate.Body.Replace(rangeBodyTemplate.Parameters[0], memberFn.Body), memberFn.Parameters);
    }
}

public class FilterStartsWith : IFilter<String> {
    public string start;

    public FilterStartsWith(string start) => this.start = start;

    public Expression<Func<T, bool>> FilterFn<T>(Expression<Func<T, string>> memberFn) {
        Expression<Func<string, bool>> rangeBodyTemplate;
        if (!String.IsNullOrEmpty(start))
            rangeBodyTemplate = s => s.StartsWith(start);
        else
            rangeBodyTemplate = s => true;

        return Expression.Lambda<Func<T, bool>>(rangeBodyTemplate.Body.Replace(rangeBodyTemplate.Parameters[0], memberFn.Body), memberFn.Parameters);
    }
}

public static class FilterExt {
    public static IQueryable<TData> WhereFilteredBy<TData, TMember>(this IQueryable<TData> src, IFilter<TMember> r, Expression<Func<TData, TMember>> memberFn) => src.Where(r.FilterFn(memberFn));
}

Given all this, you use it like so:

var r1 = new FilterDateTimeRange(DateTime.Now.AddDays(-1).Date, DateTime.Now.AddDays(-1).Date);
var yesterdayFilter = new FilterDateRange(DateTime.Now.AddDays(-1), DateTime.Now.AddDays(-1));

var r1a = Accounts.Where(r1.RangeFilter<Accounts>(a => a.Modified_date));
var ya = Accounts.WhereFilteredBy(yesterdayFilter, a => a.Modified_date);

Since the C# type inference engine isn't as sophisticated as e.g. F# and won't infer through return expressions, you must specify the type when using the standard Where but an IQueryable extension replacement Where can infer the type from the first parameter (e.g. Accounts).

Since the IFilter is generic, you can use other types of filters such as FilterStartsWith to filter on other types of fields:

List<Table1> Table1InRangeWithName(IFilter<DateTime?> range, IFilter<string> name) => context.Table1.WhereFilteredBy(range, t1 => t1.Modified_date).WhereFilteredBy(name, t1 => t1.Name).ToList();

And then call it with a pre-created FilterDataRange and FilterStartsWith:

var nameFilter = new FilterStartsWith("TEST");
var ans = Table1InRangeWithName(yesterdayFilter, nameFilter);