LINQ DataTable Select Distinct Rows

Question

I am trying to get the distinct row with sum of each duplicate rows

DataTable newItems = new DataTable();

newItems.Columns.Add("Qty");
newItems.Columns.Add("BarcodeNumber");
newItems.Columns.Add("DisplayName");
newItems.Columns.Add("UnitPrice");
newItems.Columns.Add("TotalPrice");

newItems.Rows.Add(1, "URT29384", "Wallet", 88.8, 88.8);
newItems.Rows.Add(1, "URT29233", "Mouse", 4.44, 4.44);
newItems.Rows.Add(1, "URT29756", "Flash Drive", 1.47, 1.47);
newItems.Rows.Add(1, "URT29323", "Spoon", 99.95, 99.95);
newItems.Rows.Add(1, "URT29384", "Wallet", 88.8, 88.8);
newItems.Rows.Add(1, "URT29384", "Wallet", 88.8, 88.8);

i want the result to be

 3 URT29384  Wallet       266.4     266.4
 1 URT29233  Mouse          4.44      4.44
 1 URT29756  Flash Drive    1.47      1.47
 1 URT29323  Spoon         99.95     99.95

is this possible using LINQ? a little help here.. TYIA

just an offtopic advice: Prices should be stored as `System.Decimal`. See http://stackoverflow.com/a/1165788/390819 — Cristian Lupascu, Oct 04 '12 at 08:47

Tim Schmelter · Accepted Answer · 2012-10-04T08:42:30.533

6

Yes, you can use Enumerable.GroupBy:

var result = newItems.AsEnumerable()
            .GroupBy(r => r.Field<String>("BarcodeNumber"))
            .Select(g => new { 
                Qty = g.Count(),
                BarcodeNumber = g.Key,
                DisplayName = g.First().Field<String>("DisplayName"),
                UnitPrice = g.Sum(r => r.Field<double>("UnitPrice")),
                TotalPrice = g.Sum(r => r.Field<double>("TotalPrice")),
            })
            .OrderByDescending(x => x.Qty);

Note that this is not a DataTable or IEnumerable<DataRow> but an anonymous type with your columns. You would need to use a foreach loop to add real DataRows to another DataTable if it's required.

foreach (var grp in result)
    Console.WriteLine("Qty:{0} BarcodeNumber:{1} DisplayName:{2} UnitPrice:{3} TotalPrice:{4}"
        , grp.Qty, grp.BarcodeNumber, grp.DisplayName, grp.UnitPrice, grp.TotalPrice);

You need to add using System.Linq;.

edited Oct 04 '12 at 08:42

answered Oct 04 '12 at 08:36

Tim Schmelter

450,073
74
686
939

Is it possible to cast the result into datatable? – Vincent Dagpin Oct 04 '12 at 08:42
ah there.. it's not necessary to convert to datatable – Vincent Dagpin Oct 04 '12 at 08:43
Posted an awnser that puts it into a datatable. – Jan P. Oct 04 '12 at 08:46
If you have database layer it would be more eficient and easy for you to deal with this problem there, especialy if you are expecting large amount or records. – Gregor Primar Oct 04 '12 at 08:49
@VincentDagpin, Yes, it's possible to 'convert' it to DataTable: DataTable dt = new DataTable(); dt = linqquery.CopyToDataTable(); – Maciej Los Dec 22 '14 at 17:25

score 1 · Answer 2 · answered Oct 04 '12 at 08:44

Yes it is possible

var foo = newItems.AsEnumerable()
    .GroupBy(item => item.Field<string>(newItems.Columns["BarcodeNumber"]));

DataTable newTable = new DataTable();

newTable.Columns.Add("Qty");
newTable.Columns.Add("BarcodeNumber");
newTable.Columns.Add("DisplayName");
newTable.Columns.Add("UnitPrice");
newTable.Columns.Add("TotalPrice");

foreach (var item in foo)
{
    newTable.Rows.Add(
    item.Count(),
    item.Key,
    item.First().Field<string>(newItems.Columns["DisplayName"]),
    item.Sum(row => decimal.Parse(row.Field<string>(newItems.Columns["UnitPrice"]))),
    item.Sum(row => decimal.Parse(row.Field<string>(newItems.Columns["TotalPrice"]))));
}

score 1 · Answer 3 · answered Jan 22 '13 at 09:26

private static DataTable GetUniqueEntries(DataTable dt)  
    {  
        var query = (  
        from row in dt.AsEnumerable()  
        select row.Field<string>("UserName")).Distinct();  //any column <UserName>

        DataTable dtDistinctNames = new DataTable();  
        dtDistinctNames.Columns.Add("UserName", typeof(string));  

        //have to return a datatable, thus loop through entries  
        foreach (string item in query)  
        {  
            DataRow newRow = dtDistinctNames.NewRow();  
            newRow["UserName"] = item;  
            dtDistinctNames.Rows.Add(newRow);  
        }  

        return dtDistinctNames;  
    }

LINQ DataTable Select Distinct Rows

3 Answers3