LINQ Max() with Nulls

Question

I have a list that contains a bunch of Points (with an X and Y component).

I want to get the Max X for all points in the list, like this:

double max = pointList.Max(p=> p.X);

The problem is when I have a null in the list instead of a point. What would be the best way to get around this issue?

Answer 1

Well, you could just filter them out:

pointList.Where(p => p != null).Max(p => p.X)

On the other hand, if you want nulls to be treated as though they were points having X-coordinate 0 (or similar), you could do:

pointList.Max(p => p == null ? 0 : p.X)

Do note that both techniques will throw if the sequence is empty. One workaround for this (if desirable) would be:

pointList.DefaultIfEmpty().Max(p => p == null ? 0 : p.X)

Answer 2

If you want to provide a default value for X of a null point:

pointList.Max(p => p == null ? 0 : p.X)

Or to provide a default for an empty list:

int max = points.Where(p => p != null)
                .Select(p => p.X)
                .DefaultIfEmpty()
                .Max();

Answer 3

I would not recommend using the DefaultIfEmpty in this case, since it produces a rather large SQL compared to other alternatives.

Please look at this example:

We have a list of modules for a page and want to get the maximum value of the column "Sort". If the list has no records, then null is returned. DefaultIfEmpty checks for null and returns the default value of the column data type when the column is null.

var max = db.PageModules.Where(t => t.PageId == id).Select(t => t.Sort).DefaultIfEmpty().Max();

This produces the following SQL:

exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    MAX([Join1].[A1]) AS [A1]
    FROM ( SELECT 
        CASE WHEN ([Project1].[C1] IS NULL) THEN 0 ELSE [Project1].[Sort] END AS [A1]
        FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
        LEFT OUTER JOIN  (SELECT 
            [Extent1].[Sort] AS [Sort], 
            cast(1 as tinyint) AS [C1]
            FROM [dbo].[PageModules] AS [Extent1]
            WHERE [Extent1].[PageId] = @p__linq__0 ) AS [Project1] ON 1 = 1
    )  AS [Join1]
)  AS [GroupBy1]',N'@p__linq__0 int',@p__linq__0=11
go

If we instead cast the column to a nullable and let Convert.ToInt32() handle the null as so:

var max = Convert.ToInt32(db.PageModules.Where(t => t.PageId == id).Max(t => (int?)t.Sort));

Then we get the following SQL:

exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    MAX([Extent1].[Sort]) AS [A1]
    FROM [dbo].[PageModules] AS [Extent1]
    WHERE [Extent1].[PageId] = @p__linq__0
)  AS [GroupBy1]',N'@p__linq__0 int',@p__linq__0=11
go

I can really recommend using ExpressProfiler for checking the SQL that gets executed: http://expressprofiler.codeplex.com/

The last Linq expression can also be written as:

var max = Convert.ToInt32(db.PageModules.Where(t => t.PageId == id).Select(t => (int?)t.Sort).Max());

and will produce the same SQL but I like the more concise .Max(t => (int?)t.Sort).