CODEWITHSUNDEEP
c#linqentity-frameworkc#-4.0linq-to-entities
Sander Rijken
Sander Rijken

Reputation: 21615

Arithmetic operations on subqueries using Expression.Subtract(?)

I'm trying to create an expression tree that's similar to performing subqueries like:

SELECT (SELECT Sum(Foo) FROM Bar1) - (SELECT Sum(Foo) FROM Bar2))

I'm trying to reuse 2 expression trees that are too complex to repeat.

What I have right now is 2 (simplified) expression trees:

Expression<Func<Bar, int>> SumBar1 =
    (bar) => (from b in bar.Something
              where b.Type = 1
              select b).Sum();

Expression<Func<Bar, int>> SumBar2 =
    (bar) => (from b in bar.Something
              where b.Type = 2
              select b).Sum();

I've already tried to use Expression.Subtract:

Expression foo = Expression.Subtract(SumBar1, SumBar2);

This fails with the error:

The binary operator Subtract is not defined for the types 'System.Func2[Bar,System.Int32]' and 'System.Func2[Bar,System.Int32]'.

I also tried using Expression.Invoke to call the trees:

Expression.Subtract( Expression.Invoke(SumBar1, Expression.Parameter(typeof(Bar)), Expression.Invoke(SumBar2, Expression.Constant(typeof(Bar))));

But then I get:

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

Is there any way to combined the two expression trees into a new tree, subtracting them, and passing along the parameter?

Upvotes: 1

Views: 1665

Answers (2)

Chris Pitman
Chris Pitman

Reputation: 13104

This comes up a lot when dynamically building up Linq queries for EF, and you almost got there. I've written code to do this by hand before, but doing with with LinqKit is so much easier.

Once you are using LinqKit, just write a lambda expression that invokes both subexpressions and subtracts the results. Then call "Expand" on the resulting expression and save the result. The new expression will no longe rhave the invokes because the arguments passed to the inner expressions have been substituted into their bodies and the method calls removed.

Expression<Func<Bar, int>> SumBar1 = 
    (bar) => (from b in bar.Something 
              where b.Type = 1 
              select b).Sum(); 

Expression<Func<Bar, int>> SumBar2 = 
    (bar) => (from b in bar.Something 
              where b.Type = 2 
              select b).Sum();

Expression<Func<Bar, int>> Combined = (bar) => SumBar1.Invoke(bar) - SumBar2.Invoke(bar);
Expression<Func<Bar, int>> Result = Combined.Expand();

Upvotes: 2

Onkelborg
Onkelborg

Reputation: 3997

Now I don't know EF, but LINQ, and this sounds a bit strange to me. Why do you want to subtract a delegate from another?

More appropriate would be something like:

Expression<Func<Bar, int>> sumBar1 =
    (bar) => (from b in bar.Something
              where b.Type = 1
              select b).Sum();

Expression<Func<Bar, int>> sumBar2 =
    (bar) => (from b in bar.Something
              where b.Type = 2
              select b).Sum();

Expression<Func<Bar, int>> totalSum =
    bar =>
              sumBar1(bar) - sumBar2(bar);

totalSum(DB.GetBar());

I have to make a reservation though, I haven't really tested this, and this can be totally wrong.. :)

Upvotes: 0

Related Questions