LINQ Max() with Nulls

I have a list that contain开发者_JAVA技巧s 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?

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)

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();

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).

Place a nullable cast INSIDE the expression to ensure that a blank list will cast as a null. You can then add defaults.

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

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

Should work.

checking for null didn't work for me. I used DefaultIfEmpty()

   int max_sequence = _dbContext.myTable
                .Where(e=>e.field1==param.field1
                && e.fieldDate==param.fieldDate
                )
                .Select(e => e.Sequence)
                .DefaultIfEmpty()
                .Max();

Try casting to nullable

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

more: Max or Default?

Why not simply:

double? maxOrNull  = pointList.
    .Where(p => p != null)
        .OrderByDescending(p => p.x)
        .FirstOrDefault();
double max = 0;
if (maxOrNull.HasValue) max = maxOrNull.Value;

This will work with in memory lists and Linq2Sql, and probably efficiently also.

Nullable column with max is as follow

var maximum = objectEntity.where(entity => entity.property != null).max(entity => entity.property.HasValue);

Above statement return maximum number of entity property