Removing Primary Key and Joining Rows in Datatable-CodePudding

I'm trying to sort a DataTable to be able to get the best selling items by order in C#. The DataTable's fields are orderId, productId, size, and amount (amount = how much of it was bought). The current primary key in the table is comprised of orderId, productId, and size. I would like to get the best selling items by sorting the table in a way that would remove orderId, and have productId and size as the new primary key, and amount as the sum of all amount's for orders with this productId and size. What would be the best way to do this? I'm attaching my current table data for reference. My table data for reference. If anything is unclear please let me know, this is my first time using Stack Overflow.

CodePudding user response：

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication20
{
    class Program
    {
        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("orderid", typeof(int));
            dt.Columns.Add("productid", typeof(int));
            dt.Columns.Add("size", typeof(int));
            dt.Columns.Add("amount", typeof(int));

            dt.Rows.Add(new object[] { 1, 1, 38, 12 });
            dt.Rows.Add(new object[] { 1, 1, 41, 6 });
            dt.Rows.Add(new object[] { 1, 2, 36, 8 });
            dt.Rows.Add(new object[] { 1, 2, 38, 5 });
            dt.Rows.Add(new object[] { 1, 3, 46, 2 });
            dt.Rows.Add(new object[] { 4, 3, 40, 7 });
            dt.Rows.Add(new object[] { 8, 3, 40, 7 });
            dt.Rows.Add(new object[] { 9, 2, 40, 9 });
            dt.Rows.Add(new object[] { 12, 2, 37, 5 });
            dt.Rows.Add(new object[] { 13, 2, 37, 4 });
            dt.Rows.Add(new object[] { 14, 2, 38, 3 });
            dt.Rows.Add(new object[] { 15, 3, 41, 4 });
            dt.Rows.Add(new object[] { 16, 2, 36, 7 });
            dt.Rows.Add(new object[] { 16, 3, 41, 5 });
            dt.Rows.Add(new object[] { 17, 2, 38, 4 });
            dt.Rows.Add(new object[] { 18, 3, 40, 3 });
            dt.Rows.Add(new object[] { 19, 5, 38, 9 });
            dt.Rows.Add(new object[] { 20, 2, 36, 2 });
            dt.Rows.Add(new object[] { 21, 1, 40, 3 });
            dt.Rows.Add(new object[] { 22, 1, 38, 8 });
            dt.Rows.Add(new object[] { 23, 1, 40, 9 });
            dt.Rows.Add(new object[] { 24, 2, 37, 1 });
            dt.Rows.Add(new object[] { 25, 5, 39, 4 });
            dt.Rows.Add(new object[] { 2034, 3, 40, 3 });
            dt.Rows.Add(new object[] { 2035, 2, 37, 6 });
            dt.Rows.Add(new object[] { 2035, 3, 40, 5 });
            dt.Rows.Add(new object[] { 2036, 2, 36, 2 });
            dt.Rows.Add(new object[] { 2037, 2, 37, 3 });
            dt.Rows.Add(new object[] { 2037, 3, 41, 7 });
            dt.Rows.Add(new object[] { 2038, 1, 39, 3 });
            dt.Rows.Add(new object[] { 2038, 5, 37, 4 });

            var results = dt.AsEnumerable()
                .GroupBy(x => new { productid = x.Field<int>("productid"), size = x.Field<int>("size") })
                .Select(x => new { productid = x.Key.productid, size = x.Key.size, count = x.Count(), total = x.Sum(y => y.Field<int>("amount")) })
                .OrderByDescending(x => x.count)
                .ToList();
 
        }
    }
 
    
}