How to get the last inserted id in a Linq To Sql custom sql expression ?

Question

Hello,

Here is my problem. I'd like to get the last inserted Id with a custom sql expression in Linq To Sql.

My insert method:

public int Add(string Label)
{
    _dbContext.ExecuteCommand("INSERT INTO Products (Label) VALUES (@Label);", Label);

    _dbContext.SubmitChanges();

    var lastId = _dbContext.ExecuteQuery<int>("SELECT Scope_Identity() as [Scope_Identity];").ToList()[0];

    return  lastId;
}

lastId always returns null. When I tried this query (Insert + Select) directly in Sql Server, it works perfectly and returns the last inserted Id. I don't want to use a procedure and I can't use a new Product object (it is not possible for me to use InsertOnSubmit or whatever).

Can you please help me ? Thanks.

Answer 1

Try using:

INSERT INTO Products (Label) OUTPUT inserted.ProductID VALUES (@Label);

Rob

Answer 2

Hello,

Ok I've found how to do it:

public int Add(string Label)
{
   var query = String.Format("INSERT INTO Products (Label) VALUES (@Label); SELECT ProductId FROM Products WHERE ProductId = Scope_Identity();", Label);

   var lastId = _dbContext.ExecuteQuery<int>(query).ToList()[0];

   return  lastId;
}