How do I include an InvocationExpression that is only evaluated once in an expression tree for linq to sql?

Question

I'm trying to manually combine expression trees to accomplish a level of modularity that seems to allude me using the standard linq operators. The code essentially creates an expression tree that uses one expression to decide which of two other expressions to call. One of the other expressions requires an extra parameter that is itself obtained using another expression. This parameter is used to obtain multiple values, but for each access the expression that retrieves the parameter is repeated. I've included the code and output to better explain:

public void Test() {
    var parameters = ProjectionOne.Parameters;
    Expression<Func<Foo, bool>> isType = f => f.TypeId == 1;
    Expression<Func<Foo, Satellite>> satSelector = f => f.Satellites.Single();
    var satelliteSelector = Expression.Invoke(satSelector, parameters[0]);
    var test = Expression.Lambda<Func<Foo, Bar>>(
        Expression.Condition(
            Expression.Invoke(isType, parameters[0]),
            Expression.Invoke(ProjectionOne, parameters[0]),
            Expression.Invoke(ProjectionTwo, parameters[0], satelliteSelector)), parameters);
}

public Expression<Func<Foo, Bar>> ProjectionOne {
    get {
        return foo => new Bar() {
            Id = foo.Id
        };
    }
}

public Expression<Func<Foo, Satellite, Bar>> ProjectionTwo {
    get {
        return (foo, sat) => new Bar() {
            Id = foo.Id,
            Start = sat.Start,
            End = sat.End
        };
    }
}

When I run this query on a database the SQL is produced as follows:

SELECT [t0].[value], [t0].[value2] AS [Start], [t0].[value3] AS [End], [t0].[Id] AS [Id]
FROM (
    SELECT 
        (CASE 
            WHEN [t0].[TypeId] = @p0 THEN 1
            WHEN NOT ([t0].[TypeId] = @p0) THEN 0
            ELSE NULL
         END) AS [value], (
        SELECT [t2].[Start]
        FROM [dbo].[Satellite] AS [t2]
        WHERE [t2].[Id] = [t0].[Id]
        ) AS [value2], (
        SELECT [t2].[End]
        FROM [dbo].[Satellite] AS [t2]
        WHERE [t2].[Id] = [t0].[Id]
        ) AS [value3], [t0].[Id]
    FROM [dbo].[Foo] ) AS [t0]

The problem is the duplicate sub selects. One retrieves the 'Start' value, while the other retrieves the 'End' value. It would be much better if they were both retrieved from a single sub select. How do I change the construction of the expression tree to enforce this? Also, I do understand that there are simpler ways to perform this query, however it is part of a larger framework not shown here that can only be achieved by being able to manually assemble expression trees from a large set of re-useable expressions.

Answer 1

I suspect that you've already done everything you can (since you are already using a projection, that the engine is choosing to express as individual sub-selects).

The only other things I can think of are (for example) using a table-valued UDF (via the data-context) to get the start/end - see whether it flattens that into the query.

Have you profiled the query and compared to your preferred layout? It might well be that the profile is identical (i.e. the TSQL optimizer has dealt with it). In which case, no need to be overly concerned.