Stored procedures sometimes get a bad rap from developers, given their long history. But we need to remember that, often, they are the most effective way to coalesce complex queries. Just don't let them store business logic.
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 the data and updating it.
Open the Improving 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 a count with a stored procedure, by using the following code: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 ShouldAllowCallingStoredProcedureAndGettingResult() { //Arrange var init = new Initializer(); var context = new BlogContext(Settings.Default.BlogConnection); init.InitializeDatabase(context); var repo = new BlogRepository(context); //Act var count = repo.GetBlogCount(); //Assert Assert.AreEqual(1, count); } } }
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.GetBlogCountDefinition); context.Set<Blog>().Add(new Blog() { CreationDate = DateTime.Now, ShortDescription = "Testing", Title = "Test Blog" }); 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 GetBlogCountDefinition = @"CREATE PROCEDURE [dbo].[GetBlogCount]ASBEGINSET NOCOUNT ON;SELECT Count(*) FROM dbo.Blogs 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:using System; using System.Data.Entity; using System.Linq; using BusinessLogic; using DataAccess.Mappings; 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(); } } }
DataAccess
project, we add a new folder named Queries
with a new C# class named StoredProcedures
, with the following code:using System.Linq; namespace DataAccess.Queries { public static class StoredProcedures { public static int GetBlogCount(this IBlogRepository repository) { var items = repository.UnitOfWork.Context.Database.SqlQuery<int>(@"GetBlogCount"); var count = items.FirstOrDefault(); return count; } } }
We start-off our solution, as always, with a test that ensures that our features are properly implemented, and that we have demonstrated the functionality required.
In this example, for clarity alone, we have put the definition of the stored procedure into our code. This is not a requirement, and we could have easily let the stored procedure be defined in an embedded SQL file, or have it already exist in the database.
Once the stored procedure is created, we put an extension
method on the repository that allows us to invoke the SQL statement from the Database
object on the DbContext
. We have to drill through a couple of layers, but this is preferable to surfacing a raw DbContext
. We want that layered abstraction in this case.
Entity Framework takes the SQL query, executes it, and then tries to map the return
operation into the type that we have given it. In this case, we told it that the return
parameter is of the integer type.
When dealing with stored procedures in the Entity Framework code first model, we have to be aware of several things that could hamstring us, and cause runtime errors throughout our application.
If the type that is returned cannot be parsed into the type that it is expecting, we will get a runtime error. This error will bubble from the framework and will need to be guarded against at the point that we call the stored procedure.