How to prevent multiple joins to the same table when using PredicateBuilder

How to prevent multiple joins to the same table when using PredicateBuilder

In this example I am using the NorthWind database supplied by MS.
I am trying to use a predicate builder to assemble multiple
Expression<Func<T,bool>>
into a single expression tree. It mostly works great in my opinion, but
there is one major flaw that I can't seem to address.
I currently have two expressions defined as such:
Expression<Func<Customer, bool>> UnitPriceLessThan2 = c =>
c.Orders.Any(o => o.Order_Details.Any(d => d.Product.UnitPrice <= 2));
Expression<Func<Customer, bool>> UnitPriceGreaterThan5 = c =>
c.Orders.Any(o => o.Order_Details.Any(d => d.Product.UnitPrice >= 5));
I use the universal predicate builder from Pete Montgomery to OR these two
together like such:
Expression<Func<Customer,bool>> PriceMoreThan5orLessThan2 =
UnitPriceLessThan2.Or(UnitPriceGreaterThan5);
Both of those expressions need to navigate to the Product entity through
the same path, so it makes sense to reuse the same sub query for both
conditions. If I was just manually writing the condition it would look
something like this:
Expression<Func<Customer,bool>> PriceMoreThan5orLessThan2 = c =>
c.Orders.Any(o => o.Order_Details.Any(d => d.Product.UnitPrice >= 5 ||
d.Product.UnitPrice <= 2));
However, because of the requirements to build these predicates
dynamically, I can't do that because there would be hundreds of possible
combinations...or more.
So my question is how can I prevent LINQ to Entities from creating a query
like this:
SELECT
/*all the customer columns*/
FROM [dbo].[Customers] AS [Extent1]
WHERE ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent2].[OrderID] AS [OrderID]
FROM [dbo].[Orders] AS [Extent2]
WHERE [Extent1].[CustomerID] = [Extent2].[CustomerID]
) AS [Project1]
WHERE EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Order Details] AS [Extent3]
INNER JOIN [dbo].[Products] AS [Extent4] ON [Extent3].[ProductID]
= [Extent4].[ProductID]
WHERE ([Project1].[OrderID] = [Extent3].[OrderID]) AND
([Extent4].[UnitPrice] <= cast(2 as decimal(18)))
)
)) OR ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent5].[OrderID] AS [OrderID]
FROM [dbo].[Orders] AS [Extent5]
WHERE [Extent1].[CustomerID] = [Extent5].[CustomerID]
) AS [Project4]
WHERE EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Order Details] AS [Extent6]
INNER JOIN [dbo].[Products] AS [Extent7] ON [Extent6].[ProductID]
= [Extent7].[ProductID]
WHERE ([Project4].[OrderID] = [Extent6].[OrderID]) AND
(([Extent7].[UnitPrice] >= cast(5 as decimal(18)))))));
The problem being that we've created two EXISTS sub queries when we really
only needed one.
Instead I would like the query to look like this:
SELECT
/*all the customer columns*/
FROM [dbo].[Customers] AS [Extent1]
WHERE( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent5].[OrderID] AS [OrderID]
FROM [dbo].[Orders] AS [Extent5]
WHERE [Extent1].[CustomerID] = [Extent5].[CustomerID]
) AS [Project4]
WHERE EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Order Details] AS [Extent6]
INNER JOIN [dbo].[Products] AS [Extent7] ON [Extent6].[ProductID]
= [Extent7].[ProductID]
WHERE ([Project4].[OrderID] = [Extent6].[OrderID]) AND
(([Extent7].[UnitPrice] >= cast(5 as decimal(18))) OR
([Extent7].[UnitPrice] <= cast(2 as decimal(18))))
)
))
Can I somehow store and reuse the navigation path as an expression and
then inject the two conditions with their appropriate user supplied
operators and values into that?
Or use some expression visitor implementation to...I don't know what
exactly, find and replace?
Thank you for reading my rather lengthy question :)