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?

score 124 · Accepted Answer · answered Feb 18 '11 at 23:42

124

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)

answered Feb 18 '11 at 23:42

Ani

111,048
26
262
307

1

I would not recommend using the `DefaultIfEmpty`. Please look at my answer as to why: [link](http://stackoverflow.com/a/18428483/575471) – Nikkelmann Aug 25 '13 at 11:29
1

@Nikkelmann I believe this is a LINQ query of in-memory entities, without a connection to any database. – Ramon Snir Aug 25 '13 at 11:33
@RamonSnir if that is the case then either is fine :) But now ppl know not to use it for LinqToSql. – Nikkelmann Aug 25 '13 at 11:36
Or rather: Linq to Entities. – Nikkelmann Aug 25 '13 at 11:48
He could also use a nullable type, `pointList.Max(p => p == null ? (double?)null : p.X)` if he wanted. Note that with nullable types `Max` will not throw, not even with an empty sequence. – Jeppe Stig Nielsen Aug 25 '13 at 14:24

score 30 · Answer 2 · answered Feb 18 '11 at 23:44

30

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

answered Feb 18 '11 at 23:44

Mark Byers

811,555
193
1,581
1,452

1

Interesting approach, I like it better than mine because it avoids the conditional and the `DefaultIfEmpty` is applied on the *result* type. – Ani Feb 18 '11 at 23:50

score 14 · Answer 3 · answered Aug 25 '13 at 11:25

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

The problem with any ORM or tool that generates SQL for you is they make it so easy to shoot yourself in the foot. All you deal with is code and almost no one looks at the generated SQL. Problem with these mechanical translations is that they produce "One size fits all" code and sometimes, it is way bigger than what you may need. Thanks for highlighting that in this post. Bottom line, always check your generated queries! +1!! — Mrchief, Jan 05 '15 at 15:34
+1 for always check your generated queries! You can write bad SQL (so we profile it) but somehow the ORM always gets the blame when we don't profile (or even look at) the SQL it generates! — Dave, Sep 06 '16 at 12:50
I'm trying to use this method, but I have a different query syntax so I can't seem to get the lamda expression in the select to work. I am using `Convert.ToInt32( (from x in table join y in table 2 on x.ID equals y.ID where y.ProjectID == ProjectID select x.ObservationNum).Max());` Is there a way to do the joins with your syntax, or to modify my method to get to the nullable int cast? — Paul Gibson, Dec 28 '18 at 15:58
@paul-gibson From the top of my head, try ".Max(t => (int?)t.Sort)". — Nikkelmann, Dec 30 '18 at 00:40
Doh . . . and it's right there in your answer . . . thanks :) — Paul Gibson, Jan 02 '19 at 16:34

score 5 · Answer 4 · answered Sep 12 '17 at 17:11

5

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;

answered Sep 12 '17 at 17:11

Carter Medlin

11,857
5
62
68

score 3 · Answer 5 · answered Feb 18 '11 at 23:44

3

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

Should work.

answered Feb 18 '11 at 23:44

Robaticus

22,857
5
54
63

score 1 · Answer 6 · answered May 10 '21 at 19:41

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

score 0 · Answer 7 · edited May 23 '17 at 11:47

0

Try casting to nullable

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

more: Max or Default?

edited May 23 '17 at 11:47

Community

1
1

answered Aug 22 '13 at 13:05

Petr

2,603
2
17
9

score 0 · Answer 8 · answered Feb 03 '17 at 13:27

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.

score 0 · Answer 9 · answered Feb 16 '18 at 04:03

0

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

answered Feb 16 '18 at 04:03

Niraj Trivedi

2,370
22
24

LINQ Max() with Nulls

9 Answers9

Linked