Click here to Skip to main content
15,883,705 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Friends,

I have an XML and i need group and sum YearlyAmts->netAmount based upon Account -> Type. I de- serialize the xml and make it as object. now i need to apply query over the object. I am able to group and sum yearly amount but my requirement is while grouping in need to consider Account type as well.

Expected Output

Type Year NetAmount
401k 2016 5000
VA 2016 40000

Below is the sample xml
XML
<YearlyContributions>
<Item1>
<contribution>
	<ContributionId>4</ContributionId>
	<CType>profitsharing</CType>
	<CSubType>Amount</CSubType>
	<TaxTreatment>PreTax</TaxTreatment>
	<Account>
		<AccountID>5</AccountID>
		<Type>401K</Type>
		<LimitGrp>401K</LimitGrp>
		<AcClient>
			<birthyear>1971</birthyear>
			<ClientID>2</ClientID>
			<endOfAnalysisYear>2066</endOfAnalysisYear>
			<Isprimary>N</Isprimary>
			<retirementYear>2040</retirementYear>
		</AcClient>
		<MarketValue>30000</MarketValue>
		<VAAccountType/>
	</Account>
	<income>
		<IncomeID>2</IncomeID>
		<Type>SB</Type>
		<Client>
			<birthyear>1971</birthyear>
			<ClientID>2</ClientID>
			<endOfAnalysisYear>2066</endOfAnalysisYear>
			<Isprimary>N</Isprimary>
			<retirementYear>2040</retirementYear>
		</Client>
		<OwnershipType>Single</OwnershipType>
		<TaxExempt>N</TaxExempt>
		<CertainGauranteePeriod>0</CertainGauranteePeriod>
		<SurvivorshipPercent>0</SurvivorshipPercent>
		<exclusionratio>0</exclusionratio>
		<ExclusionRatioExpectedDeathAge>0</ExclusionRatioExpectedDeathAge>
		<AmountPeriodID>4</AmountPeriodID>
	</income>
	<AmountPeriodID>0</AmountPeriodID>
</contribution>
<stateofmortality/>
<YearlyAmts>
	<Item1>
		<year>2016</year>
		<netAmount>50000</netAmount>
	</Item1>
	<Item2>
		<year>2017</year>
		<netAmount>51000</netAmount>
	</Item2>
	<Item3>
		<year>2018</year>
		<netAmount>52020</netAmount>
	</Item3>
</YearlyAmts>
</Item1>
<Item2>
<contribution>
	<ContributionId>5</ContributionId>
	<CType>profitsharing</CType>
	<CSubType>Percent</CSubType>
	<TaxTreatment>PreTax</TaxTreatment>
	<Account>
		<AccountID>9</AccountID>
		<Type>VA</Type>
		<LimitGrp>401K</LimitGrp>
		<AcClient>
			<birthyear>1970</birthyear>
			<ClientID>1</ClientID>
			<endOfAnalysisYear>2065</endOfAnalysisYear>
			<Isprimary>Y</Isprimary>
			<retirementYear>2039</retirementYear>
		</AcClient>
		<MarketValue>30000</MarketValue>
		<VAAccountType>401K</VAAccountType>
	</Account>
	<income>
		<IncomeID>1</IncomeID>
		<Type>SB</Type>
		<Client>
			<birthyear>1970</birthyear>
			<ClientID>1</ClientID>
			<endOfAnalysisYear>2065</endOfAnalysisYear>
			<Isprimary>Y</Isprimary>
			<retirementYear>2039</retirementYear>
		</Client>
		<OwnershipType>Single</OwnershipType>
		<TaxExempt>N</TaxExempt>
		<CertainGauranteePeriod>0</CertainGauranteePeriod>
		<SurvivorshipPercent>0</SurvivorshipPercent>
		<exclusionratio>0</exclusionratio>
		<ExclusionRatioExpectedDeathAge>0</ExclusionRatioExpectedDeathAge>
		<AmountPeriodID>3</AmountPeriodID>
	</income>
	<AmountPeriodID>0</AmountPeriodID>
</contribution>
<stateofmortality/>
<YearlyAmts>
	<Item25>
		<year>2016</year>
		<netAmount>40000</netAmount>
	</Item25>
	<Item26>
		<year>2017</year>
		<netAmount>40800</netAmount>
	</Item26>
	<Item27>
		<year>2018</year>
		<netAmount>41616</netAmount>
	</Item27>
</YearlyAmts>
</Item2>
</YearlyContributions>


Newly Added: Class structure
C#
public class YearlyContributions
{
    public List<ContributionItem> Item { get; set; }
}
public class ContributionItem
{
    public Contribution Contribution { get; set; }
    public string stateofmortality { get; set; }
    public YearlyAmt YearlyAmts { get; set; }
}
public class Contribution
{
    public int ContributionId { get; set; }
    public string CType { get; set; }
    public string CSubType { get; set; }
    public string TaxTreatment { get; set; }
    public Account Account { get; set; }
    public Income Income { get; set; }
    public int AmountPeriodID { get; set; }
}
public class Account
{
    public int AccountID { get; set; }
    public string Type { get; set; }
    public string LimitGrp { get; set; }
    public Client AcClient { get; set; }
    public double MarketValue { get; set; }
    public string VAAccountType { get; set; }
}
public class Client
{
    public int birthyear { get; set; }
    public int ClientID { get; set; }
    public int endOfAnalysisYear { get; set; }
    public string Isprimary { get; set; }
    public int retirementYear { get; set; }
}
public class Income
{
    public int IncomeID { get; set; }
    public string Type { get; set; }
    public Client Client { get; set; }
    public string OwnershipType { get; set; }
    public string TaxExempt { get; set; }
    public double CertainGauranteePeriod { get; set; }
    public double SurvivorshipPercent { get; set; }
    public double exclusionratio { get; set; }
    public double ExclusionRatioExpectedDeathAge { get; set; }
    public int AmountPeriodID { get; set; }
}
public class YearlyAmt
{
    public List<YearlyItem> YearlyAmts { get; set; }
}
public class YearlyItem
{
    public int year { get; set; }
    public double NetAmount { get; set; }
}


What I have tried:

C#
var lstcon = ycon.Item
        .SelectMany(s => s.YearlyAmts.YearlyAmts)
        .GroupBy(yr => yr.year)
        .Select(g => new
        {
            Key = g.Key,
            Value = g.Sum(s => s.NetAmount),
        })
        .ToList();
Posted
Updated 5-Sep-16 10:18am
v3
Comments
Maciej Los 5-Sep-16 15:28pm    
I really wonder how you deserialized such of structure of data... There's no identical objects: Item1, Item2, etc.
jinesh sam 5-Sep-16 15:32pm    
using xpath expression "starts-with"

1 solution

Please, read my comment to the question first.

Second guess:

C#
var result = items.SelectMany(i=>
        i.YearlyAmt.YearlyAmts.Select(ya => new
            {
                ContributionId = i.Contribution.ContributionId,
                AccountId = i.Contribution.Account.AccountID,
                Type = i.Contribution.Account.Type,
                Year = ya.year,
                NetAmount = ya.NetAmount
            }))
    .GroupBy(x=>new{Year = x.Year, Type = x.Type})
    .Select(grp=>new
        {
            Type = grp.Key.Type,
            Year = grp.Key.Year,
            NetAmount = grp.Sum(a=>a.NetAmount)
        });


In your case, above code should return:

Type  Year  NetAmount
401K  2016  50000 
401K  2017  51000 
401K  2018  52020 
VA    2016  40000 
VA    2017  40800 
VA    2018  41616
 
Share this answer
 
v3
Comments
jinesh sam 5-Sep-16 16:01pm    
Since YearlyAmts is a list i can't directly use the year and NetAmount property. I updated the question with class structure. please have a look
Maciej Los 5-Sep-16 16:06pm    
Sorry, your class definition is incomplete...
jinesh sam 5-Sep-16 16:19pm    
Updated the class definition. Please have a look
Maciej Los 5-Sep-16 16:55pm    
Done. Check updated answer (second guess section).
jinesh sam 6-Sep-16 5:28am    
Thanks Maciej :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900