Problem in LINQ Query

Question

In my table I am having my client Id as

Actual Input:-

<client>
    <ClientId>421</ClientId>
    <Amount>100</Amount>
<client>
<client>
    <ClientId>426</ClientId>
    <Amount>200</Amount>
<client>
<client>
    <ClientId>421</ClientId>
    <Amount>300</Amount>
<client>
<client>
    <ClientId>427</ClientId>
    <Amount>400</Amount>
<client>
<client>
    <ClientId>429</ClientId>
    <Amount>500</Amount>
<client>
<client>
    <ClientId>436</ClientId>
    <Amount>600</Amount>
<client>
<client>
    <ClientId>421</ClientId>
    <Amount>900</Amount>
<client>

I need to query this using LINQ.

I need to sum the amount field for only unique Client Id and show it as Single record(eg: for 421 there are three clientids records. But I need only one 421 with the summation of all the client amount and the amount should display as (1300)

And I need the other client Id values as displayed with this

Finally My Output should be like this

421     1300
426     200
427     400
429     500
436     600

How can I achieve this LINQ query using XML in LINQ

Answer 1

This worked for me. Your XML example is wrong too, missing closing </client>

var result =
    from c in document.Elements("client")
    group c by c.Element("ClientId").Value into grouped
    select new 
    { 
       ClientId = grouped.Key, 
       TotalSum = grouped.Sum(g => Decimal.Parse(g.Element("Amount").Value)) 
    };

foreach (var group in result)
    Console.WriteLine(group.ClientId + ": " + group.TotalSum);

EDIT: I loaded the XDocument like follow for the example:

var document = XElement.Parse(
@"      
<clients>      
<client>
    <ClientId>421</ClientId>
    <Amount>100</Amount>
</client>
<client>
    <ClientId>426</ClientId>
    <Amount>200</Amount>
</client>
<client>
    <ClientId>421</ClientId>
    <Amount>300</Amount>
</client>
<client>
    <ClientId>427</ClientId>
    <Amount>400</Amount>
</client>
<client>
    <ClientId>429</ClientId>
    <Amount>500</Amount>
</client>
<client>
    <ClientId>436</ClientId>
    <Amount>600</Amount>
</client>
<client>
    <ClientId>421</ClientId>
    <Amount>900</Amount>
</client>
</clients>", LoadOptions.PreserveWhitespace);

Answer 2

Firstly, make sure your xml is correct. your closing tags are missing the /, also you'll need a root element.

XElement Results = XElement.Load(xmlfilepath);
XNamespace NameSpace = Results.Name.Namespace;
var xe = (from c in Results.Elements(NameSpace + "client")
          group c by c.Element(NameSpace + "ClientId").Value  into g
          select new { clientid = g.Key, AmountTotal = g.Sum(h=>decimal.Parse(h.Element(NameSpace+"Amount").Value)) }).ToList();

you can then foreach through the result and do what you need to do