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

I have a table like this

开发者_JS百科

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?

---

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));

---

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;
}

---

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', '')))

---

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()

---

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