Finding intersecting ranges – Small variant using Expression.Constant

Last time we saw how use expression to find intersecting ranges. Here’s another useful overload of the same method – it is probably common to have one of ranges on the entity object (for example – the employee’s vacation), while knowing the other (for example the week of training). In that case, we can revise the implementation a little to use to concrete values:

public static Expression<Func<TEntity, bool>>
    RangesIntersect<TEntity, TComparable>(TComparable from, TComparable to,
                                        Expression<Func<TEntity, TComparable>> getEntityRangeStart,
                                        Expression<Func<TEntity, TComparable>> getEntityRangeEnd)
{
    ParameterExpression newLambdaParameter = Expression.Parameter(typeof(TEntity));
    var rewireStart = RewireLambdaExpression(getEntityRangeStart, newLambdaParameter);
    var rewireEnd = RewireLambdaExpression(getEntityRangeEnd, newLambdaParameter);

    return Expression.Lambda<Func<TEntity, bool>>(
        RangesIntersect(Expression.Constant(from), Expression.Constant(to),
                        rewireStart.Body, rewireEnd.Body),
        newLambdaParameter);
}

public static IQueryable<TEntity> WhereRangesIntersect<TEntity, TComparable>(
    this IQueryable<TEntity> query,
    TComparable range1Start,
    TComparable range1End,
    Expression<Func<TEntity, TComparable>> getEntityRange2Start,
    Expression<Func<TEntity, TComparable>> getEntityRange2End)
{
    return query.Where(RangesIntersect(range1Start, range1End, getEntityRange2Start, getEntityRange2End));
}

Example Use

using (VacationsEntities model = new VacationsEntities())
{
    var trainingStart = new DateTime(2013, 1, 1);
    var trainingEnd = trainingStart.AddDays(7);
    var query = model.Vacations
                     .WhereRangesIntersect(trainingStart,
                                           trainingEnd,
                                           v => v.Start.Value,
                                           v => v.End.Value);
    // ...

Resulting SQL

The Entity Framework chose to convert the two Expression.Constants to date literals instead of using SQL parameters:

SELECT 
[Extent1].[VacationId] AS [VacationId], 
[Extent1].[EmployeeId] AS [EmployeeId], 
[Extent1].[Start] AS [Start], 
[Extent1].[End] AS [End]
FROM [Vacations] AS [Extent1]
WHERE (convert(datetime, '2013-01-01 00:00:00.000', 121) <= [Extent1].[End]) AND
      ([Extent1].[Start] <= convert(datetime, '2013-01-08 00:00:00.000', 121))

Advertisement