• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

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.
1 Solution
Fernando SotoRetiredCommented:
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


Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now