Reusing existing linq expressions in a compiled query

Question

Consider the following code which provides two methods: One to return an IQueryable, and one which leverages a compiled query to very efficient return the location matching a specific ID:

    public IQueryable<Location> GetAllLocations()
    {
        return from location in Context.Location
               where location.DeletedDate == null
                     && location.Field1 = false
                     && location.Field2 = true
                     && location.Field3 > 5
               select new LocationDto 
               {
                     Id = location.Id,
                     Name = location.Name
               }
    }


    private static Func<MyDataContext, int, Location> _getByIdCompiled;

    public Location GetLocationById(int locationId)
    {
        if (_getByIdCompiled == null) // precompile the query if needed
        {
            _getByIdCompiled = CompiledQuery.Compile<MyDataContext, int, Location>((context, id) => 

                (from location in Context.Location
                where location.DeletedDate == null
                      && location.Field1 = false
                      && location.Field2 = true
                      && location.Field3 > 5
                      && location.Id == id
                select new LocationDto {
                     Id = location.Id,
                     Name = location.Name
                })).First());
        }

        // Context is a public property on the repository all of this lives in
        return _getByIdCompiled(Context, locationId);
    }

This is a pretty big simplification of the actual code, but I think it gets the idea accross, and it works fine. The next thing I want to do is refactor the code, so that the common bit of the expression can be reused, since it will be used in many other types of compiled queries. In other words, this expression:

                from location in Context.Location
                where location.DeletedDate == null
                      && location.Field1 = false
                      && location.Field2 = true
                      && location.Field3 > 5
                select new LocationDto 
                {
                     Id = location.Id,
                     Name = location.Name
                };

How can I somehow capture this in a variable or function and reuse it in multiple compiled queries? My attempts so far have led to errors complaining about things not being translatable to SQL, Member access not allowed, etc.

Update: Another potentially better way I could have asked this question is as follows:

Consider the two compiled queries below:

_getByIdCompiled = CompiledQuery.Compile<MyDataContext, int, LocationDto>((context, id) => 
      (from location in Context.Location // here
      where location.DeletedDate == null // here
            && location.Field1 = false // here
            && location.Field2 = true // here
            && location.Field3 > 5 // here
            && location.Id == id
      select new LocationDto { // here
          Id = location.Id, // here
          Name = location.Name
      })).First()); // here

_getByNameCompiled = CompiledQuery.Compile<MyDataContext, int, LocationDto>((context, name) => 
       (from location in Context.Location // here
     where location.DeletedDate == null // here
         && location.Field1 = false // here
         && location.Field2 = true // here
         && location.Field3 > 5 // here
         && location.Name == name
     select new LocationDto { // here
       Id = location.Id, // here
       Name = location.Name // here
     })).First()); // here

All of the lines marked // here are duplicate very un-dry pieces of code. (In my code base, this actually 30+ lines of code.) How do I factor it out and make it reusable?

Answer 1

So, this whole thing is somewhat odd in that the Compile method needs to not only see the Expression objects passed to each query operator (Where, Select, etc.) as something it can understand, but it needs to see the whole query, including the use of all of the operators, as something it can comprehend as Expression objects. This pretty much removes more traditional query composition as an option.

This is going to get a tad messy; more so than I would really like, but I don't see a whole lot of great alternatives.

What we're going to do is create a method to construct our queries. It's going to accept a filter as a parameter, and that filter will be an Expression that represents a filter for some object.

Next we're going to define a lambda that will look almost exactly like what you would pass to Compile, but with an extra parameter. That extra parameter will be of the same type as our filter, and it will represent that actual filter. We'll use that parameter, instead of the filter, throughout the lambda. We'll then use a UseIn method to replace all instances of that third parameter in our new lambda with the filter expression that we are providing to the method.

Here is the method to construct the query:

private static Expression<Func<MyDataContext, int, IQueryable<LocationDto>>>
    ConstructQuery(Expression<Func<Location, bool>> filter)
{
    return filter.UseIn((MyDataContext context, int id,
        Expression<Func<Location, bool>> predicate) =>
        from location in context.Location.Where(predicate)
        where location.DeletedDate == null
                && location.Field1 == false
                && location.Field2 == true
                && location.Field3 > 5
                && location.Id == id
        select new LocationDto
        {
            Id = location.Id,
            Name = location.Name
        });
}

Here is the UseIn method:

public static Expression<Func<T3, T4, T5>>
    UseIn<T1, T2, T3, T4, T5>(
    this Expression<Func<T1, T2>> first,
    Expression<Func<T3, T4, Expression<Func<T1, T2>>, T5>> second)
{
    return Expression.Lambda<Func<T3, T4, T5>>(
        second.Body.Replace(second.Parameters[2], first),
        second.Parameters[0],
        second.Parameters[1]);
}

(The typing is a mess here, and I can't figure out how to give the generic types meaningful names.)

The following method is used to replace all instances of one expression with another:

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

Now that we've gotten through this gory mess, comes the easy part. ConstructQuery should be able to be modified at this point to represent your real query, without much difficulty.

To call this method we simply need to provide whatever filter we want applied to this alteration of the query, such as:

var constructedQuery = ConstructQuery(location => location.Id == locationId); 

Answer 2

Linq statements have to end in either select or group clause so you can't cut out part of the query and store it elsewhere, but if you will always be filtering by the same four conditions, you can use the lambda syntax instead, and then add any additional where clauses in new queries.

And as pointed out by @Servy, you need to call First() or FirstOrDefault() to get a single element from the query result.

IQueryable<Location> GetAllLocations()
{
    return Context.Location.Where( x => x.DeletedDate == null
                                     && x.Field1      == false
                                     && x.Field2      == true
                                     && x.Field3       > 5
                                 ).Select( x => x );
}

Location GetLocationById( int id )
{
    return ( from x in GetAllLocations()
             where x.Id == id
             select x ).FirstOrDefault();
}

//or with lambda syntax

Location GetLocationById( int id )
{
    return GetAllLocations()
               .Where( x => x.Id == id )
               .Select( x => x )
               .FirstOrDefault();
}