在Entity框架中是否有一个函数转换为SQL中的RANK（）函数？ [英] Is there a function in Entity Framework that translates to the RANK() function in SQL?

问题描述

比方说，我想按国家排名我的客户数据库。在SQL中我会写：

Let's say I want to rank my customer database by country. In SQL I would write:

select CountryID, CustomerCount = count(*), 
       [Rank] = RANK() over (order by count(*) desc)
from Customer

现在我想实体框架中这样写：

Now I want to write this in Entity Framework:

var ranks = db.Customers
  .GroupBy(c => c.CountryID)
  .OrderByDescending(g => g.Count())
  .Select((g, index) => new {CountryID = g.Key, CustomerCount = g.Count, Rank = index+1});

有两个问题：

1. 这是行不通的。 EF抛出一个 System.NotSupportedException ;显然没有SQL翻译<$ C的超载$ C>。选择（） 使用的行号;你将不得不拉一切与内存中的 .ToList（）为了能够调用此方法;和


2. 即使您运行在本地内存的方法，它不处理排名相同的方式的 RANK（） 功能SQL确实，也就是说，它们应该有同等级别，然后以下项目跳转到原来的顺序。

1. It doesn't work. EF throws a System.NotSupportedException; evidently there's no SQL translation for the overload of .Select() that uses the row number; you would have to pull everything into memory with a .ToList() in order to be able to call this method; and
2. Even if you run the method in local memory, it doesn't handle equal rankings the way the RANK() function does in SQL, i.e. they should have an equal rank, and then the following item skips to the original order.

所以，我应该怎么做呢？

So how should I do this?

推荐答案

据我所知排名（）在LINQ没有内置函数。 这个答案使用你的方法，但它似乎为他们工作。这里是你如何使用它：

AFAIK Rank() has no builtin function in LINQ. This answer uses your approach, but it seems to work for them. Here's how you could use it:

var customersByCountry = db.Customers
    .GroupBy(c => c.CountryID);
    .Select(g => new { CountryID = g.Key, Count = g.Count() });
var ranks = customersByCountry
    .Select(c => new 
        { 
            c.CountryID, 
            c.Count, 
            Rank = customersByCountry.Count(c2 => c2.Count > c.Count) + 1
        });

这篇关于在Entity框架中是否有一个函数转换为SQL中的RANK（）函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！