Get the highest ID number in a SQL table , LINQ , C#?

How can we get the highest ID number in an empty SQL table ?

e.g:

Assume we have a table with ten rows, but all of rows were deleted , now we wanna get the highest row's ID !!!

The below code throw an Exception because the tab开发者_如何学运维le is empty :

var maxID = db.TableNames.Select(q => q.Id).Max();

---

For SQL Server try the IDENT_CURRENT function.

Note that there can be gaps in identity value sequences because of failed transactions etc, so there is no guarantee that a row was ever committed with this value.

---

 IList<int> values = new List<int> { 1, 2, 3, 4, 5 };
 IList<int> emptyValues = new List<int> { };

 int max = values.DefaultIfEmpty(Int32.MinValue)
                                    .Max();

 // do not throw exception
 int maxEmpty = emptyValues.DefaultIfEmpty(Int32.MinValue)
                           .Max();

// max == 5
// maxEmpty == Int32.MinValue

EDIT:

My answer can help you get rid of exception in case of empty table but not the next auto increment value. You can follow Joe's answer and wrap IDENT_CURRENT function into a new stored procedure GetIdentity than call it using LINQ

var nextIdent = (from a in db.GetIdentity("TableName")) + 1;

---

If ID is an identity column, the following SQL will return what you're looking for. LINQ may not give you direct access to this function, in which case you'll need to run a simple DataReader.

USE DATABASENAME;
GO
DBCC CHECKIDENT ("TABLENAME", NORESEED);
GO

---

I don't know why you need to do that as it sound like you are trying to do something hacky Anyway if the table is empty why don't you reset the Identity counter(If the Integrity reference allows you to do that) :

ALTER TABLE MyTable ALTER COLUMN MyColumn INT IDENTITY (0, 1)

---

I don't know whether or not you are locked into your DB schema but this is a reason why people use GUIDs. You may consider looking into that.