2

I'm trying to display a result from a database via via that includes an aggregate value from the same table. i have a table that has Name, Score and Round which holds players names and scores for each particular round. I want to display that in a grid (say I select round 5) but I also want to show the rank of each player which is their total score over all rounds up to and including the selected round.

So far I have two queries - one returns a list of the the names and the sum of their round scores and another that retrieves their name, score for a particular round. I only need the index of the sum though to get the rank, I don't need the sum of the score itself. I'm trying to include the first query in the second query with something like

Rank = r1.FindIndex(r => r.Name == tr.Name)

but I keep getting the error 'LINQ to Entities does not recognize the method 'Int32 FindIndex'. I know why I'm getting this (it can't be compiled into SQL) but I can't figure out how to get around it. So, how can I structure my query so it gets the rows I need from the table but also factors in the correct rank i.e. index of a player in the first query? Any advice?

Dmitry
  • 13,797
  • 6
  • 32
  • 48
Steve
  • 1,903
  • 5
  • 22
  • 30

2 Answers2

1

If you want queries to be run in-memory, use the IEnumerable form instead of the IQueryable form. The method to do this cleanly with Linq is .AsEnumerable()

Rank = r1.AsEnumerable().FindIndex(r => r.Name == tr.Name);

Note that the call to AsEnumerable() is necessary ONLY if the compile-time type is IQueryable. Since we're talking about extension methods, they'll be called correctly based on the type known at runtime - and all the IEnumerable methods use a foreach and yield return pattern, which will iterate the SQL query into memory

David
  • 10,458
  • 1
  • 28
  • 40
  • Thanks for help but that line now says IEnumerable does not contain a definition for FindIndex. – Steve Nov 26 '14 at 02:59
  • 1
    This is because IEnumerable has no definition for FindIndex(). Maybe this can help you: [How to get the index of an element in an IEnumerable](http://stackoverflow.com/questions/1290603/how-to-get-the-index-of-an-element-in-an-ienumerable) in case you follow Aravol's advice. – flaudre Nov 26 '14 at 10:08
0

Thanks to both Aravol and Flaudre. In the end a simply changed the data structure at the db end to hold the information I needed via a view. It was way simpler to implement than trying to figure these LINQ errors.

Steve
  • 1,903
  • 5
  • 22
  • 30