CODEWITHSUNDEEP
c#linq
Inside Man
Inside Man

Reputation: 4372

Get sum of two fields when the Other fields are the Same - Linq

this is my query:

var query = from buy in db.tblCustomerBuys.AsEnumerable()
            select new
            {
                Year = Convert.ToInt32(buy.BuyDate.Substring(0, 4)),
                ProductID = Convert.ToInt32(buy.ProductID),
                Price = Convert.ToDecimal(buy.TotalPrice),
                Amount = Convert.ToDouble(buy.Amount),
                Major = (Convert.ToBoolean(db.tblCustomers.First(x => x.CustomerID == buy.CustomerID).IsMajor) == true ? "Major" : "Normal"),
                Month = ClassDate.MonthName(Convert.ToInt32(buy.BuyDate.Split('/')[1])),
                Season = ClassDate.SeasonName(Convert.ToInt32(buy.BuyDate.Split('/')[1]))
            };

the table is about customers' buys. I need to get sum of price and amount for each product Id where all of the other fields are the same. Currently the output is for each row in the table.

for example for Product 1 we have:

Year: 2012, ID: 1, Price: 12000, Amount: 2k, Major: Normal, Month: 2, Season: 1
Year: 2012, ID: 1, Price: 10000, Amount: 3k, Major: Normal, Month: 2, Season: 1

as you can see all of the other fields than Price and Amount are the same,in this case I need to get sum of price and amount:

Year: 2012, ID: 1, Price: 22000, Amount: 5k, Major: Normal, Month: 2, Season: 1

so the output records will be less. How can I do this in a fast way?

Upvotes: 0

Views: 79

Answers (2)

Alireza
Alireza

Reputation: 5503

1-You do not need to use AsEnumerable(), this will result in fetching all the data to the memory which might not be necessary. If it is possible to fetch only the grouping result, that might be much faster, see the next point.

2-You need Group by

db.tblCustomerBuys.
        .GroupBy(buy => new 
        {
            Year = Convert.ToInt32(buy.BuyDate.Substring(0, 4)),
            ProductID = Convert.ToInt32(buy.ProductID),
            Major = (Convert.ToBoolean(db.tblCustomers.First(x => x.CustomerID == buy.CustomerID).IsMajor) == true ? "Major" : "Normal"),
            Month = ClassDate.MonthName(Convert.ToInt32(buy.BuyDate.Split('/')[1])),
            Season = ClassDate.SeasonName(Convert.ToInt32(buy.BuyDate.Split('/')[1]))
        })
        .Select(x => new
        {
            Year = x.Key.Year,
            ProductID = x.Key.ProductId,
            Price = x.Sum(i => Convert.ToInt32(i.Price)),
            Amount = x.Sum(i => Convert.ToDouble(i.Amount)),
            Major = x.Key.Major,
            Month = x.Key.Month,
            Season = x.Key.Season
        }).ToList();

Please remember that if you run this in EF core (as of the time of writing this answer), all the data will be fetched to the memory and then groupped, which is much slower than if it was done in DBMS. This might not be an issue for you, also no an issue in EF6.x

Upvotes: 3

jdweng
jdweng

Reputation: 34421

Try this. You need to use a GroupBy to get all products with same year and id. Yo may aslo need to group the Major, Month, and Season.

var totals = query.GroupBy(x => new { year = x.Year, id = x.ProductID }).Select(x => new {
                Year = x.Key.year,
                ProductID = x.Key.id,
                Price = x.Sum(y => y.Price),
                Amount = x.Sum(y => y.Amount),
                Major = x.First().Amount,
                Month = x.First().Month,
                Season = x.First().Season
            }).ToList();

Upvotes: 1

Related Questions