LINQ COUNT on multiple columns

Question

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

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

I am looking for the “best” way to do this. In the version, I encountered errors in the table 4 times when you look at basic SQL, so it is too slow.

Answer 1

Here is the solution I came up with. Note that this is close to the solution proposed by @OdeToCode (but in VB syntax), with one significant difference:

 Dim temp = _ (From t In context.MyTable _ Group t.f1, t.f2, t.f3 By t.title Into g = Group _ Select title, g).ToList Dim results = _ From t In temp _ Select t.title, _ f1_count = tgCount(Function(x) If(x.f1, False)), _ f2_count = tgCount(Function(x) If(x.f2, False)), _ f3_count = tgCount(Function(x) If(x.f3, False)) 

The first request does the grouping, but ToList receives the grouped data as it is from the server. Eliminating the count here causes the resulting SQL query to create a sub-SELECT for each count. I am doing the count in the second query locally.

This works since I know that the first query will return a manageable number of rows. If he were to return millions of rows, I probably would have to go in the other direction.

Answer 2

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

  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 displays as a nullable bool), which you can do with the Count operator and the predicate. For more information about the LINQ group statement here: Standard LINQ statements

Answer 3

I think that is where LINQ falls. If you want to use SQL efficiently, if you want to use good code, use LINQ.

You can always execute the query directly, since you already know SQL.

 class TitleCount { public string Title; public int Count1; public int Count2; public int Count3; } DataContext dc = new DataContext("Connection string to db"); IEnumerable<TitleCount> query = dc.ExecuteQuery<TitleCount>( @"SELECT title, COUNT(f1) as Count1, COUNT(f2) as Count2, COUNT(f3) as Count3 FROM myTable GROUP BY title");