多列上的 LINQ COUNT [英] LINQ COUNT on multiple columns

问题描述

如果我有一个包含标题列和包含 1 或 NULL 的 3 位列(f1、f2、f3)的表，我将如何编写 LINQ 以返回包含每个位列的计数的标题，其中包含 1?我正在寻找与此 SQL 查询等效的内容:

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

我正在寻找最好"的方式来做到这一点.我想出来的版本在看底层SQL的时候进了4次表，太慢了.

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.

推荐答案

这是我想出的解决方案.请注意，它接近@OdeToCode 提出的解决方案(但采用 VB 语法)，但有一个主要区别:

Here's the solution I came up with. Note that it's close to the solution proposed by @OdeToCode (but in VB syntax), with one major 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 = t.g.Count(Function(x) If(x.f1, False)), _
        f2_count = t.g.Count(Function(x) If(x.f2, False)), _
        f3_count = t.g.Count(Function(x) If(x.f3, False))

第一个查询进行分组，但 ToList 从服务器获取分组数据.消除此处的计数可防止生成的 SQL 语句为每个计数生成子 SELECT.我在本地进行第二个查询中的计数.

The first query does the grouping, but the ToList gets the grouped data as-is from the server. Eliminating the counting here keeps the resulting SQL statement from producing sub-SELECTs for each count. I do the counting in the second query locally.

这是有效的，因为我知道第一个查询将返回可管理的行数.如果它返回数百万行，我可能不得不转向另一个方向.

This works since I know the first query will return a manageable number of rows. If it were returning millions of rows, I'd probably have to go in another direction.

这篇关于多列上的 LINQ COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！