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.?
This question is related to
c#
sql
entity-framework
max
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]
maxAge = Persons.Max(c => c.age)
or something along those lines.
int maxAge = context.Persons.Max(p => p.Age);
This version, if the list is empty:
null
- for nullable overloadsSequence contains no element
exception - for non-nullable overloads-
int maxAge = context.Persons.Select(p => p.Age).DefaultIfEmpty(0).Max();
This version 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.
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();
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()
In VB.Net it would be
Dim maxAge As Integer = context.Persons.Max(Function(p) p.Age)
Maybe help, if you want to add some filter:
context.Persons
.Where(c => c.state == myState)
.Select(c => c.age)
.DefaultIfEmpty(0)
.Max();
Or you can try this:
(From p In context.Persons Select p Order By age Descending).FirstOrDefault
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();
Source: Stackoverflow.com