90

I have a table User which has an identity column UserID, now what is the correct Linq to Entity line of code that would return me the max UserID?

I've tried:

using (MyDBEntities db = new MyDBEntities())
{
    var User = db.Users.Last();
    // or
    var User = db.Users.Max();

    return user.UserID;
}

but Last and Max don't seem to be supported.

Any ideas?

moondaisy
  • 4,303
  • 6
  • 41
  • 70
Ray
  • 12,101
  • 27
  • 95
  • 137

7 Answers7

169

Do that like this

db.Users.OrderByDescending(u => u.UserId).FirstOrDefault();
ArunPratap
  • 4,816
  • 7
  • 25
  • 43
Jonas Kongslund
  • 5,058
  • 2
  • 28
  • 27
  • 4
    Does somebody know the performance of this solution compared to the Max(u => u.userId) solution? – Felix Keil Nov 19 '15 at 08:36
  • 1
    I'm not sure about it but IMHO Max is more performant than Order, since Max is O(n) and Sort implies some algorithm to sort, which is way bigger than O(n). – Luis Apr 11 '16 at 21:14
118

try this

int intIdt = db.Users.Max(u => u.UserId);

Update:

If no record then generate exception using above code try this

int? intIdt = db.Users.Max(u => (int?)u.UserId);
Rikin Patel
  • 8,848
  • 7
  • 70
  • 78
14
var max = db.Users.DefaultIfEmpty().Max(r => r == null ? 0 : r.ModelID);

when there are no records in db it would return 0 with no exception.

Leandro Bardelli
  • 10,561
  • 15
  • 79
  • 116
Vihana Kewalramani
  • 915
  • 11
  • 14
12

NisaPrieto

Users user = bd.Users.Where(u=> u.UserAge > 21).Max(u => u.UserID); 

will not work because MAX returns the same type of variable that the field is so in this case is an INT not an User object.

Leandro Bardelli
  • 10,561
  • 15
  • 79
  • 116
Minyie Diaz
  • 129
  • 1
  • 2
3

In case if you are using the async and await feature, it would be as follows:

User currentUser = await db.Users.OrderByDescending(u => u.UserId).FirstOrDefaultAsync();
Jason Sturges
  • 15,855
  • 14
  • 59
  • 80
Psi-Ed
  • 683
  • 1
  • 9
  • 22
0

Note that none of these answers will work if the key is a varchar since it is tempting to use MAX in a varchar column that is filled with "ints".

In a database if a column e.g. "Id" is in the database 1,2,3, 110, 112, 113, 4, 5, 6 Then all of the answers above will return 6.

So in your local database everything will work fine since while developing you will never get above 100 test records, then, at some moment during production you get a weird support ticket. Then after an hour you discover exactly this line "max" which seems to return the wrong key for some reason....

(and note that it says nowhere above that the key is INT...) (and if happens to end up in a generic library...)

So use:

Users.OrderByDescending(x=>x.Id.Length).ThenByDescending(a => a.Id).FirstOrDefault();

edelwater
  • 2,650
  • 8
  • 39
  • 67
-2

The best way to get the id of the entity you added is like this:

public int InsertEntity(Entity factor)
{
    Db.Entities.Add(factor);
    Db.SaveChanges();
    var id = factor.id;
    return id;
}
moondaisy
  • 4,303
  • 6
  • 41
  • 70