LINQ Using Max to select a single row

Question

I m using LINQ on an IQueryable returned from NHibernate and I need to select the row with the maximum value s  in a couple of fields    I ve simplified the bit that I m sticking on  I need to select the one row from my table with the maximum value in one field   var table   new Table   new Row id  1  status  10   new Row id  2  status  20     from u in table group u by 1 into g where u Status    g Max u   gt  u Status  select u   This is incorrect but I can t work out the right form   BTW  what I m actually trying to achieve is approximately this   var clientAddress   this repository GetAll        GroupBy a   gt  a       SelectMany              g   gt              g Where                  a   gt                  a Reference    clientReference  amp  amp                   a Status    ClientStatus Live  amp  amp                   a AddressReference    g Max x   gt  x AddressReference   amp  amp                   a StartDate    g Max x   gt  x StartDate         SingleOrDefault      I started with the above lambda but I ve been using LINQPad to try and work out the syntax for selecting the Max     UPDATE  Removing the GroupBy was key   var all   this repository GetAll     var address   all              Where                  a   gt                  a Reference    clientReference  amp  amp                   a Status    ClientStatus Live  amp  amp                   a StartDate    all Max x   gt  x StartDate   amp  amp                  a AddressReference    all Max x   gt  x AddressReference                SingleOrDefault

User · Answer

Addressing the first question  if you need to take several rows grouped by certain criteria with the other column with max value you can do something like this   var query       from u1 in table     join u2 in           from u in table         group u by u GroupId into g         select new   GroupId   g Key  MaxStatus   g Max x   gt  x Status          on new   u1 GroupId  u1 Status   equals new   u2 GroupId  Status   u2 MaxStatus      select u1

User · Answer

You can group by status and select a row from the largest group   table GroupBy r   gt  r Status  OrderByDescending g   gt  g Key  First   First      The first First   gets the first group  the set of rows with the largest status   the second First   gets the first row in that group  If the status is always unqiue  you can replace the second First   with Single

User · Answer

You can also do    from u in table orderby u Status descending select u  Take 1

User · Answer

I don t see why you are grouping here   Try this   var maxValue   table Max x   gt  x Status  var result   table First x   gt  x Status    maxValue     An alternate approach that would iterate table only once would be this   var result   table OrderByDescending x   gt  x Status  First      This is helpful if table is an IEnumerable lt T gt  that is not present in memory or that is calculated on the fly

User · Answer

More one example   Follow    qryAux    from q in qryAux where             q OrdSeq     from pp in Sessao Query lt NameTable gt    where pp FieldPk                q FieldPk select pp OrdSeq  Max   select q     Equals     select t     from nametable t  where t OrdSeq            select max t2 OrdSeq  from nametable t2 where t2 FieldPk  t FieldPk

User · Answer

Simply in one line   var result   table First x   gt  x Status    table Max y   gt  y Status      Notice that there are two action  the inner action is for finding the max value  the outer action is for get the desired object

[c#] LINQ Using Max() to select a single row

Examples related to c#

Examples related to .net

Examples related to linq