多个列LINQ COUNT [英] LINQ COUNT on multiple columns

问题描述

如果我有一个标题栏和第3位的列（F1，F2，F3）含有1个或空表，如何将我写的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

我在寻找最佳的方式做到这一点。该版本我想出了逢低进表4次，当你看下面的SQL，所以它的速度太慢。

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.

推荐答案

下面是我想出了一个解决方案。需要注意的是它靠近提出@ OdeTo code（但在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语句。我做的第二个查询计数本地。

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屋！