CODEWITHSUNDEEP
c#asp.netasp.net-mvcalgorithmlinq
Failed Computer Science 101
Failed Computer Science 101

Reputation: 135

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

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) ?

Upvotes: 0

Views: 105

Answers (3)

tophallen
tophallen

Reputation: 1063

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();
}

Upvotes: 0

Arghya C
Arghya C

Reputation: 10068

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();

Upvotes: 2

Martin Staufcik
Martin Staufcik

Reputation: 9490

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.

Upvotes: 0

Related Questions