Solved

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

Posted on 2010-09-14
1
588 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 63

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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!
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

632 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