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

Question

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?

Chaos Legion · Accepted Answer · 2016-05-12 10:41:45Z

2

Try something like this:

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

edited May 12, 2016 at 10:41

answered May 12, 2016 at 8:57

Chaos Legion

3,0101 gold badge17 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mortysporty Over a year ago

Thank you! That worked perfectly! I had to modifiy your solution slightly as I am not working with a strongly typed datatable, and IsInError is a bit field. Apart from that it was perfect!

Chaos Legion Over a year ago

Edited it to suit your answer better.

mortysporty Over a year ago

Thumbs up! :) Thanks

Valeklosse · Accepted Answer · 2016-05-12 07:59:08Z

0

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.

answered May 12, 2016 at 7:59

Valeklosse

1,0173 gold badges20 silver badges37 bronze badges

1 Comment

mortysporty Over a year ago

Thank you! I will give it a try!

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related