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

Question

I have a table like this

StudID Date I II III IV V VI VII VIII -------------------------------------------------------------- 100 2-10-11 TTFFFFFT 101 2-10-11 TTTFFFFT 100 3-10-11 TFFFFFFT 100 4-10-11 TFFFFFFT 

Now I need to get the number of T ie, Trues in the table for a particular student in a particular month StudID - this is a varchar field Date - this is a date field and all other data type with bit

any ideas?

Answer 1

As the comments suggest, you should normalize your data.

However, if you cannot do this, you just need to count the number of truths in each row.

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

edit: To limit the amount based on StudID and month, you should use the Where statement

 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 => (sI ? 1 : 0) + (s.II ? 1 : 0) + ... + (s.VIII ? 1 : 0)); 

Answer 2

 var numberOfTrues = context.Students. Where(x => x.StudID == 123 && x.Date.Month == studentMonth). Sum(x => xIToInt() + x.II.ToInt() + ... + x.VIII.ToInt()); // Add extension method public static int ToInt(this bool value) { return value ? 1 : 0; } 

Answer 3

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

Answer 4

If T and F are actually characters, not bit / booleans, you can try

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

but I'm wondering what the generated SQL looks like. The easiest thing is not something like

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

Answer 5

Create a function called bool CheckBit (StudID, Date, num), where you pass StudID, Date and a number from 1 to 8. Put the function call in a loop and go to 1 - 8 and save how many times you return true