CODEWITHSUNDEEP
c#linq
João Gomes
João Gomes

Reputation: 340

Count across multiple columns in LINQ

I know the best answer to this question is a better and optimized DB, but is it possible to optimize this sum on the current structure?

The table is something like this:

rowid a1 b1 b2 b3
123   s  s  n  s
124   n  n  n  n
125   s  n  s  s

I need to count the number of all "s".

var items = AsDynamic(App.Data["items"]).Where(p => Convert.ToDateTime(p.data).Year == ano);

var items_1a = items.Where(u => u.udd == "_1a");

var items_1a_jan = items_1a.Where(m => Convert.ToDateTime(m.data).Month == 1);

int items_1a_jan_s = items_1a_jan.Count(q => q.a1 == "s") + items_1a_jan.Count(q => q.b1 == "s") + items_1a_jan.Count(q => q.b2 == "s") + items_1a_jan.Count(q => q.b3 == "s") + items_1a_jan.Count(q => q.b4 == "s") + items_1a_jan.Count(q => q.b5 == "s") + items_1a_jan.Count(q => q.b6 == "s") + items_1a_jan.Count(q => q.b7 == "s") + items_1a_jan.Count(q => q.b8 == "s") + items_1a_jan.Count(q => q.b9 == "s") + items_1a_jan.Count(q => q.b10 == "s") + items_1a_jan.Count(q => q.b11 == "s") + items_1a_jan.Count(q => q.b12 == "s") + items_1a_jan.Count(q => q.b13 == "s") + items_1a_jan.Count(q => q.c1 == "s") + items_1a_jan.Count(q => q.c2 == "s") + items_1a_jan.Count(q => q.c3 == "s") + items_1a_jan.Count(q => q.c4 == "s") + items_1a_jan.Count(q => q.c5 == "s") + items_1a_jan.Count(q => q.c6 == "s") + items_1a_jan.Count(q => q.c7 == "s") + items_1a_jan.Count(q => q.c8 == "s") + items_1a_jan.Count(q => q.c9 == "s") + items_1a_jan.Count(q => q.c10 == "s") + items_1a_jan.Count(q => q.c11 == "s") + items_1a_jan.Count(q => q.c12 == "s") + items_1a_jan.Count(q => q.c13 == "s") + items_1a_jan.Count(q => q.c14 == "s") + items_1a_jan.Count(q => q.c15 == "s") + items_1a_jan.Count(q => q.c16 == "s") + items_1a_jan.Count(q => q.c17 == "s") + items_1a_jan.Count(q => q.d1 == "s") + items_1a_jan.Count(q => q.d2 == "s") + items_1a_jan.Count(q => q.d3 == "s") + items_1a_jan.Count(q => q.d4 == "s") + items_1a_jan.Count(q => q.d5 == "s") + items_1a_jan.Count(q => q.d6 == "s") + items_1a_jan.Count(q => q.d7 == "s") + items_1a_jan.Count(q => q.d8 == "s") + items_1a_jan.Count(q => q.d9 == "s") + items_1a_jan.Count(q => q.e1 == "s") + items_1a_jan.Count(q => q.e2 == "s") + items_1a_jan.Count(q => q.e3 == "s") + items_1a_jan.Count(q => q.e4 == "s") + items_1a_jan.Count(q => q.e5 == "s") + items_1a_jan.Count(q => q.e6 == "s") + items_1a_jan.Count(q => q.e7 == "s") + items_1a_jan.Count(q => q.e8 == "s") + items_1a_jan.Count(q => q.e9 == "s") + items_1a_jan.Count(q => q.e10 == "s") + items_1a_jan.Count(q => q.e11 == "s");

Upvotes: 0

Views: 174

Answers (3)

amarsha4
amarsha4

Reputation: 483

I'm not sure exactly how you store your data, but you could do something like this:

var rows =
        new []
        {
            new { RowNumber = 123, Chars = new Char[] { 's', 's', 'n', 's' } },
            new { RowNumber = 123, Chars = new Char[] { 'n', 'n', 'n', 'n' } },
            new { RowNumber = 123, Chars = new Char[] { 's', 'n', 's', 's' } }
        };

    var count =
        rows
            .SelectMany(row => row.Chars)
            .Count(chr => chr == 's');

Upvotes: 0

rattrapper
rattrapper

Reputation: 431

If you really have so much field and need to count among all of them then you can use reflection:

var sCount = data
    .Sum(x => x.GetType().GetProperties().Count(prop => prop.GetValue(x) as string == "s"));

Upvotes: 0

Gilad Green
Gilad Green

Reputation: 37299

Use SelectMany to flatten the columns of each row to one long list:

var result = items.SelectMany(i=> new string[] {i.a1, i.b1, i.b2, i.b3 })
                  .Count(i => i == "s");

Upvotes: 4

Related Questions