Using linq to get min and max of a particular field in one query

Question

Lets say you have a class like:

public class Section {
   public DateTime  StartDate;
   public DateTime? EndDate;
}

I have a list of these objects, and I would like to get the minimum start date and the maximum end date, but I would like to use one linq query so I know that I'm only iterating over the list once.

For instance, if I was doing this without linq, my code would look a bit like this (not checking for nulls):

DateTime? minStartDate;
DateTime? maxEndDate;
foreach(var s in sections) {
     if(s.StartDate < minStartDate) minStartDate = s.StartDate;
     if(s.EndDate > maxEndDate) maxEndDate = s.EndDate;
}

I could have two linq queries to get the min and max, but I know that under the covers, it would require iterating over all values twice.

I've seen min and max queries like this before, but with grouping. How would you do this without grouping, and in a single linq query?

maybe helpful http://stackoverflow.com/questions/914109/how-to-use-linq-to-select-object-with-minimum-or-maximum-property-value — kenny, Jun 24 '15 at 14:08
Are you looking for a single linq query that will return a collection of two dates - min and max - *and* ignore nulls? — Drew Kennedy, Jun 24 '15 at 15:30

score 1 · Answer 1 · answered Jun 24 '15 at 16:34

How would you do this without grouping, and in a single linq query?

If I had to do that, then I'd do:

var minMax = (from s0 in sections
  from s1 in sections
  orderby s0.StartDate, s1.EndDate descending
  select new {s0.StartDate, s1.EndDate}).FirstOrDefault();

But I'd also consider the performance impact depending on the provider in question.

On a database I'd expect this to become something like:

SELECT s0.StartDate, s1.EndDate
FROM Sections AS s0
CROSS JOIN Sections AS s1
ORDER BY created ASC, EndDate DESC 
LIMIT 1

OR

SELECT TOP 1 s0.StartDate, s1.EndDate
FROM Sections AS s0, Sections AS s1
ORDER BY created ASC, EndDate DESC

Depending on database type. How that in turn would be executed could well be two table scans, but if I was going to care about these dates I'd have indices on those columns so it should be two index look-scans toward the end of each index, so I'd expect it to be pretty fast.

I have a list of these objects

Then if I cared a lot about performance, I wouldn't use Linq.

but I would like to use one linq query so I know that I'm only iterating over the list once

That's why I wouldn't use linq. Since there's nothing in linq designed to deal with this particular case, it would hit the worse combination. Indeed it would be worse than 2 iterations, it would be N +1 iterations where N is the number of elements in Sections. Linq providers are good, but they aren't magic.

If I really wanted to be able to do this in Linq, as for example I was sometimes doing this against lists in memory and sometimes against databases and so on, I'd add my own methods to do each the best way possible:

public static Tuple<DateTime, DateTime?> MinStartMaxEnd(this IQueryable<Section> source)
{
  if(source == null)
    return null;
  var minMax = (from s0 in source
  from s1 in source
  orderby s0.StartDate, s1.EndDate descending
  select new {s0.StartDate, s1.EndDate}).FirstOrDefault();
  return minMax == null ? null : Tuple.Create(minMax.StartDate, minMax.EndDate);
}
public static Tuple<DateTime, DateTime?> MinStartMaxEnd(this IEnumerable<Section> source)
{
  if(source != null)
    using(var en = source.GetEnumerator())
      if(en.MoveNext())
      {
        var cur = en.Current;
        var start = cur.StartDate;
        var end = cur.EndDate;
        while(en.MoveNext())
        {
          cur = en.Current;
          if(cur.StartDate < start)
            start = cur.StartDate;
          if(cur.EndDate.HasValue && (!end.HasValue || cur.EndDate > end))
            end = cur.EndDate;
        }
        return Tuple.Create(start, end);
      }
  return null;
}

but I would like to use one linq query so I know that I'm only iterating over the list once

To come back to this. Linq does not promise to iterate over a list once. It can sometimes do so (or not iterate at all). It can call into database queries that in turn turn what is conceptually several iterations into one or two (common with CTEs). It can produce code that is very efficient for a variety of similar-but-not-quite-the-same queries where the alternative in hand-coding would be to either suffer a lot of waste or else to write reams of similar-but-not-quite-the-same methods.

But it can also hide some N+1 or N*N behaviour in what looks like a lot less if you assume Linq gives you a single pass. If you need particular single-pass behaviour, add to Linq; it's extensible.

The query in SQL wouldn't have to have two table references. It would simply be: SELECT min(start_date), max(end_date) FROM Sections — bpeikes, Jun 25 '15 at 18:38

score -2 · Answer 2 · answered Jun 24 '15 at 14:16

-2

You can use Min and Max:

List<Section> test = new List<Section>();

minStartDate = test.Min(o => o.StartDate);
maxEndDate = test.Max(o => o.EndDate);

answered Jun 24 '15 at 14:16

Thomas Ayoub

29,063
15
95
142

I explicitly stated that I wanted to do it in one linq query. Your code would require iterating over the data twice. – bpeikes Jun 25 '15 at 18:39

Using linq to get min and max of a particular field in one query

2 Answers2