Solved

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

Posted on 2010-09-14
1
566 Views
Last Modified: 2012-05-10
I'm trying to join between two XML on AccountId and group by AccountId to aggregate the transactions.
The XMLs look like:

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

<Transactions>
<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"/>
<Accounts>

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.
0
Comment
Question by:breezback
1 Comment
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 33673794
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.

Fernando
// 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

0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now