Sometimes, the policies of a given enterprise require the use of stored procedures for handling insert and update opeartions to the database. While we don't necessarily recommend this practice, this recipe shows how to ensure that Entity Framework complies with such a policy.
We will be using the NuGet
Package Manager to install the Entity Framework 4.1 assemblies.
The package installer can be found at http://nuget.org.
We will also be using a database for connecting to the data and updating it.
Open the Improving Updating Entities with Stored Procedures solution in the included source code examples.
TransactionTests
to the test project. We make a test that connects to the database, adds an object, and then retrieves an entity with a stored procedure, by using the following code:using System; using System.Linq; using BusinessLogic; using DataAccess; using DataAccess.Database; using DataAccess.Queries; using Microsoft.VisualStudio.TestTools.UnitTesting; using Test.Properties; namespace Test { [TestClass] public class StoredProcedureTests { [TestMethod] public void ShouldAllowEditsThroughUpsert() { //Arrange var init = new Initializer(); var context = new BlogContext(Settings.Default.BlogConnection); init.InitializeDatabase(context); context.Set<Blog>().Add(new Blog { CreationDate = DateTime.Today.AddDays(-1), Rating = 0, ShortDescription = "Dummy", Title = "Dummy" }); context.SaveChanges(); //Act var blog = context.Set<Blog>().FirstOrDefault(); blog.Title = "TestingSP"; context.SaveChanges(); //Assert Assert.IsTrue(context.Set<Blog>().Any(x => x.Title == "TestingSP")); } [TestMethod] public void ShouldAllowInsertsThroughUpsert() { //Arrange var init = new Initializer(); var context = new BlogContext(Settings.Default.BlogConnection); init.InitializeDatabase(context); //Act context.Set<Blog>().Add(new Blog() { CreationDate = DateTime.Now, Rating = 1.5, ShortDescription = "Testing", Title = "SPInsert" }); context.SaveChanges(); //Assert Assert.IsTrue(context.Set<Blog>().Any(x => x.Title == "SPInsert")); } } }
DataAccess
project in the Database
folder with the following code to set up the data:using System; using System.Data.Entity; using BusinessLogic; namespace DataAccess.Database { public class Initializer : DropCreateDatabaseAlways<BlogContext> { public Initializer() { } protected override void Seed(BlogContext context) { context.Database.ExecuteSqlCommand(StoredProcedureDefinitions.UpsertBlogDefinition); context.SaveChanges(); } } }
DataAccess
project, we add a new C# class named StoredProcedureDefinitions
with the following code, so we recreate our stored procedures with each database creation:namespace DataAccess.Database { public static class StoredProcedureDefinitions { public static string UpsertBlogDefinition = @"CREATE PROCEDURE UpsertBlog @Id int = 0, @CreationDate date, @Description text, @Title nvarchar(250), @Rating float AS BEGIN SET NOCOUNT ON; DECLARE @return_status int; IF EXISTS (SELECT * FROM BLOGS WHERE BlogId = @Id) BEGIN UPDATE [EFCookbook].[dbo].[Blogs] SET [Description] = @Description, [Title] = @Title, [Rating] = @Rating WHERE BlogId = @Id SET @return_status = @Id END Else BEGIN INSERT INTO [EFCookbook].[dbo].[Blogs] ([CreationDate] ,[Description] ,[Title] ,[Rating]) VALUES (@CreationDate, @Description, @Title, @Rating) SET @return_status = SCOPE_IDENTITY() END Select 'Return Status' = @return_status; END "; } }
BusinessLogic
project, add a new C# class named Blog
with the following code:using System; namespace BusinessLogic { public class Blog { public int Id { get; set; } public DateTime CreationDate { get; set; } public string ShortDescription { get; set; } public string Title { get; set; } public double Rating { get; set; } } }
Mapping
folder to the DataAccess
project, and add a BlogMapping
class to the folder with the following code:using System.ComponentModel.DataAnnotations; using System.Data.Entity.ModelConfiguration; using BusinessLogic; namespace DataAccess.Mappings { public class BlogMapping : EntityTypeConfiguration<Blog> { public BlogMapping() { this.ToTable("Blogs"); this.HasKey(x => x.Id); this.Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).HasColumnName("BlogId"); this.Property(x => x.Title).IsRequired().HasMaxLength(250); this.Property(x => x.Creationdate).HasColumnName("CreationDate").IsRequired(); this.Property(x => x.ShortDescription).HasColumnType("Text").IsMaxLength().IsOptional().HasColumnName("Description"); } } }
BlogContext
class to contain the new mappings and a DbSet
property for Blog
with the following code. Notice the override on the SaveChanges
utilizes the stored procedure upsert
:using System.Data; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Linq; using BusinessLogic; using DataAccess.Mappings; using DataAccess.Queries; namespace DataAccess { public class BlogContext : DbContext, IUnitOfWork { public BlogContext(string connectionString) : base(connectionString) { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new BlogMapping()); base.OnModelCreating(modelBuilder); } public IQueryable<T> Find<T>() where T : class { return this.Set<T>(); } public void Refresh() { this.ChangeTracker.Entries().ToList().ForEach(x=>x.Reload()); } public void Commit() { this.SaveChanges(); } public override int SaveChanges() { int storedProcChanges = 0; var changeSet = ChangeTracker.Entries<Blog>(); if (changeSet != null) { foreach (DbEntityEntry<Blog> entry in changeSet) { switch (entry.State) { case EntityState.Added: case EntityState.Modified: { var id = this.UpsertBlog(entry.Entity); entry.State = EntityState.Detached; if(id != -1) entry.Entity.Id = id; this.Set<Blog>().Attach(entry.Entity); storedProcChanges++; } break; } } } return base.SaveChanges() + storedProcChanges; } public DbContext Context { get { return this; } } public void Add<T>(T item) where T : class { this.Set<T>().Add(item); } } }
DataAccess
project, we add a new folder named Queries
with a new C# class named StoredProcedures
with the following code:using System.Data.Entity; using System.Data.SqlClient; using System.Linq; using BusinessLogic; using DataAccess.Database; namespace DataAccess.Queries { public static class StoredProcedures { private static string _upsertQuery = @" DECLARE @return_value int EXEC @return_value = [dbo].[UpsertBlog] @Id, @CreationDate, @Description, @Title, @Rating SELECT 'Return Value' = @return_value "; public static Blog GetBlog(this IBlogRepository repository, int blogId) { var items = repository.UnitOfWork.Context.Database.SqlQuery<Blog>(@"GetBlog @BlogId", new SqlParameter("BlogId",blogId) ); var blog = items.FirstOrDefault(); if (blog != null && repository.UnitOfWork.Context.Entry(blog) == null) repository.UnitOfWork.Context.Set<Blog>().Attach(blog); return blog; } public static int UpsertBlog(this DbContext context, Blog blog) { return context.Database.SqlQuery<int>(_upsertQuery,new object[] { new SqlParameter("Id",blog.Id), new SqlParameter("CreationDate",blog.CreationDate), new SqlParameter("Description",blog.ShortDescription), new SqlParameter("Title",blog.Title), new SqlParameter("Rating",blog.Rating) }).First(); } } }
We start-off our solution with a couple of tests that ensure we have achieved the goal, which, in this case, is to insert or update an entity without the use of generated SQL statements, but instead by using an update and insert stored procedure.
We move to setting up an initializer that will create the stored procedure for us every time our test database is dropped and recreated. We also provide a class to centralize these definitions in case of changes. This can load from .txt
or .sql
files just as easily. This ensures that the stored procedure will be there when we call it.
We then move to defining the blog and the blog mapping for our context. These will not be used by our context for SQL generation on update and insert, but will be used for selections and deletes. These two pieces can, over course, be changed as well, but would be overkill for this recipe.
The BlogContext
is where we are able to modify the save
behavior, by overriding the SaveChanges
method. We are able to pull all of the Blog
object state entries from the change tracker. This give us the ability to check for added or modified blog objects, execute our upsert
stored procedure, and then clear the object modified state, so that the generated SQL doesn't pick up those changes. Notice how we detach the objects before modifying the ID. The reason for that is, the context will return an error if you try to change the key of an attached object.
When we are forced to deal with stored procedures, we want to make sure that we avoid some serious runtime errors with the following suggestions and support:
We have to be very careful with this, as the runtime error chances increase drastically. The following are a few of the common error scenarios to avoid:
If we are planning to use the stored procedure updates for more than one object type, then we should abstract the query string and the parameter collection behind a factory, so that the SqlQuery<>
usage in the context can be as clean and generic as possible.
There are several open source libraries that make this easier to accomplish, here are a couple of them:
http://www.codeproject.com/KB/database/CodeFirstStoredProcedures.aspx