In part 3 of this series I demonstrated using EDMX with stored procedures only.  In this article I do the same with Code First.  As discussed previously, while sprocs may be as far from the Code First ethos as you can get, you may find yourself in a situation where conventions and existing infrastructure require heavy use of sprocs.  The example code is up at github.  

Setting up Context and Classes

The first step is to set up a new DbContext Class, and entities for your tables.   Why do we want entities if we won’t be accessing tables directly?  Because we will be using Stored Procedure mapping for the CUD operations.  Additionally, you can re-map your sproc output class to match one of the default entities e.g. Model vs ModelSelectByKey_Result.  This will be useful for the update operation later on.  

You can see an example of this in part 2

Updating the Classes when the Database Changes

Before moving on to the examples, one important caveat – currently there is no way to add new classes to an existing context using the Code First from database wizard.  This is a serious drawback as EDMX has a very convenient “Update Model From Database” option.   

The workaround I found, which I posted on stackoverflow, is as follows:

  • Create a new Entity Data Model, called DBContextTemp or whatever, you will be deleting this at the end
  • Choose the Code First from Database wizard option
  • Select your new table(s) as objects to add, then complete the wizard
  • Open your orignal DBContext file and add the following line for each of the new entity classes generated by the wizard:
  •  public virtual DbSet<TableName> TableNamePlural { get; set ; }
  • Remove DBContextTemp
  • This doesn’t solve the problem of updating fields, but adjusting a few properties shouldn’t be too difficult

    Calling Sprocs via SQLQuery()

    Ok so first the bad news – calling parameterized sprocs to return data with Code First is a real pain.  Here’s the code for executing the ModelSelectFilter  sproc:

     var name = new SqlParameter("@name", DBNull.Value);
     var manufacturerCode = new SqlParameter("@manufacturercode", DBNull.Value);
     var categoryName = new SqlParameter("@categoryname", DBNull.Value);
     var description = new SqlParameter("@description", DBNull.Value);
     var features = new SqlParameter("@features", DBNull.Value);
     var minListPrice = new SqlParameter("@minListPrice", DBNull.Value);
     var maxListPrice = new SqlParameter("@maxListPrice", DBNull.Value);
     var statusName = new SqlParameter("@statusName", DBNull.Value);
     var manufacturerName = new SqlParameter("@manufacturerName", DBNull.Value);
     var models = db.Database.SqlQuery<ModelSelectFilter_Result>("product.ModelSelectFilter @name, @manufacturercode, @categoryname,@description,@features,@minListPrice,@maxListPrice,@statusName, @manufacturerName", 
     name,manufacturerCode, categoryName, description, features, minListPrice,maxListPrice,statusName,manufacturerName).ToList();

    compared to the same sproc using function mapping in EDMX:

    var models = db.ModelSelectFilter(null,null,null,null,null,null,null,null,null);

    Yikes.   At this point there doesn’t seem to be much difference in using raw calls or some micro ORM such as Dapper, which would look something like this:

    var models = cnn.Query<Model>("product.ModelSelectFilter", new {name = null, manufacturerCode = null, categoryName = null, description = null, features = null, minListPrice = null, maxListPrice = null, statusName=null,manufacturerName=null }, commandType:CommandType.StoredProcedure ).ToList();

    Thankfully stored procedure mapping takes some of the pain out of this.

    StoredProcedure Mapping

    Stored procedure mapping allows us perform data operations on entities as if entity framework were generating the SQL itself. Mapping the CUD operations takes place in the OnModelCreating() method of the dbContext class.  For each entity you wish to map to, call the MapToStoredProcedures() method:

     .MapToStoredProcedures(s =>
     s.Update(u => u.HasName("product.ModelUpdate"))
     .Delete( d => d.HasName("product.ModelDelete"))
     .Insert( i => i.HasName("product.ModelInsert"))

    Note if your sprocs use output parameters they will need to have a default value of null, and won’t be accessible through procedure mapping.  


    Adding a model is very simple, and no different from direct table access, which is what we want:

    var model = new Model { Name = "Domane 5.2", ListPrice = 3499.99m };
     modelId = model.ModelId;


    Updating has some caveats, but not too bad.  First, you will need to load an instance of the entity, which you can do by changing the type of SqlQuery to :</span>

    var modelIdParam = new SqlParameter("@modelid", modelId);
    var model = db.Database.SqlQuery<Model>("product.ModelSelectByKey @modelid", modelIdParam).SingleOrDefault();
    if (model != null)
     model.Features = "500 Series OCLV Frame";

    Second, note that the new model will not be part of the db.Models collection.  If you update one or more fields and call db.SaveChanges(), nothing will happen.  You first need to attach the model to the Models collection.  This is a slight difference from EDMX where you  can set the function import to map a collection of Models, which the context will automatically detect changes to. 


    Nothing complicated here again:

    var model = new Model { ModelId = modelId };

    Below is the full SQL Profiler trace of the test program:



    So there you have it, a full set of CRUD operations in Code First using only sprocs.   After looking at both, my conclusion that if you use sprocs extensively or exclusively, Code First is not the right tool.  But if you were using a Code First in the majority of your application, I would not create an edmx file simply to interface with a handful of sprocs, Code First certainly can work with sprocs that interact with entities.