如何使用Entity框架通过LINQ获取行号？ [英] How to get row number via LINQ using Entity framework?

问题描述

希望有人可以帮助我在这里，因为我有点卡住。

我在游戏的线索数据库前面建立一个服务。 / p>

数据库有以下两个表：

  CREATE TABLE [dbo ]。[PB_HiscoreEntry]（
 [Id] UNIQUEIDENTIFIER NOT NULL，
 [PlayerId] UNIQUEIDENTIFIER NOT NULL，
 [Score] INT NOT NULL，
 [DateCreated] DATETIME NOT NULL 
）; 
 
 
 CREATE TABLE [dbo]。[PB_Player]（
 [Id] UNIQUEIDENTIFIER NOT NULL，
 [UniquePlayerId] NCHAR（32）NOT NULL，
 [Name] NVARCHAR（50）NOT NULL，
 [DateCreated] DATETIME NOT NULL 
）; 
  

这个想法当然是只有每个玩家一次在数据库中， 。这个表PB_HiscoreEntry将有很多分数，但是通过做一个简单的OrderBy降序，我可以创建一个真正的核心列表，其中最高分的一个在顶部，最低在底部。
我的问题是，我的数据库不知道与其他人相比的分数的实际排名。这是我应该做的，因为我做上面的OrderBy查询。

这里是一些代码来帮助illutrate我想要归档：

  var q =（
 from he in entities.PB_HiscoreEntry 
 orderby he.Score ascending 
 select new HiscoreItem b $ b {
 UserId = he.PB_Player.UniquePlayerId，
 Username = he.PB_Player.Name，
 Score = he.Score，
 //输入排名，相对到其他entires这里
 Rank = 1 
}）; 
 

HiscoreItem，只是我自己的DTO，我需要通过电线发送。

$ b

解决方案

div>

你正确的轨道，你只需要使用 Queryable.Select 重载，需要一个额外的索引。看看这个：

  var entries = 
来自entities.PB_HiscoreEntry 
 orderby条目中的条目。分数递减
选择条目; 
 
 //注意这里的（entry，index）lambda。 
 var hiscores = entries.Select（（entry，index）=> new HiscoreItem（）
 {
 UserId = entry.PB_Player.UniquePlayerId，
 Username = entry.PB_Player。 Name，
 Score = entry.Score，
 Rank = index + 1 
}）; 
  

我不是100％确定Entity Framework知道如何使用
选择< TSource，TResult>（this IQueryable< TSource>，Expression< Func< TSource，int，TResult>） overload。如果是这种情况，只需使用静态 Enumerable 类的等效方法：

  //注意.AsEnumerable（）这里。 
 var hiscores = entries.AsEnumerable（）
 .Select（（entry，index）=> new HiscoreItem（）
 {
 UserId = entry.PB_Player.UniquePlayerId，
 Username = entry.PB_Player.Name，
 Score = entry.Score，
 Rank = index + 1 
}）; 
  

我希望这有助于。

Hope someone can help me out here as I'm a little stuck.

I'm building a service in front of a hiscore database for a game.

The database have the following two tables:

CREATE TABLE [dbo].[PB_HiscoreEntry] (
    [Id]          UNIQUEIDENTIFIER NOT NULL,
    [PlayerId]    UNIQUEIDENTIFIER NOT NULL,
    [Score]       INT              NOT NULL,
    [DateCreated] DATETIME         NOT NULL
);


CREATE TABLE [dbo].[PB_Player] (
    [Id]             UNIQUEIDENTIFIER NOT NULL,
    [UniquePlayerId] NCHAR (32)       NOT NULL,
    [Name]           NVARCHAR (50)    NOT NULL,
    [DateCreated]    DATETIME         NOT NULL
);

The idea is of course to only have each player once in the database and let them have multiple hiscore entries. This table PB_HiscoreEntry will have a lot of scores, but by doing a simple OrderBy descending, I can create a real hiscore list where the one with highest score is at the top and the lowest at the bottom. My problem here is that my database don't have any idea of the actual Rank of the score compared to the others. This is something I should do as I do the OrderBy query described above.

Here is some code to help illutrate what I want to archive:

var q = (
    from he in entities.PB_HiscoreEntry
    orderby he.Score descending
    select new HiscoreItem()
    {
        UserId = he.PB_Player.UniquePlayerId,
        Username = he.PB_Player.Name,
        Score = he.Score,
        //Put in the rank, relative to the other entires here
        Rank = 1 
    });

HiscoreItem, is just my own DTO i need to send over the wire.

So anybody have an idea of how I can do this or am I on a totally wrong path here?

解决方案

You're on the right track, you just need to use the Queryable.Select overload that takes an extra index. Take a look at this:

var entries =
    from entry in entities.PB_HiscoreEntry
    orderby entry.Score descending
    select entry;

// Note the (entry, index) lambda here.
var hiscores = entries.Select((entry, index) => new HiscoreItem()
{
    UserId = entry.PB_Player.UniquePlayerId,
    Username = entry.PB_Player.Name,
    Score = entry.Score,
    Rank = index + 1
});

I'm not 100% sure if Entity Framework knows how to work with the Select<TSource, TResult>(this IQueryable<TSource>, Expression<Func<TSource, int, TResult>>) overload. If that's the case, just use the equivalent method of the static Enumerable class:

// Note the .AsEnumerable() here.
var hiscores = entries.AsEnumerable()
    .Select((entry, index) => new HiscoreItem()
{
    UserId = entry.PB_Player.UniquePlayerId,
    Username = entry.PB_Player.Name,
    Score = entry.Score,
    Rank = index + 1
});

I hope this helps.

这篇关于如何使用Entity框架通过LINQ获取行号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！