In previous versions of EF it was only possible to map Code First entities directly to tables. It is fairly easy to use SqlQuery to select data using stored procedures but there was no feasible way to use stored procedures for insert, update and delete.

Limitations

  • Stored procedure mapping can currently only be done with the Fluent API. In the future we may look at an attribute based (Data Annotation) alternative.
  • You cannot use a mixture of stored procedures and direct table access for a given entity (or entity hierarchy). The insert, update and delete operations must all use direct table access or stored procedures.

Default Code First Conventions

This feature has no impact on the default Code First Conventions. We will always map directly to tables by default.

Existing databases, new database , and migrations

When mapping to an existing database that was not created by Code First, EF will ‘trust you’ that the shape of the database matches the shape of your model that was discovered/configured. This is no different for stored procedures, EF will try to access the stored procedures using the shape defined in your model. The Fluent API allows you to configure the shape of your stored procedures to match your database.

When Code First automatically creates a database at runtime, if stored procedures are included in the model they will be created in the database.

Code First Migrations is now capable of creating stored procedures. When you configure a model to use stored procedures, Add-Migration will scaffold code to create the required stored procedures. Migrations is also capable of altering stored procedures when configuration is changed. 

Basic Entity Mapping

You can opt into using stored procedures for insert, update and delete using the Fluent API.

modelBuilder
  .Entity<Blog>()
  .MapToStoredProcedures();

 

Doing this will cause Code First to use some conventions to build the expected shape of the stored procedures in the database.

  • Three stored procedures named <type_name>_Insert, <type_name>_Update and <type_name>_Delete (e.g. Blog_Insert, Blog_Update and Blog_Delete).
  • Parameter names correspond to the property names.
    Note: If you use HasColumnName() or the Column attribute to rename the column for a given property then this name is used for parameters instead of the property name.
  • 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.
  • The update stored procedure will have a parameter for every property, except for those marked as computed. The stored procedure should return a result set with a column for each computed property.
  • The delete stored procedure should have a parameter for the key value of the entity (or multiple parameters if the entity has a composite key). Additionally, the delete procedure should also have parameters for any independent association foreign keys on the target table (relationships that do not have corresponding foreign key properties declared in the entity).

Using the following class as an example:

public class Blog 
{ 
  public int BlogId { get; set; } 
  public string Name { get; set; } 
  public string Url { get; set; } 
}

 

The default stored procedures would be:

CREATE PROCEDURE [dbo].[Blog_Insert] 
  @Name nvarchar(max), 
  @Url nvarchar(max) 
AS 
BEGIN
  INSERT INTO [dbo].[Blogs] ([Name], [Url])
  VALUES (@Name, @Url)

  SELECT SCOPE_IDENTITY() AS BlogId
END 

 

CREATE PROCEDURE [dbo].[Blog_Update] 
  @BlogId int, 
  @Name nvarchar(max), 
  @Url nvarchar(max) 
AS 
  UPDATE [dbo].[Blogs]
SET [Name] = @Name, [Url] = @Url WHERE BlogId = @BlogId;

 

CREATE PROCEDURE [dbo].[Blog_Delete] 
  @BlogId int 
AS 
  DELETE FROM [dbo].[Blogs]
  WHERE BlogId = @BlogId

Overriding the Defaults

You can override part or all of what was configured by default.

You can change the name of one or more stored procedures. This example renames the update stored procedure only.

modelBuilder 
  .Entity<Blog>() 
  .MapToStoredProcedures(s => 
    s.Update(u => u.HasName("modify_blog"))); 

 

This example renames all three stored procedures.

modelBuilder 
  .Entity<Blog>() 
  .MapToStoredProcedures(s => 
    s.Update(u => u.HasName("modify_blog")) 
     .Delete(d => d.HasName("delete_blog")) 
     .Insert(i => i.HasName("insert_blog"))); 

 

In these examples the calls are chained together, but you can also use lambda block syntax.

modelBuilder 
  .Entity<Blog>() 
  .MapToStoredProcedures(s => 
    { 
      s.Update(u => u.HasName("modify_blog")); 
      s.Delete(d => d.HasName("delete_blog")); 
      s.Insert(i => i.HasName("insert_blog")); 
    }); 

 

This example renames the parameter for the BlogId property on the update stored procedure.

modelBuilder 
  .Entity<Blog>() 
  .MapToStoredProcedures(s => 
    s.Update(u => u.Parameter(b => b.BlogId, "blog_id"))); 

 

These calls are all chainable and composable. Here is an example that renames all three stored procedures and their parameters.

modelBuilder 
  .Entity<Blog>() 
  .MapToStoredProcedures(s => 
    s.Update(u => u.HasName("modify_blog") 
                   .Parameter(b => b.BlogId, "blog_id") 
                   .Parameter(b => b.Name, "blog_name") 
                   .Parameter(b => b.Url, "blog_url")) 
     .Delete(d => d.HasName("delete_blog") 
                   .Parameter(b => b.BlogId, "blog_id")) 
     .Insert(i => i.HasName("insert_blog") 
                   .Parameter(b => b.Name, "blog_name") 
                   .Parameter(b => b.Url, "blog_url"))); 

 

You can also change the name of the columns in the result set that contains database generated values.

modelBuilder
  .Entity<Blog>()
  .MapToStoredProcedures(s =>
    s.Insert(i => i.Result(b => b.BlogId, "generated_blog_identity")));

 

CREATE PROCEDURE [dbo].[Blog_Insert] 
  @Name nvarchar(max), 
  @Url nvarchar(max) 
AS 
BEGIN
  INSERT INTO [dbo].[Blogs] ([Name], [Url])
  VALUES (@Name, @Url)

  SELECT SCOPE_IDENTITY() AS generated_blog_id
END 

 

Relationships Without a Foreign Key in the Class

When a foreign key property is included in the class definition, the corresponding parameter can be renamed in the same way as any other property. When a relationship exists without a foreign key property in the class, the default parameter name is <navigation_property_name>_<primary_key_name>.

For example, the following class definitions would result in a Blog_BlogId parameter being expected in the stored procedures to insert and update Posts.

public class Blog 
{ 
  public int BlogId { get; set; } 
  public string Name { get; set; } 
  public string Url { get; set; }

  public List<Post> Posts { get; set; } 
} 

public class Post 
{ 
  public int PostId { get; set; } 
  public string Title { get; set; } 
  public string Content { get; set; } 

  public Blog Blog { get; set; } 
} 

Overriding the Defaults

You can change parameters for foreign keys that are not included in the class by supplying the path to the primary key property to the Parameter method.

modelBuilder
.Entity<Post>() .MapToStoredProcedures(s => s.Insert(i => i.Parameter(p => p.Blog.BlogId, "blog_id")));

 

If you don’t have a navigation property on the dependent entity (i.e no Post.Blog property) then you can use the Association method to identify the other end of the relationship and then configure the parameters that correspond to each of the key property(s).

modelBuilder
.Entity<Post>() .MapToStoredProcedures(s => s.Insert(i => i.Association<Blog>( b => b.Posts, c => c.Parameter(b => b.BlogId, "blog_id"))));

Concurrency Tokens

Update and delete stored procedures may also need to deal with concurrency:

  • If the entity contains concurrency tokens, the stored procedure can optionally have an output parameter that returns the number of rows updated/deleted (rows affected). Such a parameter must be configured using the RowsAffectedParameter method.
    By default EF uses the return value from ExecuteNonQuery to determine how many rows were affected. Specifying a rows affected output parameter is useful if you perform any logic in your sproc that would result in the @@RowCount variable being incorrect (from EF's perspective) at the end of execution.
  • For each concurrency token there will be a parameter named <property_name>_Original (i.e. Timestamp_Original). This will be passed the original value of this property – the value when queried from the database.
    • Concurrency tokens that are computed by the database – such as timestamps – will only have an original value parameter.
    • Non-computed properties that are set as concurrency tokens will also have a parameter for the new value in the update procedure. This uses the naming conventions already discussed for new values. An example of such a token would be using a Blog's URL as a concurrency token, the new value is required because this can be updated to a new value by your code (unlike a Timestamp token which is only updated by the database).

This is an example class and update stored procedure with a timestamp concurrency token.

public class Blog 
{ 
  public int BlogId { get; set; } 
  public string Name { get; set; } 
  public string Url { get; set; } 
  [Timestamp]
  public byte[] Timestamp { get; set; }
}

 

CREATE PROCEDURE [dbo].[Blog_Update] 
  @BlogId int, 
  @Name nvarchar(max), 
  @Url nvarchar(max),
  @Timestamp_Original rowversion 
AS 
  UPDATE [dbo].[Blogs]
  SET [Name] = @Name, [Url] = @Url    
  WHERE BlogId = @BlogId AND [Timestamp] = @Timestamp_Original

 

Here is an example class and update stored procedure with non-computed concurrency token.

public class Blog 
{ 
  public int BlogId { get; set; } 
  public string Name { get; set; } 
  [ConcurrencyCheck]
  public string Url { get; set; } 
}

 

CREATE PROCEDURE [dbo].[Blog_Update] 
  @BlogId int, 
  @Name nvarchar(max), 
  @Url nvarchar(max),
  @Url_Original nvarchar(max),
AS 
  UPDATE [dbo].[Blogs]
  SET [Name] = @Name, [Url] = @Url    
  WHERE BlogId = @BlogId AND [Url] = @Url_Original

 

Overriding the Defaults

You can optionally introduce a rows affected parameter.

modelBuilder 
  .Entity<Blog>() 
  .MapToStoredProcedures(s => 
    s.Update(u => u.RowsAffectedParameter("rows_affected"))); 

 

For database computed concurrency tokens – where only the original value is passed – you can just use the standard parameter renaming mechanism to rename the parameter for the original value.

modelBuilder 
  .Entity<Blog>() 
  .MapToStoredProcedures(s => 
    s.Update(u => u.Parameter(b => b.Timestamp, "blog_timestamp"))); 

 

For non-computed concurrency tokens – where both the original and new value are passed – you can use an overload of Parameter that allows you to supply a name for each parameter.

modelBuilder
.Entity<Blog>()
.MapToStoredProcedures(s => s.Update(u => u.Parameter(b => b.Url, "blog_url", "blog_original_url")));  

Many to Many Relationships

We’ll use the following classes as an example in this section.

public class Post 
{ 
  public int PostId { get; set; } 
  public string Title { get; set; } 
  public string Content { get; set; } 

  public List<Tag> Tags { get; set; } 
} 

public class Tag 
{ 
  public int TagId { get; set; } 
  public string TagName { get; set; } 

  public List<Post> Posts { get; set; } 
} 

Many to many relationships can be mapped to stored procedures with the following syntax.

modelBuilder 
  .Entity<Post>() 
  .HasMany(p => p.Tags) 
  .WithMany(t => t.Posts) 
  .MapToStoredProcedures(); 

If no other configuration is supplied then the following stored procedure shape is used by default.

  • Two stored procedures named <type_one><type_two>_Insert and <type_one><type_two>_Delete (i.e. PostTag_Insert and PostTag_Delete).
  • The parameters will be the key value(s) for each type. The name of each parameter being <type_name>_<property_name> (i.e. Post_PostId and Tag_TagId).

Here are example insert and update stored procedures.

CREATE PROCEDURE [dbo].[PostTag_Insert] 
  @Post_PostId int, 
  @Tag_TagId int 
AS 
  INSERT INTO [dbo].[Post_Tags] (Post_PostId, Tag_TagId)  
  VALUES (@Post_PostId, @Tag_TagId)  

 

CREATE PROCEDURE [dbo].[PostTag_Delete] 
  @Post_PostId int, 
  @Tag_TagId int 
AS 
  DELETE FROM [dbo].[Post_Tags]   
  WHERE Post_PostId = @Post_PostId AND Tag_TagId = @Tag_TagId

Overriding the Defaults

The procedure and parameter names can be configured in a similar way to entity stored procedures.

modelBuilder 
  .Entity<Post>() 
  .HasMany(p => p.Tags) 
  .WithMany(t => t.Posts) 
  .MapToStoredProcedures(s => 
    s.Insert(i => i.HasName("add_post_tag") 
                   .LeftKeyParameter(p => p.PostId, "post_id") 
                   .RightKeyParameter(t => t.TagId, "tag_id")) 
     .Delete(d => d.HasName("remove_post_tag") 
                   .LeftKeyParameter(p => p.PostId, "post_id") 
                   .RightKeyParameter(t => t.TagId, "tag_id")));

Last edited Sep 12, 2013 at 9:21 PM by RoMiller, version 34

Comments

tymberwyld Jan 10 at 4:14 AM 
I really need the ability to have something similar to a "ParameterNotMapped(x => x.PropertyName)". I have certain use-cases where it's essential that certain properties are not sent to Update/Insert procs. This is NOT the same as the "NotMapped" attribute because I do need these values in the properties when "reading" from the tables. So, for now I am stuck adding Parameters to my procs which just get ignored.

RoMiller Sep 11, 2013 at 8:47 PM 
@Pakorasu - If you point Code First to an existing database it will just 'trust you' that the database is correct and try and map to it. If Code First is responsible for creating the database then it will ensure it's created to match the model. This applies to all database objects (tables etc.) and not just stored procedures. Code First migrations supports creating stored procedures and can be used to add them to a database you previously created using Code First.

RoMiller Sep 11, 2013 at 8:45 PM 
@taspeotis - That was probably just a typo in an early version of this spec. By default EF does use nvarchar, but you can always change it to varchar.

Pakorasu Aug 2, 2013 at 1:01 AM 
Hello.
I found what was wrong about my previous comment. The stored procedures are not created if the DB already exists, the stored procedures are created only if the DB is a new DB created by EF and if its initial configuration has MapToStoredProcedures feature enabled, is that a possible bug?.

By the way, I'm working in a custom DB Provider and I want to add this feature but there is no a class that I can use for override to implement it. Are you planning to add it later or not add it at all?

Thanks for your time.

Pakorasu Jul 18, 2013 at 9:24 PM 
I tried to use this functionality, but I received the error message "Could not find the stored procedure 'Blog_Insert'", I review my code to verify if there are any errors but not.
So I just take the example code to create the Stored Procedure in the DB, then execute the code and the app works fine.
I supposed that EF will create the stored procedures, not that I first must create the stored procedures before use this feature, it will be good if you add that to the post.

Saludos

lazyspider Mar 27, 2013 at 9:18 AM 
@BriceLambson, well , thanks a lot, I'll keep an eye on it~

BriceLambson Mar 26, 2013 at 9:42 PM 
@lazyspider, Generating the stored procedures isn't implemented yet (only mapping to them is). Keep an eye on Work Item 948; it is actively being worked on.

lazyspider Mar 26, 2013 at 9:25 AM 
I can't find the generated stored-procedure, please don't tell me it located in system stored procedures. i chose the default way...MapToStoredProcedures();

BriceLambson Mar 11, 2013 at 5:44 PM 
@taspeotis, the use of varchar in this spec was merely an oversight. I've updated them to nvarchar to match the default database type EF uses for string properties.

AndrewPeters Mar 8, 2013 at 11:12 PM 
@taspeotis, The function parameters we generate should definitely match what we generate for the corresponding columns. If you are seeing something different, please file a bug, thanks!

AndrewPeters Mar 8, 2013 at 11:11 PM 
@wrlucas, this particular feature is limited to modification function mapping (CUD). What you describe is known as function imports and is something we do want to try and get to for EF6.

wrlucas Mar 8, 2013 at 10:42 PM 
Why is the API limited to Update, Delete and Insert stored procedures? I need support for Select stored procedures. I create drop downs that are a subset of the table which are created by joining other tables and setting the where clause. I don't want to have to create a view for every variation of drop down I create. I certainly to do not what to have to customize the drop down source on the client.

taspeotis Feb 28, 2013 at 11:23 AM 
Why are the default stored procedures using VARCHAR(MAX) instead of NVARCHAR(MAX)? I thought EF only generated NVARCHAR-based stuff.