Entity Framework: Return GUID from code-first stored procedure insert?

Question

I have an InsertPerson stored procedure and i need it to return a GUID (Uniqueidentifier) into my person object that is being created. Is this even possible with entity framework codefirst? I've tried everything and entity framework is ignoring the guid im trying to return using an output parameter. Does anyone have an example if this is possible?

Person.cs:

public class Person
{
   public Guid Id { get; set; }
   public string FirstName { get; set; }
}

Stored Procedure:

CREATE PROCEDURE [dbo].[InsertPerson]
    @KeyPlayerId UNIQUEIDENTIFIER ,
    @FirstNameNVARCHAR(255) 
AS

-- Perform Insert
insert into [dbo.].[Person]....

-- Return GUID
select @Id as [Id];

END;

Do you want to map this stored proc to the Create operation for this entity? If this is the case you would have to use EF6 which supports mapping CUD operation to stored procs in CodeFirst apps - http://msdn.microsoft.com/en-us/data/dn468673. — Pawel, Jan 03 '14 at 23:32

score 1 · Answer 1 · answered Feb 10 '14 at 19:36

I was able to accomplish it by telling Entity Framework that the Id is a Database Generated value. I mapped the Insert procedure to InsertPerson in the PersonMap class and use that when the model is created in the OnModelCreating method. In the stored procedure I generate a new Id and pass this back to Entity Framework. Hope this helps!

PersonMap.cs

public class PersonMap : EntityTypeConfiguration<Person>
{
    public PersonMap()
    {
        // Primary Key
        this.HasKey(t => t.Id);

        // Tell Entity Framework the database will generate the key.
        this.Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        // Properties
        this.Property(t => t.FirstName)
            .IsRequired()
            .HasMaxLength(255);

        //Map to Stored Procedure
        this.MapToStoredProcedures(s => s.Insert(i => i.HasName("InsertPerson")));
    }
}

OnModelCreating

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new PersonMap());            
    }

InsertPerson Stored Procedure

CREATE PROCEDURE InsertPerson 
-- Since Id is marked as Database Generated we only need 
-- a parameter for First Name
@FirstName nvarchar(255) = 0 
AS
-- Variable to hold new Id
DECLARE @Id uniqueidentifier

-- Generate a new Id using NEWID function that returns a unique identifier
SET @Id = NEWID()

-- Perform Insert
INSERT INTO [dbo].[Person] VALUES (@Id, @FirstName)

-- Return the Id to Entity Framework
SELECT @Id AS 'Id'

score 0 · Answer 2 · answered Jan 03 '14 at 21:20

0

I think this may help you:

var sqlConnection1 = new SqlConnection("Your Connection String");
var cmd = new SqlCommand();
var reader;

cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();
rdr = cmd.ExecuteReader();
var outputValue = Guid.Parse(cmd.Parameters["@Response"].Value.ToString());

sqlConnection1.Close();

answered Jan 03 '14 at 21:20

Guillermo Oramas R.

1,303
2
14
31

Sorry, I meant to find an answer using Entity framework code first, not pure ADO.NET – user2309367 Jan 03 '14 at 22:25
The you should see this post -> http://stackoverflow.com/questions/8180310/ef4-1-code-first-stored-procedure-with-output-parameter – Guillermo Oramas R. Jan 03 '14 at 22:40

Entity Framework: Return GUID from code-first stored procedure insert?

2 Answers2