How to get max value of a column using Entity Framework?

Question

To get maximum value of a column that contains integer, I can use the following T-SQL comand

SELECT MAX(expression )
FROM tables
WHERE predicates;

Is it possible to obtain the same result with Entity Framework.

Let's say I have the following model

public class Person
{
  public int PersonID { get; set; }
  public int Name { get; set; }
  public int Age { get; set; }
}

How do I get the oldest person's age?

int maxAge = context.Persons.?

Answer 1

Try this int maxAge = context.Persons.Max(p => p.Age);

And make sure you have using System.Linq; at the top of your file

Answer 2

If the list is empty I get an exception. This solution will take into account this issue:

int maxAge = context.Persons.Select(p => p.Age).DefaultIfEmpty(0).Max();

Answer 3

int maxAge = context.Persons.Max(p => p.Age); 

The version above, if the list is empty:

• Returns null ― for nullable overloads
• Throws Sequence contains no element exception ― for non-nullable overloads

_

int maxAge = context.Persons.Select(p => p.Age).DefaultIfEmpty(0).Max(); 

The version above handles the empty list case, but it generates more complex query, and for some reason doesn't work with EF Core.

_

int maxAge = context.Persons.Max(p => (int?)p.Age) ?? 0; 

This version is elegant and performant (simple query and single round-trip to the database), works with EF Core. It handles the mentioned exception above by casting the non-nullable type to nullable and then applying the default value using the ?? operator.

Answer 4

Or you can try this:

(From p In context.Persons Select p Order By age Descending).FirstOrDefault

Answer 5

Maybe help, if you want to add some filter:

context.Persons
.Where(c => c.state == myState)
.Select(c => c.age)
.DefaultIfEmpty(0)
.Max();

Answer 6

Your column is nullable

int maxAge = context.Persons.Select(p => p.Age).Max() ?? 0;

Your column is non-nullable

int maxAge = context.Persons.Select(p => p.Age).Cast<int?>().Max() ?? 0;

In both cases, you can use the second code. If you use DefaultIfEmpty, you will do a bigger query on your server. For people who are interested, here are the EF6 equivalent:

Query without DefaultIfEmpty

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        MAX([Extent1].[Age]) AS [A1]
        FROM [dbo].[Persons] AS [Extent1]
    )  AS [GroupBy1]

Query with DefaultIfEmpty

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        MAX([Join1].[A1]) AS [A1]
        FROM ( SELECT 
            CASE WHEN ([Project1].[C1] IS NULL) THEN 0 ELSE [Project1].[Age] END AS [A1]
            FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
            LEFT OUTER JOIN  (SELECT 
                [Extent1].[Age] AS [Age], 
                cast(1 as tinyint) AS [C1]
                FROM [dbo].[Persons] AS [Extent1]) AS [Project1] ON 1 = 1
        )  AS [Join1]
    )  AS [GroupBy1]

Answer 7

Selected answer throws exceptions, and the answer from Carlos Toledo applies filtering after retrieving all values from the database.

The following one runs a single round-trip and reads a single value, using any possible indexes, without an exception.

int maxAge = _dbContext.Persons
  .OrderByDescending(p => p.Age)
  .Select(p => p.Age)
  .FirstOrDefault();

Answer 8

maxAge = Persons.Max(c => c.age)

or something along those lines.

Answer 9

As many said - this version

int maxAge = context.Persons.Max(p => p.Age);

throws an exception when table is empty.

Use

int maxAge = context.Persons.Max(x => (int?)x.Age) ?? 0;

or

int maxAge = context.Persons.Select(x => x.Age).DefaultIfEmpty(0).Max()

Answer 10

In VB.Net it would be

Dim maxAge As Integer = context.Persons.Max(Function(p) p.Age)