Linq2Xml - How to Group, Join and Sum etc..

I'm trying to join between two XML on AccountId and group by AccountId to aggregate the transactions.
The XMLs look like:

<Account Id="100" Name="MyAccount1"/>
<Account Id="99" Name="MyAccount2"/>
<Account Id="88" Name="MyAccount3"/>

<Transaction AccountId="100" Amount="250.00" PurchaseDate="12/04/2010"/>
<Transaction AccountId="100" Amount="500.00" PurchaseDate="11/04/2010"/>
<Transaction AccountId="99" Amount="600.00" PurchaseDate="12/04/2010"/>
<Transaction AccountId="99" Amount="250.00" PurchaseDate="20/04/2010"/>

I would like my output to be a collection of objects like this:
AccountName="MyAccount1" AccountId="100" Balance="750.00" LastUpdate="12/04/2010"
AccountName="MyAccount2" AccountId="99" Balance="850.00" LastUpdate="20/04/2010"

LastUpdate should be the last purchase date on the transaction group.
Balance should be the aggregation (Sum) of the amounts in the transaction.
I'm not interested in Accounts that don't have transactions (like AccountId="88")

I'd like C# code pls.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Fernando SotoConnect With a Mentor RetiredCommented:
Hi breezback;

The code snippet below should do what you want. Please note that I have place CultureInfo object to test the code. If you do not need it there you can remove it.

// Define cultures to be used to parse dates.
CultureInfo culture = CultureInfo.CreateSpecificCulture("he-IL");

XElement acct = XElement.Load("Accounts.xml");
XElement trans = XElement.Load("Transactions.xml");

var report = from a in acct.Descendants("Account")
             let ACCT = a.Attribute("Id").Value
             let TRANS = trans.Descendants("Transaction").Where(A => A.Attribute("AccountId").Value == ACCT)
             where TRANS.Count() > 0
             select new
                 AccountName = a.Attribute("Name").Value,
                 AccountId = ACCT,
                 Balance = TRANS.Select(t => Convert.ToDecimal(t.Attribute("Amount").Value)).Sum(A => A),
                 LastUpdate = TRANS.Select(d => DateTime.Parse(d.Attribute("PurchaseDate").Value, culture)).OrderByDescending(d => d).FirstOrDefault()

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.