How get Row Index in a SQL Database using entity framework without query all data?

Question

We do have a database with thousand of entries. we would like to get the "ranked position" for a specific item by its name.

Since there is a lot of data we would like to avoid bring query ALL data in order to determine row index (using ToList() and IndexOf() for instance).

I tried using

        List<Ranking> ranking = _context.Testes
           .OrderByDescending(t => t.Val)
           .Select((d, i) => new Ranking() {
               Name = d.Name,
               Ranking= i
           }).First(d=>d.Name = "Test");

but I got this error:

'value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[WebApplication4.Models.Teste]).OrderByDescending(t => t.Val).Select((d, i) => new Ranking() {Name = d.Name, Ranking = i})': This overload of the method 'System.Linq.Queryable.Select' is currently not supported.

Is that possible somehow?

Skip and take will get the nth position of the list. I would like to provide a "Name" and query the ranked position of it. — Daniel Santos
– Daniel Santos, Commented Jun 13, 2017 at 13:39
You can't translate this Select() overload to SQL. ORMs aren't meant for reporting and this is 100% a reporting query. SQL Server offers ranking functions like ROW_NUMBER, RANK and DENSE_RANK. You could create a view that calculates the rank and map your rankings to it, eg CREATE VIEW Rankings AS SELECT Name,RANK OVER(ORDER BY Val) Ranking From Tests. EF will probably map the Ranking class to the Rankings view by convention. If not, you map it using the Table attribute — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 13, 2017 at 14:00

Panagiotis Kanavos · Accepted Answer · 2017-06-13 14:14:52Z

You can't translate this Select() overload to SQL. ORMs aren't meant for reporting and this is 100% a reporting query.

SQL Server offers ranking functions like ROW_NUMBER, RANK and DENSE_RANK. You could create a view that calculates the rank and map your rankings to it, eg :

CREATE VIEW Rankings 
AS 
SELECT 
    Name,
    DENSE_RANK() OVER(ORDER BY Val) Ranking 
From Tests

DENSE_RANK() will return the same rank number if two records tie and continue with the next rank number. ROW_NUMBER will just use incrementing numbers. If you use ROW_NUMBER you should probably use additional sorting critera to avoid generating random rankings for ties.

EF will probably map the Ranking class to the Rankings view by convention. If not, you map it using the Table attribute or ToTable if you use Code-First configuration :

[Table("Rankings")
public class Ranking
{
    public string Name{get;set;}
    public int Ranking {get;set;}
}

Retrieving a specific ranking requires only a Where() clause:

var someRanking=context.Rankings.Where(r=>r.Name=someName);

David Browne - Microsoft · Accepted Answer · 2017-06-13 14:24:55Z

In LINQ something like (note you must handle ties to get a well-defined ranking)

            var q = from t in db.Testes
                    where t.Name == "whatever"
                    select new
                      {
                        Testes =t,
                        Rank =1+db.Testes.Where(ot => ot.Val < t.Val || (ot.Val == t.Val && ot.Id < t.Id) ).Count()
                      };

which translates to

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Val] AS [Val], 
    [Project1].[Name] AS [Name], 
    1 + [Project1].[C1] AS [C1]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Val] AS [Val], 
        [Extent1].[Name] AS [Name], 
        (SELECT 
            COUNT(1) AS [A1]
            FROM [dbo].[Testes] AS [Extent2]
            WHERE ([Extent2].[Val] < [Extent1].[Val]) OR (([Extent2].[Val] = [Extent1].[Val]) AND ([Extent2].[Id] < [Extent1].[Id]))) AS [C1]
        FROM [dbo].[Testes] AS [Extent1]
        WHERE N'whatever' = [Extent1].[Name]
    )  AS [Project1]

Collectives™ on Stack Overflow

How get Row Index in a SQL Database using entity framework without query all data?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related