How do I get a distinct, ordered list of names from a DataTable using LINQ?
Asked Answered
E

7

117

I have a DataTable with a Name column. I want to generate a collection of the unique names ordered alphabetically. The following query ignores the order by clause.

var names =
    (from DataRow dr in dataTable.Rows
    orderby (string)dr["Name"]
    select (string)dr["Name"]).Distinct();

Why does the orderby not get enforced?

Entrechat answered 1/8, 2008 at 13:14 Comment(0)
F
40

To make it more readable and maintainable, you can also split it up into multiple LINQ statements.

  1. First, select your data into a new list, let's call it x1, do a projection if desired
  2. Next, create a distinct list, from x1 into x2, using whatever distinction you require
  3. Finally, create an ordered list, from x2 into x3, sorting by whatever you desire
Frown answered 4/9, 2008 at 2:57 Comment(0)
E
58

The problem is that the Distinct operator does not grant that it will maintain the original order of values.

So your query will need to work like this

var names = (from DataRow dr in dataTable.Rows
             select (string)dr["Name"]).Distinct().OrderBy( name => name );
Entrechat answered 1/8, 2008 at 13:18 Comment(0)
F
40

To make it more readable and maintainable, you can also split it up into multiple LINQ statements.

  1. First, select your data into a new list, let's call it x1, do a projection if desired
  2. Next, create a distinct list, from x1 into x2, using whatever distinction you require
  3. Finally, create an ordered list, from x2 into x3, sorting by whatever you desire
Frown answered 4/9, 2008 at 2:57 Comment(0)
S
12
var sortedTable = (from results in resultTable.AsEnumerable()
select (string)results[attributeList]).Distinct().OrderBy(name => name);
Symbiosis answered 5/12, 2008 at 1:8 Comment(0)
O
9

Try out the following:

dataTable.Rows.Cast<DataRow>().select(dr => dr["Name"].ToString()).Distinct().OrderBy(name => name);
Octavie answered 28/4, 2013 at 9:27 Comment(0)
C
4

Try the following

var names = (from dr in dataTable.Rows
             select (string)dr["Name"]).Distinct().OrderBy(name => name);

this should work for what you need.

Climactic answered 7/8, 2008 at 2:35 Comment(0)
H
3

To abstract: all of the answers have something in common.

OrderBy needs to be the final operation.

Hatpin answered 30/1, 2018 at 16:19 Comment(0)
G
3

You can use something like that:

dataTable.Rows.Cast<DataRow>().GroupBy(g => g["Name"]).Select(s => s.First()).OrderBy(o => o["Name"]);
Glassy answered 25/6, 2018 at 10:56 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.