CODEWITHSUNDEEP
c#linqdistinct
mortysporty
mortysporty

Reputation: 2889

C# Linq How to select the distinct row count of multiple columns in a datatable

I have a datatable with multiple key columns (key1, key2, ..., keyN). I want to count the distinct key combinations in the table, that match a certain criteria (IsInError = false).

Basically i want to perform the query

SELECT COUNT(*) FROM (SELECT DISTINCT key1, key2, ..., keyN FROM TABLE WHERE IsInError = 'false') A

on the datatable.

I have researched a little and found that for a table containing only one key (and with no 'where') I can do this

DataTable table = new DataTable();

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

But how would I go about doing it with multiple keys and a condition?

Upvotes: 2

Views: 2789

Answers (2)

Chaos Legion
Chaos Legion

Reputation: 2970

Try something like this:

table.AsEnumerable().Where(v => v.Field<bool>("IsInError") == false).Select(_ => new { key1 = _.key1, key2 = _.key2, ...., keyn=_.keyn }).Distinct().Count();

Upvotes: 2

Valeklosse
Valeklosse

Reputation: 1017

A group by might be more suited to this scenario.

It will group the columns into distinct rows and return a count if desired.

i.e. 

var groups = table.GroupBy(_ => new {_.Key1, _.Key2, _.KeyN})
                  .Select(_ => new { key = _.Key, count = _.Count());

This is untested code, but should help point you in the correct direction.

Upvotes: 0

Related Questions