LINQ Max() with Nulls
C#LinqC# Problem Overview
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?
C# Solutions
Solution 1 - C#
Well, you could just filter them out:
pointList.Where(p => p != null).Max(p => p.X)
On the other hand, if you want null
s 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)
Solution 2 - C#
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();
Solution 3 - C#
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)
.
Solution 4 - C#
double max = pointList.Where(p=>p != null).Max(p=>p.X)
Should work.
Solution 5 - C#
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;
Solution 6 - C#
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();
Solution 7 - C#
Try casting to nullable
double max = (double?)pointList.Max(p => p.X);
more: https://stackoverflow.com/questions/341264/linq-max-or-default
Solution 8 - C#
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.
Solution 9 - C#
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