How can I perform a nested Join, Add, and Group in LINQ?

Question

I have two objects, let's call them Input and Output

Input has properties Input_ID, Label, and Input_Amt
Output has properties Output_ID and Output_Amt

I want to perform the equivalent SQL statement in LINQ:

SELECT Label, Sum(Added_Amount) as Amount FROM
    (SELECT I.Label, I.Input_Amt + ISNULL(O.Output_Amt, 0) as Added_Amount
    FROM Input I LEFT OUTER JOIN Output O ON I.Input_ID = O.Output_ID)
GROUP BY Label

For the inner query, I'm writing something like:

var InnerQuery = from i in input
                 join o in output
                 on i.Input_ID equals o.Output_ID into joined
                 from leftjoin in joined.DefaultIfEmpty()
                 select new
                 {
                     Label = i.Label,
                     AddedAmount = (i.Input_Amt + leftjoin.Output_Amt)
                 };

In testing, however, the statement returns null. What gives?

Also, how can I continue the desired query and perform the group after I've added my amounts together, all within a single LINQ statement?

Answer 1

Which LINQ provider is this actually using? Are you actually talking to a database, or just working in-process? If you're using LINQ to SQL, you can turn the log on to see what SQL is being generated.

I'm sure that InnerQuery itself won't be null - how are you examining the output?

Answer 2

Okay, now that I understand what's going on a bit better, the main problem is that you haven't got the equivalent of the ISNULL bit. Try this instead:

var InnerQuery = from i in input
                 join o in output
                 on i.Input_ID equals o.Output_ID into joined
                 from leftjoin in joined.DefaultIfEmpty()
                 select new
                 {
                     Label = i.Label,
                     AddedAmount = (i.Input_Amt + (leftjoin == null ? 0 : leftjoin.Output_Amt))
                 };

Answer 3

var labelsAndAmounts = input
  .GroupJoin
  (
    output,
    i => i.InputId,
    o => o.OutputId,
    (i, os) => new
    {
      i,
      oAmount = os.Any() ? os.Select(o => o.OutputAmt).Sum() : 0
    }
  )
  .GroupBy(x => x.i.Label)
  .Select(g => new
    {
      Label = g.Key,
      Amount = g.Select(x => x.i.InputAmt + x.oAmount).Sum()
    }
  );