An upsert is an update-or-insert operation realized via a stored procedure. Typically, we check the parameters against some criteria, and if the criteria match, we update an existing record. Otherwise, we create a new one. One problem I encountered is getting the primary key (@@identity) of the record after it has been upserted. However, introducing a parameter (@Id) seems to do the trick. Here is an example:

Suppose I have a definition of an RSS channel as follows:

create table [dbo].[Channel] (
  [Id] int identity primary key not null,
  [Title] nvarchar(128) not null,
  [Description] nvarchar(128) not null,
  [Link] nvarchar(128) not null,
  [Docs] nvarchar(128) not null,
  [Generator] nvarchar(128) not null,
  [Language] nvarchar(8) not null);

My upsert operation would look as follows:

create procedure [dbo].[ChannelUpsert](
  @Id int output,
  @Title nvarchar(128),
  @Description nvarchar(128),
  @Link nvarchar(128),
  @Docs nvarchar(128),
  @Generator nvarchar(128),
  @Language nvarchar(8))
as
 begin
  merge Channel as tbl
   using (select
     @Id as Id,
     @Title as Title,
     @Description as Description,
     @Link as Link,
     @Docs as Docs,
     @Generator as Generator,
     @Language as Language) as row
   on
     tbl.Link = row.Link
when not matched then
  insert(Title,Description,Link,Docs,Generator,Language)
  values(row.Title,row.Description,row.Link,row.Docs,row.Generator,row.Language)
when matched then
 update set
  @Id = tbl.Id,
  tbl.Title = row.Title,
  tbl.Description = row.Description,
  tbl.Docs = row.Docs,
  tbl.Generator = row.Generator,
  tbl.Language = row.Language
;
if @Id is null
  set @Id = SCOPE_IDENTITY()
return @Id
end

The parameter @Id is part of the sproc, tagged with the output keyword, meaning that when calling from C#, you’ll need to initialize an int? parameter and pass it by reference. Subsequently, we do the upsert, and if an update happens, we simply set the @Id to whatever table we just updated.

If towards the end the @Id is null, this meanins we did an insert – so we just assign @Id to the identity value of the inserted record. We also return it from the sproc, which is somewhat unnecessary.

Here is the actual usage code:

int? id = null;
ctx.ChannelUpsert(
  ref id,
  getData(elem, "title"),
  getData(elem, "description"),
  getData(elem, "link"),
  getData(elem, "docs"),
  getData(elem, "generator"),
  getData(elem, "language"));

Now that we have the id, we can use it as a foreign key in child upsert operations.

Side note: it turns out that it’s impossible to call custom sprocs (such as this one) automatically through the Entity Framework. How depressing. Well, at least Linq2SQL works.