CODEWITHSUNDEEP
c#asp.netlinq-to-sql
diamond
diamond

Reputation: 69

How to count the number of trues in a table in LINQ to SQL

I have a table like this

StudID Date I II III IV V VI VII VIII
100 2-10-11 T T F F F F F T
101 2-10-11 T T T F F F F T
100 3-10-11 T F F F F F F T
100 4-10-11 T F F F F F F T

Now i need to get the number of T's i.e, Trues in the table for a particular student in a particular month StudID is a varchar field Date is a datetime field and all the other datatype with bit

any ideas?

Upvotes: 4

Views: 179

Answers (5)

David Ruttka
David Ruttka

Reputation: 14409

var student = context.Students.Where(s => s.StudID == id && s.Date.Month == month).Single();

var trues = from results in student.GetType().GetProperties().Where(p => p.PropertyType == typeof(bool))
    let val = (bool)f.GetValue(student, null)
    where val
    select val;
// You can now check trues.Count()

Upvotes: 1

Jonas Elfström
Jonas Elfström

Reputation: 31468

If the T and F actually are characters and not bit/booleans then you could try

context.Studs.Sum(s => (s.I+s.II+s.III+s.IV).Count(c => c=='T') );

but I wonder what the generated SQL will look like. Problably not something like

SELECT SUM(LEN(REPLACE(I+II+III+IV, 'F', '')))

Upvotes: 1

Vlad Bezden
Vlad Bezden

Reputation: 89775

var numberOfTrues = context.Students.
Where(x => x.StudID == 123 && x.Date.Month == studentMonth).
Sum(x => x.I.ToInt() + x.II.ToInt() + ... + x.VIII.ToInt());


// Add extension method    
public static int ToInt(this bool value)
{
   return value ? 1 : 0;
}

Upvotes: 2

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28738

As the comments suggest, you should normalize your data.

However if you are unable to do that, you simply need to count the number of trues in each row.

context.Studs.Sum(s => (s.I ? 1 : 0) + (s.II ? 1 : 0) + ... + (s.VIII ? 1 : 0));

edit: To restrict the sum based on StudID and the month, you would use a Where operator

var id = "100";
var month = 10;

var set = context.Studs.Where(s => s.StudID == id;
set = set.Where(s => s.Date.Month == month);

return set.Sum(s => (s.I ? 1 : 0) + (s.II ? 1 : 0) + ... + (s.VIII ? 1 : 0));

Upvotes: 7

esastincy
esastincy

Reputation: 1627

Build a function called something like bool CheckBit(StudID, Date, num) where you pass in the StudID, Date, and a number from 1 to 8. Put the function call in a loop and pass in 1 - 8 and keep a count of how many times you return true

Upvotes: 0

Related Questions