Group By Multiple Columns - LINQ

Question

How can I do GroupBy Multiple Columns in LINQ

Something as in SQL :

SELECT * FROM <TableName> GROUP BY <Column1>,<Column2>

How Can i convert below to LINQ

     QuantityBreakdown
(
 MaterialID int,
 ProductID int,
 Quantity float
)

     INSERT INTO @QuantityBreakdown (MaterialID, ProductID, Quantity)
SELECT MaterialID, ProductID, SUM(Quantity)
FROM @Transactions
GROUP BY MaterialID, ProductID

Thanks

Answer 1

Use an anonymous type.

Eg

group x by new { x.Column1, x.Column2 }

Answer 2

Ok got this as :

var query = (from t in Transactions
                         group t by new {t.MaterialID, t.ProductID}
                         into grp
                             select new
                                        {
                                            grp.Key.MaterialID,
                                            grp.Key.ProductID,
                                            Quantity = grp.Sum(t => t.Quantity)
                                        }).ToList();

Answer 3

how could I do it with two tables and with Join implemented on it. and grrouping with 3 columns and also agregating one col sum say totalcost in one of the tables

Answer 4

var result = from outer in
                             (from customerobj in context.customer
                              join regobj in context.register
                              on customerobj.customer_no equals regobj.customer_no
                              join invnobj in context.inventory
                              on regobj.pos_sku equals invnobj.pos_sku
                              join sub_cat in context.sub_category
                              on invnobj.category equals sub_cat.category_id
                              select new
                              {
                                  altSKU = invnobj.alt_sku,
                                  subcatdescription = sub_cat.description,
                                  compute_0003description = invnobj.product_description == null ? " " : invnobj.product_description + " " + (invnobj.release_no.HasValue ? invnobj.release_no.Value.ToString() : "") + " " +
                                                             (invnobj.description == null ? "" : invnobj.description),
                                  regreceitno = regobj.receipt_no,
                                  qtycompute_0005 = regobj.quantity,
                                  tmpproddescription = invnobj.product_description,
                                  tmpinvdescription = invnobj.description,
                                  tmpinvreleaseno = invnobj.release_no,
                                  tmpsubrelaseno = invnobj.sub_release_no,

                              })

                         group outer by new
                         {
                             grpaltsku = outer.altSKU,
                             grpsubcatdesp = outer.subcatdescription,
                             grpprodescition = outer.tmpproddescription,
                             grpinvdescription = outer.tmpinvdescription,
                             grpinvrelno = outer.tmpinvreleaseno,
                             grpsubrelnob = outer.tmpsubrelaseno,
                             grprectno = outer.regreceitno,
                             grp003grpdesc = outer.compute_0003description,

                         } into g

                         select new d_customer_past_sale_items_entity
                         {
                             compute_0005 = g.Sum(p => p.qtycompute_0005),
                             compute_0003 = g.Key.grp003grpdesc,
                             inventory_alt_sku = g.Key.grpaltsku,
                             register_receipt_no = g.Key.grprectno,
                             sub_category_description = g.Key.grpsubcatdesp
                         };