Executing Method Against DataContext Returning Inserted ID

Question

Is there any way to use DataContext to execute some explicit SQL and return the auto-increment primary key value of the inserted row without using ExecuteMethodCall? All I want to do is insert some data into a table and get back the newly created primary key but without using LINQ (I use explicit SQL in my queries, just using LINQ to model the data).

Cheers

EDIT: Basically, I want to do this:

public int CreateSomething(Something somethingToCreate)
{
    string query = "MyFunkyQuery";
    this.ExecuteCommand(query);
    // return back the ID of the inserted value here!
}

SOLUTION

This one took a while. You have to pass a reference for the OUTPUT parameter in your sproc in your parameter list of the calling function like so:

[Parameter(Name = "InsertedContractID", DbType = "Int")] ref System.Nullable<int> insertedContractID

Then you have to do

insertedContractID = ((System.Nullable<int>)(result.GetParameterValue(16)));

once you've called it. Then you can use this outside of it:

public int? CreateContract(Contract contractToCreate)
{
   System.Nullable<int> insertedContractID = null; ref insertedContractID);
   return insertedContractID;
}

Take heavy note of GetParameterValue(16). It's indexed to whichever parameter it is in your parameter list (this isn't the full code, by the way).

Answer 1

You'll need to modify your insert statement to include a SELECT @@Identity (SQL Server) or similar at the end.

Answer 2

If you insist on using raw sql queries, then why not just use sprocs for your inserts? You could get the identity returned through an output parameters.

I'm not the greatest at SQL, but I broke out LinqPad and came up with this. It's a big hack in my opinion, but it works ... kinda.

DataContext.ExecuteQuery<T>() returns an IEnumerable<T> where T is a mapped linq entity. The extra select I added will only populate the YourPrimaryKey property.

public int CreateSomething(Something somethingToCreate)
{
// sub out your versions of YourLinqEntity & YourPrimaryKey
    string query = "MyFunkyQuery" + "select Convert(Int, SCOPE_IDENTITY()) as [YourPrimaryKey]";
    var result = this.ExecuteQuery<YourLinqEntity>(query);
    return result.First().YourPrimaryKey;
}

Answer 3

You can use something like this:

int newID = myDataContext.ExecuteQuery<int>(
    "INSERT INTO MyTable (Col1, Col2) VALUES ({0}, {1});
     SELECT Convert(Int, @@IDENTITY)",
     val1, val2).First();

The key is in converting @@IDENTITY in type int, like Ben sugested.