Entity framework code first map stored procedures with composite primary keys

Question

I want to use EF code first to connect my model classes to my database, for example:

public partial class Header
{
    public int Id { get; set; }

    public int DeviceId { get; set; }
    ...
}

and in my DbContext

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Header>().HasKey(k => new { k.Id, k.DeviceId });
    modelBuilder.Entity<Header>().MapToStoredProcedures(s =>
    {
        s.Update(u => u.HasName("dbo.SpUpdateHeader"));
        s.Insert(i =>
            i.HasName("dbo.SpInsertHeader")
            .Result<int>(r => r.Id, "Id"));
        s.Delete(d => d.HasName("dbo.SpDeleteHeader"));
     });
}

The DeviceId is fixed to the device that the record is generated, and the Id is the identity generated on that device. This DbContext is used to sync all the devices data on the server.

The problem with this code is that on insert, i get this error: A result binding for the property 'Id' was not found on the modification function 'SpInsertHeader'. Ensure that the property is database generated.

And if i set [DatabaseGenerated(DatabaseGeneratedOption.Computed)] attribute or [DatabaseGenerated(DatabaseGeneratedOption.Identity)] that property is never sent to the stored procedure

What can i do to enable this scenario?

EDIT

PROCEDURE [dbo].[SpInsertHeader] 
  @Id INT,
  @DeviceId INT
AS
BEGIN
--get new id
IF(COALESCE(@Id, 0) = 0)
BEGIN
    SELECT TOP 1 @Id = COALESCE(h.Id, 0) + 1
    FROM Header h
    WHERE h.DeviceId = @DeviceId
    ORDER BY h.Id DESC;
END

IF(COALESCE(@Id, 0) = 0)
BEGIN
    SET @Id = 1;
END

INSERT INTO Header(Id, DeviceId) VALUES (@Id, @DeviceId);

SELECT @Id AS 'Id';
END

SpInsertHeader should return the generated identity value named as Id. — Gert Arnold
OK, so Id is not db-generated (you already said so, in retrospect...). I assume it's properly mapped as primary key? I think you shouldn't return anything from the sproc (but I'm not doing this very often). — Gert Arnold
But if i don't return anything i never get the Id if the record is generated on the server, and not on a device. — Frederico Regateiro
You can't mix db-generated and explicitly setting the key value. In this case, if you need the generated key value, you have to reread it manually after SaveChanges. — Gert Arnold

Frederico Regateiro Frederico Regateiro · Accepted Answer · 2016-06-23T02:05:20

After much testing and googling, i found this blog post with help me found a solution to my problem. I realize that i cannot get the database generated id, on the server created objects, and app generated id, for the diferent devices with the same DbContext.

So i need a way to have to 2 diferent model mappings, so i created a UseApiModel property on my DbContext and on the OnModelCreating method i set

modelBuilder.Entity<Header>().Property(t => t.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

and

s.Insert(i => i.HasName("dbo.SpInsertHeader").Result<int>(r => r.Id, "Id"));

or

modelBuilder.Entity<Header>().Property(t => t.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

and

s.Insert(i => i.HasName("dbo.SpApiInsertHeader"));

Depending on the UseApiModel property.

The Api SP expect a valid Id key and the other SP will generate one, i need two SP's because EF will send the Id property or not depending on the DatabaseGeneratedOption.

I also use the Colin Angus Mackay advise to generate the Id on a INSTEAD OF INSERT

Entity framework code first map stored procedures with composite primary keys

1 Answers