Reputation: 69
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
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
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
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
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
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