CODEWITHSUNDEEP
c#.netlinq
Boggin
Boggin

Reputation: 3416

LINQ Using Max() to select a single row

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 => 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 => a)
    .SelectMany(
            g =>
            g.Where(
                a =>
                a.Reference == clientReference && 
                a.Status == ClientStatus.Live && 
                a.AddressReference == g.Max(x => x.AddressReference) && 
                a.StartDate == g.Max(x => 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 =>
                a.Reference == clientReference && 
                a.Status == ClientStatus.Live && 
                a.StartDate == all.Max(x => x.StartDate) &&
                a.AddressReference == all.Max(x => x.AddressReference))
            .SingleOrDefault();

Upvotes: 125

Views: 381488

Answers (8)

dana
dana

Reputation: 18125

.NET 6 - MaxBy

The Enumerable.MaxBy and Queryable.MaxBy extension methods were added in .NET 6 and do exactly what is being asked here.

Row hasMaxStatus = table.MaxBy(r => r.Status);

Upvotes: 1

PavelP
PavelP

Reputation: 149

What about using Aggregate?

It's better than

  1. Select max
  2. Select by max value

since it only scans the array once.

var maxRow = table.Aggregate(
  (a, b) => a.Status > b.Status ? a : b  // whatever you need to compare
);

Upvotes: 4

SantanaFire
SantanaFire

Reputation: 101

More one example:

Follow:

 qryAux = (from q in qryAux where
            q.OrdSeq == (from pp in Sessao.Query<NameTable>() 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)

Upvotes: 0

Shneor
Shneor

Reputation: 444

Simply in one line:

var result = table.First(x => x.Status == table.Max(y => 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.

Upvotes: 0

Dmitry Komin
Dmitry Komin

Reputation: 559

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 => x.Status) }
    ) on new { u1.GroupId, u1.Status } equals new { u2.GroupId, Status = u2.MaxStatus}
    select u1;

Upvotes: 7

KAPIL SHARMA
KAPIL SHARMA

Reputation: 639

You can also do:

(from u in table
orderby u.Status descending
select u).Take(1);

Upvotes: 23

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

I don't see why you are grouping here.

Try this:

var maxValue = table.Max(x => x.Status)
var result = table.First(x => x.Status == maxValue);

An alternate approach that would iterate table only once would be this:

var result = table.OrderByDescending(x => x.Status).First();

This is helpful if table is an IEnumerable<T> that is not present in memory or that is calculated on the fly.

Upvotes: 281

SLaks
SLaks

Reputation: 887453

You can group by status and select a row from the largest group:

table.GroupBy(r => r.Status).OrderByDescending(g => 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().

Upvotes: 14

Related Questions