LINQ + Find count of non-null values

Question

I have a table with the below structure.

ID VALUE
1 3.2
2 NULL
4 NULL
5 NULL
7 NULL
10 1.8
11 NULL
12 3.2
15 4.7
17 NULL
22 NULL
24 NULL
25 NULL
27 NULL
28 7

I would like to get the max count of consecutive null values in the table.

Any help would be greatly appreciated.

THanks Ashutosh

Answer 1

Is the table really ordered in a meaningful way?

I think to do it neatly you need a grouping operator that works differently from GroupBy, something like GroupConsecutive, so all separate groups are kept separate instead of being combined if they have the same key.

Unfortunately my VB is rusty-to-non-existent, but you might be able to mentally convert this:

public static class X
{
    private class Grouping<K, V> : List<V>
    {
        public K Key { get; set; }
    }

    public static IEnumerable<IGrouping<TKey, TValue>> GroupConsecutive(
        this IEnumerable<TSource> source,
        Func<TSource, TKey> keySelector)
    {
        Grouping current = null;

        foreach (var elem in source)
        {
            var key = keySelector(elem);
            if (current == null || !current.Key.Equals(key))
            {
                if (current != null)
                    yield return current;
                current = new Grouping { Key = key };
            }

            current.Add(elem);
        }

        if (current != null)
            yield return current;

    }
}

Now you can say:

table.GroupConsecutive(r => r.Value).Where(g => g.Key == null).Max(g => g.Count);

Here table is being treated as IEnumerable, so this all happens in memory.

You might be better off doing it in raw SQL if so.

Answer 2

You could always do this the hard way and simply loop over the collection once it has been returned.

Simple algorithm

for each item in collection
   if element null
      increment counter
   if element not null
      compare counter to existing max, update as necessary
      reset counter

display or otherwise use max

I love LINQ, but I'm not sure how it can be used here. As I say that, there's always someone who can come along and shut me right up by throwing out a one-liner method.

Answer 3

How about (C# I'm afraid):

var count = data.Aggregate(new { tmp = 0, max = 0 }, (current, item) => 
     item.Value == null ? new { tmp = current.tmp + 1, 
                                max = Math.Max(current.tmp + 1, current.max) }
                        : new { tmp = 0, current.max }).max;

In other words, we always keep the current count and the maximum value as we go along. It's pretty horrible, mind you. It seems to work with the sample data you've given though...

I'm not sure whether that would work with LINQ to SQL, mind you. Note that you'll need to specify an ordering before it even makes any sense - the database tables are "sets" with no notion of consecutive values.