CODEWITHSUNDEEP
c#entity-frameworklinqlinq-to-sqllinq-to-entities
dee pan
dee pan

Reputation: 161

How to Merge the List in Linq Query

I have this Linq queries,when i run those queries i got the result,which i shown in img below

var SalesinvDets = (from si in CustomerTransactions
                            where si.TransTypeID == 10
                            group new {si} by new{
         month=si.Period==1?"Jan-2015":si.Period==2?"Feb-2015":si.Period==3?"Mar-2015":si.Period==4?"Apr-2015":si.Period==5?"May-2015":
                                si.Period==6?"Jun-2015":si.Period==7?"Jul-2015":si.Period==8?"Aug-2015":si.Period==9?"Sep-2015":si.Period==10?"Oct-2015":
                                si.Period==11?"Nov-2015":si.Period==12?"Dec-2015":"",   
        month2=si.Period==13?"Jan-2016":si.Period==14?"Feb-2016":si.Period==15?"Mar-2016":si.Period==16?"Apr-2016":si.Period==17?"May-2016":
                                si.Period==18?"Jun-2016":si.Period==19?"Jul-2016":si.Period==20?"Aug-2016":si.Period==21?"Sep-2016":
                                si.Period==22?"Oct-2016":si.Period==23?"Nov-2016":si.Period==24?"Dec-2016":"",
                            period=si.Period
                            } into g    
                            select new
                            {    
                                month=g.Key.month,
                                month2=g.Key.month2,
                                Amount = g.Sum(x=>x.si.OVAmount)//.Sum(x=>x.si.OVAmount-(x.si.OVDiscount+x.si.OVFreight))//(si.OVAmount - (si.OVDiscount + si.OVFreight))
                            }).Dump();

I got this result during run the query,but i dont need like that

Linq Error

I need the result like that,pls help to achieve my task

result img

Upvotes: 0

Views: 90

Answers (1)

Mrinal Kamboj
Mrinal Kamboj

Reputation: 11478

Issue exist due to faulty GroupBy statement, it is based on the value of si.Period, which will be any one of the values specified in month and month2, therefore in each grouping either month or month2 will be empty string "", both will never be filled, thus the result that you have shown, solution for the same would be separate grouping based on month and month2, as follows:

var SalesinvDetsMonthList = (from si in CustomerTransactions
                            where si.TransTypeID == 10
                            group new {si} by new{
         month=si.Period==1?"Jan-2015":si.Period==2?"Feb-2015":si.Period==3?"Mar-2015":si.Period==4?"Apr-2015":si.Period==5?"May-2015":
                                si.Period==6?"Jun-2015":si.Period==7?"Jul-2015":si.Period==8?"Aug-2015":si.Period==9?"Sep-2015":si.Period==10?"Oct-2015":
                                si.Period==11?"Nov-2015":si.Period==12?"Dec-2015":"",   

                            period=si.Period
                            } into g    
                            select new
                            {    
                                Month=g.Key.month
                                Amount = g.Sum(x=>x.si.OVAmount)//.Sum(x=>x.si.OVAmount-(x.si.OVDiscount+x.si.OVFreight))
                            }).Where( x => x.month != "").ToList(); // Removing empty month values / default values


var SalesinvDetsMonth2List = (from si in CustomerTransactions
                            where si.TransTypeID == 10
                            group new {si} by new{
         month2=si.Period==13?"Jan-2016":si.Period==14?"Feb-2016":si.Period==15?"Mar-2016":si.Period==16?"Apr-2016":si.Period==17?"May-2016":
                                si.Period==18?"Jun-2016":si.Period==19?"Jul-2016":si.Period==20?"Aug-2016":si.Period==21?"Sep-2016":
                                si.Period==22?"Oct-2016":si.Period==23?"Nov-2016":si.Period==24?"Dec-2016":"",
                            period=si.Period
                            } into g    
                            select new
                            {    
                                month=g.Key.month,
                                Month2=g.Key.month2,
                                Amount2 = g.Sum(x=>x.si.OVAmount)//.Sum(x=>x.si.OVAmount-(x.si.OVDiscount+x.si.OVFreight))//(si.OVAmount - (si.OVDiscount + si.OVFreight))
                            }).Where( x => x.month != "").ToList(); // Removing empty month values / default values

Now we need to merge the two lists created above:

SalesinvDetsMonthList, SalesinvDetsMonth2List

For which I assume, they have same number of records,, now you need a combined entity like:

public class SalesinvDetsMonthCombined
{
  public string Month {get; set;}
  public string Month2 {get; set;}
  public int Amount {get; set;}
  public int Amount2 {get; set;}
}

int totalCount = SalesinvDetsMonthList.Count;

List<SalesinvDetsMonthCombined> result = new List<SalesinvDetsMonthCombined>();

for (int index=0, index < totalCount; index ++)
{
  result.Add(new SalesinvDetsMonthCombined
                ( Month = SalesinvDetsMonthList[index].Month,
                  Month2 = SalesinvDetsMonth2List[index].Month2,
                  Amount = SalesinvDetsMonthList[index].Amount,
                  Amount2 = SalesinvDetsMonth2List[index].Amount2
                ));
}

However please note, above code is based on assumption that you have provided, that there are matching records, in case there's discrepancy in the number of records, then you need to add a default, else it will fail

Upvotes: 2

Related Questions