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

User · Answer

maxAge   Persons Max c   gt  c age    or something along those lines

User · Answer

Maybe help  if you want to add some filter   context Persons  Where c   gt  c state    myState   Select c   gt  c age   DefaultIfEmpty 0   Max

User · Answer

int maxAge   context Persons Max p   gt  p Age     This version  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   gt  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   gt   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

User · Answer

In VB Net it would be   Dim maxAge As Integer   context Persons Max Function p  p Age

User · Answer

If the list is empty I get an exception  This solution will take into account this issue   int maxAge   context Persons Select p   gt  p Age  DefaultIfEmpty 0  Max

User · Answer

Try this int maxAge   context Persons Max p   gt  p Age    And make sure you have using System Linq  at the top of your file

User · Answer

As many said - this version  int maxAge   context Persons Max p   gt  p Age     throws an exception when table is empty   Use  int maxAge   context Persons Max x   gt   int  x Age     0    or  int maxAge   context Persons Select x   gt  x Age  DefaultIfEmpty 0  Max

User · Answer

Or you can try this    From p In context Persons Select p Order By age Descending  FirstOrDefault

User · Answer

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   gt  p Age     Select p   gt  p Age     FirstOrDefault

User · Answer

Your column is nullable  int maxAge   context Persons Select p   gt  p Age  Max      0    Your column is non-nullable  int maxAge   context Persons Select p   gt  p Age  Cast lt int  gt    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

[c#] How to get max value of a column using Entity Framework?

Examples related to c#

Examples related to sql

Examples related to entity-framework

Examples related to max