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

Question

I have two objects, name them and Input Output

InputIt has the property Input_ID, Labeland has properties andInput_Amt
OutputOutput_IDOutput_Amt

I want to execute 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 an internal query, I write 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)
                 };

However, during testing, the operator returns null. What gives?

Also, how can I continue with the requested query and execute the group after I added my sums together, all in one LINQ expression?

Answer 1

Well, now that I understand what is going on a little better, the main problem is that you do not have the equivalent of the ISNULL bit. Try 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 2

LINQ ? ? LINQ to SQL, , , SQL .

, InnerQuery - ?

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()
    }
  );