实体框架中是否有一个函数可以转换为 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 翻译.Select() 使用行号；您必须使用 .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.

那我该怎么做呢?

推荐答案

AFAIK Rank() 在 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
        });

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