LINQ(对Oracle) - Row_Number()除以分区。

This is a possible duplicate of other Partition By + Rank questions but I found most of those questions/answers to be too specific to their particular business logic. What I'm looking for is a more general LINQ version of the following type of query:

这可能是对其他分区按+秩的问题的重复，但我发现这些问题/答案大多过于特定于它们的特定业务逻辑。我要寻找的是以下类型查询的更通用的LINQ版本:

SELECT id,
       field1,
       field2,
       ROW_NUMBER() OVER (PARTITION BY id
                          ORDER BY field1 desc) ROWNUM
FROM someTable;

A very common thing we do with this is to wrap it like in something like this:

我们做的一件很常见的事情就是把它像这样包起来:

SELECT id,
       field1,
       field2
FROM (SELECT id,
      field1,
      field2,
      ROW_NUMBER() OVER (PARTITION BY id
                         ORDER BY field1 desc) ROWNUM
      FROM someTable)
WHERE ROWNUM = 1;

Which returns the row containing the highest value in field1 for each id. Changing the order by to asc of course would return the lowest value or changing the rank to 2 will get the second highest/lowest value etc, etc. Is there a way to write a LINQ query that can be executed server side that gives us the same sort of functionality? Ideally, one that as performant as the above.

它返回的行包含field1的最高价值为每个id。改变order by asc当然将返回值或改变排名最低的2将得到第二个最高/最低价值等等,等等。有办法编写一个LINQ查询,可以执行服务器端给了我们同样的功能?最理想的是，一个像上面一样的表现。

Edit: I've tried numerous different solutions after scouring the web and they all end up giving me the same problem that Reed's answer below does because the SQL generated includes an APPLY.

编辑:在搜索了web之后，我尝试了许多不同的解决方案，它们最终都给了我与Reed下面的答案相同的问题，因为生成的SQL包含一个APPLY。

A couple examples I tried:

我尝试了几个例子:

from p in db.someTable
group p by p.id into g
let mostRecent = g.OrderByDescending(o => o.field1).FirstOrDefault()
select new {
    g.Key,
    mostRecent
};

db.someTable
    .GroupBy(g => g.id, (a, b) => b.OrderByDescending(o => o.field1).Take(1))
    .SelectMany(m => m);

Both of these result in very similar, if not identical, SQL code which uses an OUTER APPLY that Oracle does not support.

这两种方法都会产生非常相似(如果不是完全相同的话)的SQL代码，这些SQL代码使用Oracle不支持的外部应用程序。

2 个解决方案

#1

You should be able to do something like:

你应该能够做到以下几点:

var results = someTable
                 .GroupBy(row => row.id)
                 .Select(group => group.OrderByDescending(r => r.id).First());

If you wanted the third highest value, you could do something like:

如果你想要第三个最高的值，你可以这样做:

var results = someTable
                 .GroupBy(row => row.id)
                 .Select(group => group.OrderByDescending(r => r.id).Skip(2).FirstOrDefault())
                 .Where(r => r != null); // Remove the groups that don't have 3 items

#2

an alternative way, by using a subquery which separately gets the maximum field1 for each ID.

另一种方法，通过使用子查询，分别获得每个ID的最大field1。

SELECT  a.*
FROM    someTable a
        INNER JOIN
        (
            SELECT  id, max(field1) max_field
            FROM    sometable
            GROUP   BY id
        ) b     ON a.id = b.ID AND
                    a.field1 = b.max_field

when converted to LINQ:

当转换为LINQ:

from a in someTable
join b in 
    (
        from o in someTable
        group o by new {o.ID} into g
        select new 
        {
          g.Key.ID,
          max_field = g.Max(p => p.field1)
        }
    ) on new {a.ID, a.field1} equals new {b.ID, field1 = b.max_field}
select a

#1