Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-09-14
1
Medium Priority
?
597 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 64

Accepted Solution

by:
Fernando Soto earned 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

782 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