How do I select the distinct row count of a column in a data table?

Question

I have a data table:

DataTable table = new DataTable();

DataColumn column;

column = new DataColumn();
column.DataType = Type.GetType("System.String");
column.ColumnName = "RelationshipTypeDescription";
table.Columns.Add(column);

column = new DataColumn();
column.DataType = Type.GetType("System.String");
column.ColumnName = "RelatedContactName";
table.Columns.Add(column);

I want to know the DISTINCT COUNT OF COLUMN "RelationshipTypeDescription".

I'm not sure how to refer to the column name in this:

int relationshipCount = table.AsEnumerable().Distinct().Count();

Can someone give me a hand?

Solution 1

You can do this:

int relationshipCount = table
    .AsEnumerable()
    .Select(r => r.Field<string>("RelationshipTypeDescription"))
    .Distinct()
    .Count();

But you probably don't need to call AsEnumerable:

int relationshipCount = table
    .Select(r => r.Field<string>("RelationshipTypeDescription"))  // Compiler error: "Cannot convert lambda expression to type 'string' because it is not a delegate type"
    .Distinct()
    .Count();

Solution 2

You can also create a new datatable containing only the distinct values of your table:

DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "RelationshipTypeDescription");

More info: https://stackoverflow.com/a/1199956/1822214

http://msdn.microsoft.com/en-us/library/wec2b2e6.aspx