检查分组集中的列是否包含除第一行以外的相同值

I am working on a rather large query, but am now stuck on the last bit. Given this example table:

我正在进行一个相当大的查询，但我现在停留在最后一点。鉴于此示例表：

  Key1  |  Key2  |  SomeCol  |
    0   |    0   |    ABC    |
    0   |    1   |    123    |
------------------------------
    1   |    5   |    ABC    |
    1   |    6   |    DEF    |
    1   |    7   |    ABC    |
------------------------------
    2   |    4   |    ABC    |
    2   |    5   |    456    |
    2   |    6   |    456    |
------------------------------
    3   |    4   |    ABC    |
    3   |    5   |    456    |
    3   |    6   |    ABC    |
------------------------------
    4   |    4   |    ABC    |
    4   |    5   |    ABC    |
    4   |    6   |    ABC    |

At this point in my query, I have extracted sequential(Key1, Key2) portions of a table and grouped by Key1. I wish to determine if all the values of SomeCol are identical, except for the first row.

在我的查询中，我已经提取了表的顺序（Key1，Key2）部分并按Key1分组。我想确定SomeCol的所有值是否相同，除了第一行。

Expected results:
  Key1  |  Key2  |  SomeCol  |
    0   |    0   |    ABC    |
    2   |    4   |    ABC    |
    4   |    4   |    ABC    |

I know I can use something like.Any(g => g.SomeCol.Distinct().Count() == 1) in a case where I need all entries to be the same, but I can't seem to figure out how to get the syntax right to Skip(1). Also, I feel that my method of checking equality is sort of a hack. I know I can do this processing easily in C#, but I want to get as much of the processing to happen on the database side. Since my query is currently written in extension methods, I would appreciate in answer in the same syntax. Thanks!

我知道我可以使用像.Any（g => g.SomeCol.Distinct（）。Count（）== 1）在我需要所有条目相同的情况下，但我似乎无法弄清楚如何将语法权限改为Skip（1）。此外，我觉得我检查平等的方法有点像黑客。我知道我可以在C＃中轻松完成这个处理，但我想在数据库端获得尽可能多的处理。由于我的查询目前是用扩展方法编写的，我希望在相同的语法中回答。谢谢！

What I have so far:

到目前为止我所拥有的：

resultFromRestOfQuery
.GroupBy(g => g.Key1)
????
.SelectMany(g => g.Take(1).Select(h => h)

1 个解决方案

#1

UPDATE

UPDATE

Alright, tested this on your values and it works.

好吧，根据你的价值测试这个，它的确有效。

var result = collection
            .OrderBy(p => p.Key1)
            .ThenBy(p => p.Key2)
            .GroupBy(p => p.Key1)
            .Where(p => p.Skip(1)
                    .Select(j => j.SomeCol)
                    .Distinct().Count() == 1)
            .Select(p => p.First())
            .ToList();

UPDATE #2

更新＃2

Perhaps this will help you with performance. Here is another version of this query without using Distinct(). Note the p.Count() > 1 - this is to avoid selecting the 1st row in a group when there's only 1 element in a group. If it's okay to select the first row when there's only one row, simply remove this part of the condition.

也许这会对你的表现有所帮助。这是此查询的另一个版本，不使用Distinct（）。请注意p.Count（）> 1 - 这是为了避免在组中只有1个元素时选择组中的第1行。如果只有一行可以选择第一行，只需删除这部分条件即可。

 var result = collection
            .OrderBy(p => p.Key1)
            .ThenBy(p => p.Key2)
            .GroupBy(p => p.Key1)
            .Where(p => p.Count() > 1 && p.Skip(1)
                    .Select(j => j.SomeCol)
                    .All(j => j == p.Last().SomeCol))
            .Select(p => p.First())
            .ToList();

#1