1

Background

I have a dataset with lots of repeating values, which have come from a CSV file.

I know how I would group, sort and add a count if the data was in a database using SQL.

SQL

SELECT pass, count(pass)
FROM table
GROUP BY pass
ORDER BY pass, count(pass) ASC

I've attempted to do this using LINQ.

What I've Tried?

//Sort
       dtData.DefaultView.Sort = "Pass";
       dtData = dtData.DefaultView.ToTable();

 //Group
        dtData = dtData.AsEnumerable()
       .GroupBy(r => new { Col1 = r["Pass"] })
       .Select(g => g.OrderBy(r => r["Pass"]).First())
       .CopyToDataTable();

However I need a extra column called count, else the group is pointless. How can i get an extra column with a count?

Note: To the person who flagged this question as a duplicate of the below question, please see remark above. The flagged question is just how to sort dataset.

Sorting rows in a data table

Community
  • 1
  • 1
Dan Cundy
  • 2,649
  • 2
  • 38
  • 65
  • 1
    [Linq](https://msdn.microsoft.com/en-us/library/mt693024.aspx)?.......... – L.B Sep 03 '16 at 19:34
  • 1
    Possible duplicate of [Sorting rows in a data table](http://stackoverflow.com/questions/9107916/sorting-rows-in-a-data-table) – uTeisT Sep 03 '16 at 19:36
  • @uteist, I'm not just sorting, i'm also grouping and adding an extra column... – Dan Cundy Sep 03 '16 at 19:39

2 Answers2

0

This should do:

dtData = dtData
    .AsEnumerable()
    .GroupBy(r => r["Pass"].ToString()) // or whatever data type it is
    .Select(g => new MyTable { Pass = g.Key, Count = g.Count() })
    .OrderBy(x => x.Pass)
    .ThenBy(x => x.Count)
    .CopyToDataTable();

where

class MyTable 
{
    public string Pass { get; set; } // or whatever data type it is
    public int Count { get; set; }
}
t3chb0t
  • 16,340
  • 13
  • 78
  • 118
  • This seems to cause an error. Not entirely sure what it is bitching about. ` The type '' cannot be used as type parameter 'T' in the generic type or method 'DataTableExtensions.CopyToDataTable(IEnumerable)'. There is no implicit reference conversion from '' to 'System.Data.DataRow'.` – Dan Cundy Sep 18 '16 at 13:14
  • 1
    @DanCundy instead of `new { Pass = g.Key, Count = g.Count() }` you need to use a concrete type because `CopyToDataTable` cannot work with an anonymous one. I've updated the answer. – t3chb0t Nov 01 '16 at 10:26
  • I'll test tonight. Thanks for getting back. – Dan Cundy Nov 01 '16 at 13:29
0
DataTable dtTable = new DataTable();
        dtTable.Columns.Add(new DataColumn("pass", typeof(int)));


        for (int i = 0; i < 100; i++)
        {
            DataRow drRow = dtTable.NewRow();
            drRow["pass"] = (i + 1) % 10;
            dtTable.Rows.Add(drRow);
        }

        var query = (from row in dtTable.AsEnumerable()
                     group row by row.Field<int>("pass") into passes
                     orderby passes.Key, passes.Count() ascending
                     select new
                     {
                         pass = passes.Key,
                         passCount = passes.Count()
                     });

        DataTable dtTableSorted = new DataTable();
        dtTableSorted.Columns.Add(new DataColumn("pass", typeof(int)));
        dtTableSorted.Columns.Add(new DataColumn("passCount", typeof(int)));

        query.ToList().ForEach(x =>
        {
            DataRow drRow = dtTableSorted.Rows.Add(x.pass, x.passCount);
        });

I know it's neither pretty nor succinct but it works.

Robert Sandu
  • 673
  • 1
  • 6
  • 15
  • You are right, this seems like a very long winded away about doing something that should be relatively simple. – Dan Cundy Sep 18 '16 at 13:17