none
EF 6 - Code First and Insert/Update/Delete stored procs RRS feed

  • Question

  • Hi,

    I am trying to use EF 6 Code First with Create/Update/Delete stored procs.

    For obscure reasons, the Insert stored proc needs an extra parameter in addition to the standard set:

    • The insert stored procedure will have a parameter for every property, except for those marked as store generated (identity or computed). The stored procedure should return a result set with a column for each store generated property.

    Is there any way to configure the stored proc to take additional parameter(s) ?

    Thanks

    Friday, November 22, 2013 12:09 PM

Answers

  • Hi Fred,

    thanks for your response.

    I need the entity to be inserted/updated as part of the object graph attached to the context using the SaveChanges method.

    I have found a way to do this using the new DbCommandInterceptor functionality:

    http://msdn.microsoft.com/en-us/data/dn469464

    The only tricky part is identifying which entity is being inserted/updated from the Object State Manager, so that I can set the value for the extra parameter.

    Thanks again

    Monday, November 25, 2013 8:23 AM

All replies

  • Hello,

    >> Is there any way to configure the stored proc to take additional parameter(s)?

    If you want to add an additional parameter using the Fluent API, the answer is no. The Fluent API can just be mapped to existing properties.

    To achieve what you want, we just need to modify the created store procedure directly, add an additional parameter that we want like below:

    CREATE PROCEDURE [dbo].[Blog_Insert]
    
        @Name [nvarchar](max),
    
        @Url [nvarchar](max),
    
           @AdditionalParam [nvarchar](max) --<--ADD A Additional Parameter
    
    AS
    
    BEGIN
    
           INSERT [dbo].[Blogs]([Name], [Url])
    
        VALUES (@Name, @AdditionalParam)
    
        
    
        
    
        DECLARE @BlogId int
    
        SELECT @BlogId = [BlogId]
    
        FROM [dbo].[Blogs]
    
        WHERE @@ROWCOUNT > 0 AND [BlogId] = scope_identity()
    
        
    
        SELECT t0.[BlogId]
    
        FROM [dbo].[Blogs] AS t0
    
        WHERE @@ROWCOUNT > 0 AND t0.[BlogId] = @BlogId
    
    END
    

    It is same for us to call the store procedure like below:

    var Name = new SqlParameter { ParameterName = "Name", Value = "name001" };
    
                    var Url = new SqlParameter { ParameterName = "Url", Value = "url001" };
    
                    var AdditionalParam = new SqlParameter { ParameterName = "AdditionalParam", Value = "AdditionalParam001" };
    
    
                    var results = db.Database.ExecuteSqlCommand("Blog_Insert @Name, @Url,@AdditionalParam ", Name, Url, AdditionalParam); 
    

    My entity class and dbcontext are:

    public class Blog
    
        {
    
    
            public int BlogId { get; set; }
    
    
            public string Name { get; set; }
    
    
            public string Url { get; set; }
    
    
    }
    
    class BlogContext : DbContext
    
        {
    
            public DbSet<Blog> Blogs { get; set; }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
    
            {
    
    
                modelBuilder.Entity<Blog>().MapToStoredProcedures();
    
    
            }
    
        }
    

    You can have a try.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 25, 2013 2:30 AM
    Moderator
  • Hi Fred,

    thanks for your response.

    I need the entity to be inserted/updated as part of the object graph attached to the context using the SaveChanges method.

    I have found a way to do this using the new DbCommandInterceptor functionality:

    http://msdn.microsoft.com/en-us/data/dn469464

    The only tricky part is identifying which entity is being inserted/updated from the Object State Manager, so that I can set the value for the extra parameter.

    Thanks again

    Monday, November 25, 2013 8:23 AM