How to get row number via LINQ using Entity framework?
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.
Comments
-
Mads Laumann almost 2 years
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?
-
Mads Laumann about 13 yearsI tried your first suggestion and get this error now at compile time:Error 2 The type arguments for method 'System.Linq.Queryable.Select<TSource,TResult>(System.Linq.IQueryable<TSource>, System.Linq.Expressions.Expression<System.Func<TSource,TResult>>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.
-
Steven about 13 yearsOeps! Sorry. You should replace 'he' with 'entry'. See my updated answer.
-
Mads Laumann about 13 yearsIf I try your AsEnumerable() suggestion I get this error: "{"There is already an open DataReader associated with this Command which must be closed first."}"
-
Steven about 13 yearsTry changing the
.AsEnumerable()
call to.ToArray()
. If that doesn't work, please post the stack trace, because the problem does not lie in the query itself. -
Mads Laumann about 13 yearsOk ToArray() is working. I now just need to make sure I don't select out too much, it will fetch it all from DB when I call ToArray(), but I think I can handle that :)
-
Steven about 13 yearsYou can call
.Take(10)
(or the exact number you need) before calling.ToArray()
. That will prevent the query from pulling down the complete table. -
Josiah over 7 yearsIf you call AsEnumerable as written then your unfiltered select statement is going to pull up all the records from that table. Big performance hit, huge memory waste, not production ready. Project doesn't work, ding reputation, pay cut, disgrace. Unemployment, alcoholism, homelessness, disease. Death. Just say no.