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:

 public virtual DbSet<TableName> TableNamePlural { get; set ; }

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 ado.net 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:

modelBuilder.Entity<Model>()
 .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.  

Create

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 };

 db.Models.Add(model);
 db.SaveChanges();

 modelId = model.ModelId;

Update

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)
{
 db.Models.Attach(model);
 model.Features = "500 Series OCLV Frame";

 db.SaveChanges();
}

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. 

Delete

Nothing complicated here again:

var model = new Model { ModelId = modelId };

 db.Models.Attach(model);
 db.Models.Remove(model);
 db.SaveChanges();

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

Conclusion

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.