How To Project a Line Number Into Linq Query Results

Question

How can I project the row number onto the linq query result set.

Instead of say:

field1, field2, field3

I would like:

1, field1, field2, field3

2, field1, field2, field3

Here is my attempt at this:

public List<ScoreWithRank> GetHighScoresWithRank(string gameId, int count)
{
    Guid guid = new Guid(gameId);
    using (PPGEntities entities = new PPGEntities())
    {
        int i = 1;
        var query = from s in entities.Scores
                    where s.Game.Id == guid
                    orderby s.PlayerScore descending
                    select new ScoreWithRank()
                    {
                        Rank=i++,
                        PlayerName = s.PlayerName,
                        PlayerScore = s.PlayerScore
                    };
        return query.ToList<ScoreWithRank>();
    }
}

Unfortunately, the "Rank=i++" line throws the following compile-time exception:

"An expression tree may not contain an assignment operator"

Possible duplicate of [How do you add an index field to Linq results](http://stackoverflow.com/questions/269058/how-do-you-add-an-index-field-to-linq-results) — Michael Freidgeim, Jul 13 '16 at 03:28

Jon Skeet · Answer 1 · 2014-10-01T05:58:44.280

56

Well, the easiest way would be to do it at the client side rather than the database side, and use the overload of Select which provides an index as well:

public List<ScoreWithRank> GetHighScoresWithRank(string gameId, int count)
{
    Guid guid = new Guid(gameId);
    using (PPGEntities entities = new PPGEntities())
    {
        var query = from s in entities.Scores
                    where s.Game.Id == guid
                    orderby s.PlayerScore descending
                    select new
                    {
                        PlayerName = s.PlayerName,
                        PlayerScore = s.PlayerScore
                    };

        return query.AsEnumerable() // Client-side from here on
                    .Select((player, index) => new ScoreWithRank()
                            {
                                PlayerName = player.PlayerName,
                                PlayerScore = player.PlayerScore,
                                Rank = index + 1;
                            })
                    .ToList();

    }
}

edited Oct 01 '14 at 05:58

answered Dec 13 '08 at 11:39

Jon Skeet

1,421,763
867
9,128
9,194

4

getting everything from the database it's not really a 'solution' – DATEx2 Apr 27 '11 at 08:42
1

@DotNetWise: It's not getting *everything* from the database - only the bit which matches the query. It's only getting the same amount of data from the database as the original attempt - just doing a little bit of post-processing. – Jon Skeet Apr 27 '11 at 08:52
How so? query.AsEnumerable() will feed all the matched records for the given gameId. Try to take only the positions ranked after 20th. You will get everything from the db in order to have the ranks and then cut out what you need. Not really the desired solution! Other than that - where is the count parameter used? – DATEx2 Jun 25 '11 at 08:49
6

@DotNetWise: I agree that the `count` parameter isn't being used yet, but so long as that's used *before* the `AsEnumerable()` call, it's fine. In particular, the `where` clause and the `orderby` clause are used before `AsEnumerable`, so all that filtering will occur in the database. As I said in my previous comment, it's only getting the records which match the query... in other words, the data which is required anyway. If you want to get positions ranked after 20th, you'd add a `Skip` call to `query`, or use `query.Skip(20).AsEnumerable()`. (You'd then want to adjust the `Rank` calculation.) – Jon Skeet Jun 25 '11 at 09:32
IMO, this is why using linq is not a good option for database queries. I will continue to write stored procs or functions in the database and use linq for client side stuff. – MikeKulls Dec 07 '11 at 22:09
7

@MikeKulls: So just because you can't do *all* database stuff with LINQ, you do *none* of it? That sounds like throwing the baby out with the bathwater to me. – Jon Skeet Dec 07 '11 at 22:18
I absolutely have no idea WHY this works..., but it works! I guess I will have to read some lambda tutorial now. – Rosdi Kasim Mar 20 '15 at 14:05

score 1 · Answer 2 · answered Dec 13 '08 at 12:09

1

Ok, that did the trick. Thanks.

Here is my final code...

Server:

public List<Score> GetHighScores(string gameId, int count)
{
    Guid guid = new Guid(gameId);
    using (PPGEntities entities = new PPGEntities())
    {
        var query = from s in entities.Scores
                    where s.Game.Id == guid
                    orderby s.PlayerScore descending
                    select s;
        return query.ToList<Score>();
    }                                                                      
}

Client:

void hsc_LoadHighScoreCompleted(object sender, GetHighScoreCompletedEventArgs e)
{
    ObservableCollection<Score> list = e.Result;

    _listBox.ItemsSource = list.Select((player, index) => new ScoreWithRank()
                            {
                                PlayerName = player.PlayerName,
                                PlayerScore = player.PlayerScore,
                                Rank = index+=1
                            }).ToList();
}

answered Dec 13 '08 at 12:09

Jeff Weber

949
1
8
18

Do you really need GetHighScores() to return a List instead of an IEnumerable? If you're going to convert it into a list, you might as well only do it once. – Jon Skeet Dec 13 '08 at 12:17
@Jon: He could call AsEnumerable instead but...The AsEnumerable method has no effect other than to change the compile-time type of source. http://msdn.microsoft.com/en-us/library/bb335435.aspx - in other words, it won't bring the objects into memory. If he wants control over that, ToList is good – Amy B Dec 13 '08 at 14:24
Yes, but only *if* he needs to do it at that point. If he doesn't need to there's no point in copying all the data twice. Hence the question nature of my cooment :) In fact even AsEnumerable isn't needed of course - if the GetHighScores method is declared to return IEnumerable that'll do it. – Jon Skeet Dec 13 '08 at 14:29
Wouldn't `Rank = index += 1` be better as `Rank = index+1` ? – NetMage Jun 23 '17 at 20:21

score 0 · Answer 3 · answered Feb 03 '13 at 12:56

0

This solution worked for me. http://www.dotnetfunda.com/articles/article1995-rownumber-simulation-in-linq.aspx

.Select((x, index) => new
{
     SequentialNumber = index + 1
    ,FieldFoo = x.FieldFoo                        
}).ToList();

answered Feb 03 '13 at 12:56

Onur Bıyık

371
4
16

The overload that takes an Int32 parameter is not supported by the entity framework. Notice that the article at dotNetFunda works with linq to objects. – Ashraf Sabry Dec 10 '13 at 09:07

score 0 · Answer 4 · edited Jun 10 '19 at 15:57

0

List<Emp> Lstemp = GetEmpList(); 
int Srno = 0; 
var columns = from t in Lstemp 
              orderby t.Name 
              select new { 
                  Row_number=++Srno, 
                  EmpID = t.ID, 
                  Name = t.Name, 
                  City = t.City 
              };

edited Jun 10 '19 at 15:57

Vahid Farahmandian

6,081
7
42
62

answered Jan 12 '19 at 14:48

Rohit Dodiya

261
3
4

shannonlh · Answer 5 · 2012-02-03T20:40:59.203

You could also make just a slight adjustment to your original code to get it working. Word of caution, if you databind or access the object again, the Rank will increment each time. In those cases the top answer is better.

let Rank = i++

and

Rank.ToString()

Full code:

public List<ScoreWithRank> GetHighScoresWithRank(string gameId, int count)
{
Guid guid = new Guid(gameId);
using (PPGEntities entities = new PPGEntities())
{
    int i = 1;
    var query = from s in entities.Scores
                let Rank = i++
                where s.Game.Id == guid
                orderby s.PlayerScore descending
                select new ScoreWithRank()
                {
                    Rank.ToString(),
                    PlayerName = s.PlayerName,
                    PlayerScore = s.PlayerScore
                };
    return query.ToList<ScoreWithRank>();
}

}

This code won't even compile. It generates the error CS0832: An expression tree may not contain an assignment operator — Ashraf Sabry, Dec 10 '13 at 09:10

How To Project a Line Number Into Linq Query Results

5 Answers5

Linked