0

I have a table with many records,

This table contains a chart, I would like to get the position of a single user:

Here is what I tried but it doesn't work:

var res = _context.Chart
            .OrderByDescending(r => r.Score)
             .SkipWhile(x => x.Username != username)
             .Select((t, i) => new { idx = i })

This gives an error, I tried many combination but I can't get it to work.

Processing of the LINQ expression 'DbSet .OrderByDescending(r => r.Score) .SkipWhile(x => x.Username != __username_0)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core.

Any idea of how I should do it? Thanks!

Update: The following code works!

_context.Chart
                .AsEnumerable()
                .OrderByDescending(r => r.Score)
                .Select((t, i) => new { Index = i, t.Score, t.Username })
                .FirstOrDefault(c => c.Username == username);

But I am worried about performance, do you think it can cause problems? thanks

gabrigabra
  • 13
  • 4
  • What error? Your code seems to be the same as the proposed solution in [this question](https://stackoverflow.com/a/4827558/3181933). – ProgrammingLlama Sep 01 '21 at 10:14
  • Yes it is.. here is the error: Processing of the LINQ expression 'DbSet .OrderByDescending(r => r.Score) .SkipWhile(x => x.Username != __username_0)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. – gabrigabra Sep 01 '21 at 10:16
  • I wonder if it's struggling to convert the `.SkipWhile` part? – ProgrammingLlama Sep 01 '21 at 10:18
  • does it help https://stackoverflow.com/a/59282608/6527049 – Vivek Nuna Sep 01 '21 at 10:26
  • @Llama I tried without the skipWhile, it gives the same error: await _context.Chart .Select((t, i) => new { Index = i, Score = t.Score, Username = t.Username }) .OrderByDescending(r => r.Score) .Where(x => x.Username == username) .FirstOrDefaultAsync(); – gabrigabra Sep 01 '21 at 11:17
  • @viveknuna I also tried to change the settings like the question that you linked, it didn't work unfortunately – gabrigabra Sep 01 '21 at 11:19

1 Answers1

0

If you want scores lower then some users try filtering by score which is lower then max score for that user name:

var res = _context.Chart
    .Where(r => r.Score <= _context.Chart.Where(x => x.Username != username).Max(x => x.Score))
    .OrderByDescending(r => r.Score)
    .Select((t, i) => new { idx = i })

If you want row number of concrete user then you will need to have the select before the filtering. Try something like this:

var res = _context.Chart
    .Select((t, i) => new { rownum = i, t.Score, t.Username })
    .OrderByDescending(r => r.Score)
    .Where(x => x.Username == username)
    .FirstOrDefault()
Guru Stron
  • 102,774
  • 10
  • 95
  • 132