Many Brownfield Architectures (read this book!) provide a database with stored procedures for CUD operations. Or maybe the company we work for has a scary DBA believing his precious little handcrafted stored procedures are a piece of art. These Insert, Update and Delete functions are easily mapped within entity framework.

In this example we have the Music Database with a Biography table with a Title, Description and Related Artist column. There are three stored procedures to apply Insert, Update and Delete operations to this table.

-- INSERT
ALTER PROCEDURE [dbo].[sp_InsertBiography]
	@Title varchar(50),
	@Description varchar(MAX),
	@ArtistId int
AS
BEGIN
INSERT INTO [dbo].[Biography]
	([Title]
	,[Description]
	,[ArtistId])
     VALUES
	(@Title
         ,@Description
         ,@ArtistId)
		
	SELECT SCOPE_IDENTITY() AS NewBiographyId
END
 
-- UPDATE
ALTER PROCEDURE [dbo].[sp_UpdateBiography]
	 @Id int
	,@Title varchar(50)
	,@Description varchar(MAX)
	,@ArtistId int
AS
BEGIN
UPDATE [dbo].[Biography]
SET
	[Title] = @Title
	,[Description] = @Description
	,[ArtistId] = @ArtistId
WHERE [Id] = @Id
END
 
-- DELETE
ALTER PROCEDURE [dbo].[sp_DeleteBiography]
	 @Id int
AS
BEGIN
DELETE FROM [dbo].[Biography]
WHERE [Id] = @Id
END

To map these into our entity model we need to let the Entity Framework know of their existence in the database. To do this we right-click the model designer surface and choose ‘Update Model from Database’. In the Update Wizard we select the Biography table, and the three corresponding CUD stored procedures.

Right click the Biography entity and open the Stored Procedure Mapping. Now choose the correct stored procedure for Insert, Update and Delete. Note that the INSERT function can also return the NewBiographyId value. This is mapped to the Id property of the Biography entity.

Now let’s use a rather clunky test method to do some Insert, Update and Delete operations on the Biography entity.

using System.Data.Objects;
using System.Diagnostics;
using System.Linq;
using Entities;
using Microsoft.VisualStudio.TestTools.UnitTesting;
 
namespace MusicEntitiesTests
{
    [TestClass]
    public class ArtistTest
    {
        [TestMethod]
        public void InsertBiographyReturnsId()
        {
            using (var context = new MusicModelContainer())
            {
                //INSERT
 
                var bio =
                    new Biography
                        {
                            ArtistId = 6,
                            Title = "Luka's younger years",
                            Description = "A lot bio-stuff about Luka Bloom"
                        };
 
                context.AddToBiographies(bio);
                context.SaveChanges();
 
                Assert.IsTrue(bio.Id > 0);
 
                Debug.WriteLine("INSERT Bio - Id = {0}: {1} - {2}",
                                bio.Id,
                                bio.Title,
                                bio.Description);
 
                // UPDATE
 
                const string description = "Somewhat more on Luka...";
                bio.Description = description;
                context.SaveChanges();
                context.Refresh(RefreshMode.StoreWins, bio);
 
                Assert.AreEqual(description, bio.Description);
 
                Debug.WriteLine("UPDATE Bio - Id = {0}: {1} - {2}",
                                bio.Id,
                                bio.Title,
                                bio.Description);
 
                // DELETE
 
                context.Biographies.DeleteObject(bio);
                context.SaveChanges();
 
                bool isDeleted = context.Biographies.All(b => b.Id != bio.Id);
 
                Debug.WriteLine("DELETE Bio - Id = {0}: isDeleted = {1}",
                                bio.Id,
                                isDeleted);
 
                Assert.IsTrue(context.Biographies.All(b => b.Id != bio.Id));
            }
        }
    }
}

Nice :-)