How to get maximum column value using Entity Framework?

Question

To get the maximum value of a column containing an integer, I can use the following T-SQL command

SELECT MAX(expression ) FROM tables WHERE predicates; 

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

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 age?

 int maxAge = context.Persons.? 

Answer 1

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

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

Answer 2

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

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

Answer 3

Or you can try the following:

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

Answer 4

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

or something like that.

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

In VB.Net, it will be

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

Answer 7

As many have said, this is the version

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

throws an exception when the table is empty.

using

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

or

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

Answer 8

The selected answer throws an exception, and Carlos Toledo’s response applies filtering after retrieving all values ​​from the database.

The next starts one cycle and reads one value using any possible indexes without exception.

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

Answer 9

Your column is reset to zero

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

Your column is not nullified

 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 make a larger request on your server. For people who are interested, here is the equivalent of EF6:

Request without DefaultIfEmpty

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

Request 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]