locked
How do I write this query/code in MVC RRS feed

  • Question

  • User2041008840 posted

    I want to run this query and put it into ViewBag 
    here is my code

    (from i in Items 
    join it in ItemsLists
    on i.ItemID equals it.Id
    
    where i.StockSellID == 249
    
    select new
    {
    it.HSNCode,
    i.StockSellID,
    i.Gst,
    i.Rate, 
    i.Quantity,
    TOTal = i.Rate * i.Quantity,
    TOtalCGST =   (i.Rate * i.Quantity * i.Gst/100)/2,
    TOtalSGST =   (i.Rate * i.Quantity * i.Gst/100)/2,
    TOtalIGST =    i.Rate * i.Quantity * i.IGST/100
    })
    

    the problem is I want to group the query by ItemID 
    so all the rate, quantity, sum each other 

    this is the output of current query

    5IOrderedQueryable<> (10 items)44
    HSNCode StockSellID GstΞΞ RateΞΞ QuantityΞΞ TOTalΞΞ TOtalCGSTΞΞ TOtalSGSTΞΞ TOtalIGSTΞΞ
    0001 249 0 700 1 700 0 0 0
    0001 249 0 100 1 100 0 0 0
    0004 249 5 50 1 50 1.25 1.25 0
    0005 249 5 20 1 20 0.5 0.5 0
    0002 249 5 90 1 90 2.25 2.25 0
    0007 249 18 258 1 258 23.22 23.22 0
    0007 249 18 258 1 258 23.22 23.22 0
    0005 249 5 20 1 20 0.5 0.5 0
    0002 249 5 9999 1 9999 249.975 249.975 0
    0004 249 5 50 1 50 1.25 1.25 0
    66 11545 10 11545 302.165 302.165 0
    Saturday, November 10, 2018 10:29 AM

Answers

  • User-271186128 posted

    Hi Prathamesh Shende,

    I want to Group by ItemID so all the column sum itself 
    How do I do this ?

    Please refer to the following code: after filter data, you could use the group by clause to group the result.

                (from i in Items
                 join it in ItemsLists
                 on i.ItemID equals it.Id
                 where i.StockSellID == 249
                 select new
                 {
                     ItemID = i.ItemID,
                     HSNCode = it.HSNCode,
                     StockSellID = i.StockSellID,
                     Gst = i.Gst,
                     Rate = i.Rate,
                     Quantity = i.Quantity,
                     TOTal = i.Rate * i.Quantity,
                     TOtalCGST = (i.Rate * i.Quantity * i.Gst / 100) / 2,
                     TOtalSGST = (i.Rate * i.Quantity * i.Gst / 100) / 2,
                     TOtalIGST = i.Rate * i.Quantity * i.IGST / 100
                 }).ToList().GroupBy(c => c.ItemID).Select(c => new
                 {
                     HSNCode = c.FirstOrDefault().HSNCode,
                     StockSellID = c.FirstOrDefault().StockSellID,
                     Gst = c.Sum(d=>d.Gst),
                     Rate = c.Sum(d => d.Rate),
                     Quantity = c.Sum(d => d.Quantity),
                 }).ToList();

    More details about using group by clause, please refer to to the following articles:

    Group query results

    Writing Complex Queries Using LINQ And Lambda 

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 12, 2018 5:48 AM

All replies

  • User2041008840 posted

    I want to Group by ItemID so all the column sum itself 
    How do I do this ?

    Saturday, November 10, 2018 10:44 AM
  • User-271186128 posted

    Hi Prathamesh Shende,

    I want to Group by ItemID so all the column sum itself 
    How do I do this ?

    Please refer to the following code: after filter data, you could use the group by clause to group the result.

                (from i in Items
                 join it in ItemsLists
                 on i.ItemID equals it.Id
                 where i.StockSellID == 249
                 select new
                 {
                     ItemID = i.ItemID,
                     HSNCode = it.HSNCode,
                     StockSellID = i.StockSellID,
                     Gst = i.Gst,
                     Rate = i.Rate,
                     Quantity = i.Quantity,
                     TOTal = i.Rate * i.Quantity,
                     TOtalCGST = (i.Rate * i.Quantity * i.Gst / 100) / 2,
                     TOtalSGST = (i.Rate * i.Quantity * i.Gst / 100) / 2,
                     TOtalIGST = i.Rate * i.Quantity * i.IGST / 100
                 }).ToList().GroupBy(c => c.ItemID).Select(c => new
                 {
                     HSNCode = c.FirstOrDefault().HSNCode,
                     StockSellID = c.FirstOrDefault().StockSellID,
                     Gst = c.Sum(d=>d.Gst),
                     Rate = c.Sum(d => d.Rate),
                     Quantity = c.Sum(d => d.Quantity),
                 }).ToList();

    More details about using group by clause, please refer to to the following articles:

    Group query results

    Writing Complex Queries Using LINQ And Lambda 

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 12, 2018 5:48 AM