c# - LINQ grouping and ordering of top 5 rows into ViewModel List -

i trying create new list of top 5 hits leaders softball web application. new mvc , having trouble putting query , passing information viewmodel. use non-query call "var results" in controller if possible. trying group atbats playerid in stat table. able call firstname , lastname of player player table in view list. thank help!

in stat.cs

using system;  namespace theflyingpig.models {     public class stat     {         public int statid { get; set; }         public int seasonid { get; set; }         public int playerid { get; set; }         public datetime gamedate { get; set; }         public int atbats { get; set; }         public int hits { get; set; }         public int walks { get; set; }         public int singles { get; set; }         public int doubles { get; set; }         public int triples { get; set; }         public int homeruns { get; set; }         public int rbis { get; set; }         public int runs { get; set; }         public int reachedonerrors { get; set; }         public int sacrificeflies { get; set; }          public virtual season season { get; set; }         public virtual player player { get; set; }     } } 

in player.cs

using system; using system.collections.generic;  namespace theflyingpig.models {     public class player     {          public int id { get; set; }         public string firstname { get; set; }         public string lastname { get; set; }          public virtual icollection<stat> stats { get; set; }     } } 

in season.cs

using system; using system.collections.generic; using system.componentmodel.dataannotations.schema;  namespace theflyingpig.models {     public class season     {         public int seasonid { get; set; }         public string seasonname { get; set; }          public virtual icollection<stat> stats { get; set; }     } } 

in teamstat.cs (my viewmodel trying push data to)

//get season leaders controller public list<stat> seasonleadershits { get; set; } 

i have tried both of following options in controller (homecontroller.cs), can't seem make headway here top 5 hits leaders team.

var lastseasonid = db.seasons.orderbydescending(u => u.seasonid).select(u => u.seasonid).firstordefault();  var leadersatbats = (from s in db.stats                      join p in db.players on s.playerid equals p.id                      s.seasonid == lastseasonid                      group s.hits s.playerid g                      order s.hits descending                      select new { leaderid = g.key, leaderlist = g.tolist() })                      .take(5);  var results = db.stats.where(s => s.seasonid == lastseasonid).groupby(s => s.playerid, s => s.hits, (key, g) => new { playerid = key, hits = g.tolist() });  var view = new teamstat() { seasonleadershits = results }; return view(view); 

what reason 'groupby'? seems me need select top 5 rows db.stats ordering hits descending. this:

var results = (     s in db.stats     join p in db.players on s.playerid equals p.id     s.seasonid == lastseasonid     order s.hits descending     select new { player = p, stats = s })     .take(5).tolist(); 

** i'm assuming each row in stats represents year.

edit:

ok, each stat object represents single game (missed first time). if want stats totaled year, piece missing query sum() of hits (and other stats well). query give top 5 seasons ranked total number of hits, along season , player associated it.

var hitleaders = (from s in stats                   join p in players on s.playerid equals p.id                   group s new { s.playerid, s.seasonid } g                   select new                    {                       playerid = g.key.playerid,                       seasonid = g.key.seasonid,                       hits = g.sum(sum => sum.hits),                      atbats = g.sum(sum => sum.atbats),                      walks = g.sum(sum => sum.walks),                      singles = g.sum(sum => sum.singles),                      doubles = g.sum(sum => sum.doubles),                      triples = g.sum(sum => sum.triples),                      homeruns = g.sum(sum => sum.homeruns),                      rbis = g.sum(sum => sum.rbis),                      runs = g.sum(sum => sum.runs),                      reachedonerrors = g.sum(sum => sum.reachedonerrors),                      sacrificeflies = g.sum(sum => sum.sacrificeflies)                   }).orderbydescending(s => s.hits).take(5); 

now, second problem going have can't assign result of query view model because produces ienumerable<of anonymoustype> view model expecting list<stat>. suggest create object represents total of stats in year player; this:

public class totalstat {     public int totalstatid { get; set; }     public int seasonid { get; set; }     public int playerid { get; set; }     public int atbats { get; set; }     public int hits { get; set; }     public int walks { get; set; }     public int singles { get; set; }     public int doubles { get; set; }     public int triples { get; set; }     public int homeruns { get; set; }     public int rbis { get; set; }     public int runs { get; set; }     public int reachedonerrors { get; set; }     public int sacrificeflies { get; set; } } 

then, you'll need change view model to:

public list<totalstat> seasonleadershits { get; set; } 

and change query create ienumerable<totalstat>. achieve changing new {} new totalstat {}, so:

//... group s new { s.playerid, s.seasonid } g select new totalstat {      playerid = g.key.playerid,  //...