LINQ COUNT on multiple columns

Question

If I have a table with a title column and 3 bit columns (f1, f2, f3) that contain either 1 or NULL, how would I write the LINQ to return the title with the count of each bit column that contains 1? I'm looking for the equivalent of this SQL query:

SELECT title, COUNT(f1), COUNT(f2), COUNT(f3) FROM myTable GROUP BY title

I'm looking for the "best" way to do it. The version I came up with dips into the table 4 times when you look at the underlying SQL, so it's too slow.

Answer 1

If you want to stick to a LINQ query and use an anonymous type, the query could look like:

 var query = 
      from r in ctx.myTable
      group r by r.title into rgroup
      select new
      {
          Title = rgroup.Key,
          F1Count = rgroup.Count(rg => rg.f1 == true),
          F2Count = rgroup.Count(rg => rg.f2 == true),
          F3Count = rgroup.Count(rg => rg.f3 == true)
      };

The trick is to recognize that you want to count the number of true fields (it gets mapped as a nullable bool), which you can do with the Count operator and a predicate. More info on the LINQ group operator here: The Standard LINQ Operators