Is it possible to parameterise a lambda in Linq to Entities Select

Question

I have 2 entity framework queries that are almost identical apart the lambda for 2 properties; Location and DateTime in the Select() method.

var departQuery = _dataContext
    .Job                
    .Where(j => j.Departure.DateTime >= startDate && j.Departure.DateTime <= endDate)
    .Select(j => new DispatchDashboardItem()
    {
        JobId = j.Id,
        Direction = "PickUp",
        CustomerName = j.Driver.Name,
        Vehicle = j.Vehicle,
        Location = j.Departure.MeetingLocation.Name,
        DateTime = j.Departure.DateTime,
    });

var returnQuery = _dataContext
    .Job                
    .Where(j => j.Return.DateTime >= startDate && j.Return.DateTime <= endDate)
    .Select(j => new DispatchDashboardItem()
    {
        JobId = j.Id,
        Direction = "DropOff",
        CustomerName = j.Driver.Name,
        Vehicle = j.Vehicle,
        Location = j.Return.MeetingLocation.Name,
        DateTime = j.Return.DateTime,
    });

I have tried creating an extension method to share the select which works without the func param, but which throws an exception then I use the location param:

    public static IQueryable<DashboardItem> SelectDashboardItem(this IQueryable<Job> query, 
            string direction, 
            Func<Job, MeetingDetail> location)
    {
        return query
            .Select(j => new DashboardItem()
            {
                JobId = j.Id,
                Direction = direction,
                CustomerName = j.Driver.Name,
                Vehicle = j.Vehicle,
                // This works without using the func
                Location = location(j).MeetingLocation.Name,
                DateTime = location(j).DateTime,                    
            });
    }

I see his error message:

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

Answer 1

Use this syntax with the let statement to achieve the composition in one swoop. You would need a base class, or better an interface, for the commonality between your Departure and Return entities.

var query = from job in _dataContext.Job
    let departureOrReturn = (direction == "PickUp" ? job.Departure : job.Return) as BaseReturnOrDeparture
    where (departureOrReturn.DateTime >= startDate && departureOrReturn.DateTime <= endDate)
    select new DispatchDashboardItem
    {
        JobId = job.Id,
        Direction = direction,
        CustomerName = job.Driver.Name,
        Vehicle = job.Vehicle,
        Location = deptartureOrReturn.MeetingLocation.Name,
        DateTime = deptartureOrReturn.DateTime,
    };

Answer 2

Instead of use Func as parameter use an Expression.Pass the same lambda expression you are using as parameter, it will implicitly converted. Now to achieve what you need you will need to use LinqKit library :

public static IQueryable<DashboardItem> SelectDashboardItem(this IQueryable<Job> query, 
            string direction, 
            Expression<Func<Job, MeetingDetail>> location)
    {
      return query.AsExpandable()
        .Select(j => new DashboardItem()
        {
            JobId = j.Id,
            Direction = direction,
            CustomerName = j.Driver.Name,
            Vehicle = j.Vehicle,
            // This works without using the func
            Location = location.Invoke(j).MeetingLocation.Name,
            DateTime = location.Invoke(j).DateTime,                    
        });
    }

Explanation:

ToExpandable creates a thin wrapper around the DLINQ Table object. Thanks to this wrapper you can use second method called Invoke that extends the Expression class to invoke the lambda expression while making it still possible to translate query to T-SQL. This works because when converting to the expression tree, the wrapper replaces all occurrences of Invoke method by expression trees of the invoked lambda expression and passes these expressions to DLINQ that is able to translate the expanded query to T-SQL.