LINQ to DataSet case insensitive group by

Question

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

Your recent edits are [inappropriate](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts) and, given the age of the posts you're applying them to, likely spam. Please desist. — Michael Petrotta, Apr 10 '12 at 05:50

Jon Skeet · Accepted Answer · 2009-09-29T07:26:09.060

23

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.

edited Sep 29 '09 at 07:26

answered Sep 29 '09 at 07:19

Jon Skeet

1,421,763
867
9,128
9,194

1

Thanx for the answer. this is what I am looking for!!! By the way if i want to apply group by on more than one column(Column1 and Column2) then how should this query look like?? – Anoop Sep 29 '09 at 08:47
@Anoop: Grouping by more than one column *and* doing so in a case-insensitive manner will be tricky. You'd probably be best off building a custom type containing the columns you're interested in, and overriding Equals. – Jon Skeet Sep 29 '09 at 09:06
Jon! Can you please explain it in a little more detailed manner?? – Anoop Sep 29 '09 at 09:33
I'm afraid I don't have time at the moment. I'll try to come back to this later on. – Jon Skeet Sep 29 '09 at 09:38

score 3 · Answer 2 · answered Sep 29 '09 at 07:02

3

This MSDN article has some information on datasets and case sensitivity..

You can control the case sensitivity of filtering, searching, and sorting by setting the dataset's CaseSensitive property.

answered Sep 29 '09 at 07:02

Quintin Robinson

81,193
14
123
132

In my case DataTable is independent of Dataset so case sensitive property is set to false. I think Linq doesnt refer to this property.It is for the operations exposed by Dataset/Datatable class. – Anoop Sep 29 '09 at 07:15
DataTable also has the property. But honestly I haven't used LINQ to interact with datasets/tables so I can't say whether it would actually have an effect. – Quintin Robinson Sep 29 '09 at 07:22

score 0 · Answer 3 · edited Mar 01 '12 at 08:52

0

var query = dt.AsEnumerable()
              .GroupBy(r => r.Field<string>("Mes"))
              .Select(g => new { Mes = g.Key, 
                                 Tns = g.Sum(s => Convert.ToDecimal(s.Field<string>("Tns"))) })
              .OrderBy(g => g.Mes.First())
              .Where(g => g.Mes == ddlMes.SelectedValue.ToString());

edited Mar 01 '12 at 08:52

alony

10,725
3
39
46

answered Mar 01 '12 at 07:00

Yuri Cano

31
2

LINQ to DataSet case insensitive group by

3 Answers3