Is it possible to do all this in a single LINQ query?

Question

I have a function like this

private List<Score> getPageNRows ( int N )
{
    // Returns object corresponding to the rows of the table
    // on "page" N of the scores page
    return (from s in this._SD.Scores
            orderby s.score1 descending
            select s)
            .Skip(ScoresController._scoresPerPage * (N - 1))
            .Take(ScoresController._scoresPerPage * N)
            .ToList();
}

Where a Score is defined by

public partial class Score
{
    public Score()
    {
        GameLogs = new HashSet<GameLog>();
    }

    public int id { get; set; }

    [Column("score")]
    public int score1 { get; set; }

    [StringLength(50)]
    public string name { get; set; }

    public DateTime playdate { get; set; }

    public virtual ICollection<GameLog> GameLogs { get; set; }
}

Here, what I really want is a List<ViewScore> where ViewScore is defined by

public class ViewScore
{
    public int score { get; set; } // corresponds directly to score1 in Score class
    public string name { get; set; } // corresponds directly to name in Score
    public string datestr { get; set; } // corresponds to playdate.ToString()
}

Is this possible to do all in the LINQ query or do I need to create helper methods?

At the very least, how do I select only the columns s.score1, s.name and s.playdate instead of all of them (via select) ?

Nevermind, others (e.g. Arghya C) have said the same thing I said, but as a solid answer. :) — David Betz, Nov 01 '15 at 03:38

score 2 · Accepted Answer · answered Oct 31 '15 at 08:09

Yes, you can do it with Linq like this

return this._SD.Scores
            .OrderByDescending(s => s.score1)
            .Skip(ScoresController._scoresPerPage * (N - 1))
            .Take(ScoresController._scoresPerPage * N))
            .Select(s => new ViewScore { score = s.score1, name = s.name, datestr = s.playdate.ToString() })
            .ToList();

score 0 · Answer 2 · edited May 23 '17 at 11:44

I would recommend going with the lambda path:

private List<Score> getPageNRows ( int N )
{
    // Returns object corresponding to the rows of the table
    // on "page" N of the scores page
    return this._SD.Scores.OrderByDescending(c => c.score1)
                          .Skip(ScoresController._scoresPerPage * (N - 1))
                          .Take(ScoresController._scoresPerPage * N)
                          .ToList();
}

According to MSDN, skip and take are not supported in the query syntax: See Here.

And see this stack overflow question asking a similar thing.

Now if you want to project the Score class into your ViewScore class, just add a Select<TSource, TResult> statement:

private List<ViewScore> getPageNRows ( int N )
{
    // Returns object corresponding to the rows of the table
    // on "page" N of the scores page
    return this._SD.Scores.OrderByDescending(c => c.score1)
                          .Skip(ScoresController._scoresPerPage * (N - 1))
                          .Take(ScoresController._scoresPerPage * N)
                          .Select(c => new ViewScore()
                                       {
                                           score = c.score1,
                                           name = c.name,
                                           datestr = c.playdate.ToString()
                                       })
                          .ToList();
}

The name `s` does not exist in the current context – Failed Computer Science 101 Oct 31 '15 at 16:39 — Failed Computer Science 101, Oct 31 '15 at 16:39

score 0 · Answer 3 · answered Oct 31 '15 at 06:28

It is possible to return only selected columns using projection. The method Select() is used for projetion:

return (from s in this._SD.Scores
                orderby s.score1 descending
                select s
                ).Skip(ScoresController._scoresPerPage * (N - 1)
                ).Take(ScoresController._scoresPerPage * N)
                 .Select(x => new ViewScore() { score = x.score1, name = x.name, datestr = x.playdate  })
                 .ToList();

Using Select() before the query gets materialized with ToList() is very handy to limit data returned from DB only to what is really needed.

Is it possible to do all this in a single LINQ query?

3 Answers3