LINQ to DataSet case insensitive group by

case-insensitive, dataset, datatable, group-by, linq

Solution

You can't do this from a query expression, but you can do it with dot notation:

var query = dataTable.AsEnumerable()
                     .GroupBy(x => table.Field<string>(Column1),
                              StringComparer.InvariantCultureIgnoreCase)
                     .Select(groupedTable => new
                             {
                                 value = groupedTable.Key,
                                 count = groupedTable.Count()
                             });

You can even use a more complicated overload of `GroupBy` to do it in one call:

var query = dataTable.AsEnumerable()
                     .GroupBy(x => table.Field<string>(Column1),
                              (key, group) => { value = key, 
                                                count = group.Count() },
                              StringComparer.InvariantCultureIgnoreCase));

Obviously that's using the invariant culture - you could also use the current culture or ordinal rules.

Problem

I have a data table and I want to perform a case insensitive group by over a column of data table (say `Column1` of type string). I observed that normally LINQ to DataSet performs a case sensitive comparison. For example, if `Column1` has two string values "Test" and "test", after applying `group by` it returns two separate rows with the values "Test" and "test", instead of one. The query is: ``` var countGroupQuery = from table in dataTable.AsEnumerable() group table by table.Field<string>(Column1) into groupedTable select new { value = groupedTable.Key, count = groupedTable.Count() }; ``` Is there any method to perform a case-insensitive `group by` so that in the above example I get only one row with one value (either "Test" or "test")? `ToUpper` or `ToLower` would actually change the values to either upper case or lower case instead of using at least one of the input values, so I don't want to use this: ``` group table by table.Field<string>(Column1).ToUpper() into groupedTable ```

Original source