Does LINQ have an equivilant of @@IDENTITY or SCOPE_IDENTITY()?

Question

Say that a page loads and the first thing I want to do is find the identity of the last record inserted into a table.

I'm not going to be inserting any records or anything, i just want to come in blind and find the last id inserted, can it be done using LINQ?

Answer 1

A loaded collection doesn't know anything about storage (which handles auto increment values). If you want to know the highest id in a loaded collection you can use an aggregate linq query and select max id.

Or you can use Linq-To-Sql to run a stored procedure and retrieve SCOPE_IDENTITY.

Answer 2

Have you tried BLToolkit. It supports LINQ as well as many other useful operations that are absent in LINQ2SQL: i.e DML with a human face :)

Regarding your question: BLToolkit contains InsertWithIdentity method

var value = db.Employee.InsertWithIdentity(() => new Northwind.Employee
{
    FirstName = "John",
    LastName  = "Shepard",
    Title     = "Spectre",
    HireDate  = Sql.CurrentTimestamp
});

or

var value =
    db
    .Into(db.Employee)
        .Value(e => e.FirstName, "John")
        .Value(e => e.LastName,  "Shepard")
        .Value(e => e.Title,     () => "Spectre")
        .Value(e => e.HireDate,  () => Sql.CurrentTimestamp)
    .InsertWithIdentity();

Answer 3

You could always execute a snippet of T-SQL from Linq to get the values:

SELECT 
    name, 
    OBJECT_NAME(OBJECT_ID) 'Table name',
    ISNULL(last_value, -1) 'Last Value'
FROM 
    sys.identity_columns 

and then grab the necessary values from that output.