Retrieving entities with stored procedures

Sometimes, we need a stored procedure to not only return data, but also to return actual and change-tracked entities. To make certain that happens, we need to do just a little bit more.

Getting ready

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 Entities with Stored Procedures solution in the included source code examples.

How to do it...

  1. We start by adding a new unit test named 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 DataAccess;
using DataAccess.Database;
using DataAccess.Queries;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Test.Properties;
namespace Test
{
  [TestClass]
  public class StoredProcedureTests
  {
    [TestMethod]
    public void ShouldAllowReturnOfATrackedEntityFromStoredProcedure()
    {
      //Arrange
      var init = new Initializer();
      var context = new BlogContext(Settings.Default.BlogConnection);
      init.InitializeDatabase(context);
      var repo = new BlogRepository(context);

      //Act
      var blog = repo.GetBlog(1);

      //Assert
      Assert.AreEqual(1, blog.Id);
    }
  }

}
  2. Add an initializer to the 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.GetBlogDefinition);
      context.Set<Blog>().Add(new Blog()
      {
        CreationDate = DateTime.Now,
        ShortDescription = "Testing",
        Title = "Test Blog"
      });
      context.SaveChanges();
    }
  }
}
  3. In the DataAccess project, we add a new C# class named StoredProcedureDefinitions with the following code, so we can recreate our stored procedures with each database creation:
    namespace DataAccess.Database
{
  public static class StoredProcedureDefinitions
  {
    public static string GetBlogDefinition = @"CREATE PROCEDURE [dbo].[GetBlog]
      @BlogId int
      AS
      BEGIN
        SET NOCOUNT ON;
        SELECT b.BlogId as Id, b.Description as ShortDescription, b.Title, b.Rating, b.CreationDate  FROM dbo.Blogs bEND
      ";
  }
}
  4. In the 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; }
  }
}
  5. Add a 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");
    }

  }
}
  6. Modify the 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();
    }
  }
}
  7. In the 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;

namespace DataAccess.Queries
{
  public static class StoredProcedures
  {
    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;
    }
  }
}
  8. Run our test and see how it works.

How it works...

We start, as always, with a test that calls our stored procedure and returns a tracked entity, so we can ensure that we have accomplished the goal that we set forth.

We also make sure that the stored procedure is returning columns that match the names of the properties that we have in our entity. This is because there is no mapping between the stored procedure's return operation and the entity instantiation. This lack of mapping causes the framework to use reflection to try and directly correlate columns to properties, and we will need to make sure that correlation can happen seamlessly.

The stored procedure is called from the repository, but notice that it drills down to fire on the Database object on DbContext. This allows us to map anything from a simple integer return to an object, which are not tracked by default. So, we have to manually attach the object to the context. This will allow us to leverage stored procedures to load objects, but still have generated change statements and tracking on the object.

There's more...

Loading the entity from a stored procedure is complex in the conventions that it relies on, and some runtime errors can occur if we do not prepare against them.

Column Mapping

If a column name doesn't have a matching property, or vice versa, it will cause a runtime error. This is part of the magic making this happen. We have to make sure that our columns from the stored procedure are one-to-one with our objects. If this is not the case, then we will have to define a Data Transfer Object (DTO) that holds the result and then is parsed into the objects and attached.

See also

In this chapter:

  • Updating entities with stored procedures
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset