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 :-)

To call and use stored procedures from a database in Entity Framework is pretty easy. Stored procedures can return Scalars, Entities and also Complex Types (properties aggregated) as we will see here.

In this example we add an existing stored procedure called sp_GetArtistNameAndCountryById. We do this by refreshing the model from the database and select the stored procedure to be related to the store model.

After that we open the Model Browser and right click the store procedure name in the store and select ‘Add Function Import’…

In the next dialog we add the stored procedure as a function to the conceptual model. Name the method and specify it to return a complex type. Press the ‘Get Column Information’ button to retrieve the returned properties. Also click the ‘Create New Complex Type’ to return as our new special Artist name-country combination.

If we look at the Model Browser now, we see the Function Import for the GetArtistNameAndCountryById function in the conceptual model.

We write a test to see if it works. We retrieve an Artist with Id = 6 which happens to be the most wonderful Irish singer Luka Bloom. Note that the resultArtist is our complex type (GetArtistNameAndCountryById_Result).

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 CallingGetArtistNameAndCountryByIdReturnsArtists()
        {
            using (var context = new MusicModelContainer())
            {
                ObjectResult<GetArtistNameAndCountryById_Result> result =
                    context.GetArtistNameAndCountryById(6);
                GetArtistNameAndCountryById_Result resultArtist = result.First();
 
                Debug.WriteLine("{0} from {1}",
                                resultArtist.Name,
                                resultArtist.Country);
 
                // Id 6 is Luka Bloom
                Assert.IsTrue(resultArtist.Name.Equals("Luka Bloom"));
            }
        }
    }
}

And the test has passed ones again :-)

If you find yourself often in a situation where you query for a specific entity under the same conditions (e.g. whether a product list price is valid today), you might want to take a look at mapping conditions. With the proper mapping condition set we can make sure that this condition is always present if we query for a specific entity.

In this example we have a music genre entity with a Name and a bit field called Inactive. Certain genres get inactive over time if they are merged together or renamed.

We never ever ever want to return inactive genre entities, so this is a good candidate for conditional mapping. We do this by opening the Table Mapping for the Genre entity and set a condition as follows.

If we now Validate our model we get a nasty error stating…

Error 3032: Problem in mapping fragments starting at line 344:Condition member ‘Genres.Inactive’ with a condition other than ‘IsNull=False’ is mapped. Either remove the condition on Genres.Inactive or remove it from the mapping.

Since we can map a property only once per entity, we must remove the Inactive property from the entity. Just select the Inactive property and press delete :-)

Now if we query for Genres the generated SQL statement will always contain the condition where Inactive is false. In the code below we query for all Genres but only get the active ones back.

using System;
using System.Data.Objects;
using System.Linq;
using Entities;
 
namespace MusicConsole
{
    internal class Program
    {
        private static void Main()
        {
            using (var context = new MusicModelContainer())
            {
                IQueryable<Genre> query = context.Genres;
 
                Console.WriteLine(((ObjectQuery) query).ToTraceString());
                Console.ReadKey();
            }
        }
    }
}

Another very flexible Entity Framework mapping feature, besides table splitting and entity splitting, is the use of complex type properties. These come into play when we want to group certain properties together to better mold and organize our model to our needs.

In the example below we have a Fan (of an artist) entity.

Now this entity has a lot of properties related to the name of the person. We can ‘merge’ them into a complex type. This way all name related properties are grouped together in the entity. To do this we select all properties we want to include into this complex type. Right click and select “Refactor into New Complex Type”. We see our newly created complex type in the model browser and name it FanName.

We also need to change the name of the complex type property (of type FanName) into ‘Name’ to make it more defining. Note the icon of the complex type property has a distinct character.

So now we can use the complex type property in our code as follows.

using System;
using System.Linq;
using Entities;
 
namespace MusicConsole
{
    internal class Program
    {
        private static void Main()
        {
            using (var context = new MusicModelContainer())
            {
                IQueryable<Fan> query = 
                    context.Fans.Where(f => f.Name.FirstName.Equals("Bob"));
 
                foreach (Fan fan in query)
                {
                    Console.WriteLine(fan.Name.SurName);
                }
            }
        }
    }
}

There are a number of mapping scenarios in the Entity Framework to relate the data store to the conceptual model. If we have two tables with a key in common, one-to-one or zero-to-one relationship, we can merge them into one entity. This is called entity splitting.

In the example below (see the design here) we have extended the Album entity with an optional AlbumSpec. In the database we have two identical tables to store these entities.

Since Album and AlbumSpec have a key in common and there’s a zero-to-one relationship, we can merge the AlbumSpec properties into the Album entity. This is a simple select, cut and paste of the properties from AlbumSpec to Album as shown here. We transfered DistributionPartner, CoverType and Label to the Album entity.

We need to map these properties to the data store by hand using the Table Mapping of the Album entity. We add an extra table mapping for the AlbumSpec table as shown below.

Finally we don’t need the AlbumSpec in the model any more, so we can safely throw it in the trashbin. If we hit the delete key the ‘Delete Unmapped Tables and Views’ dialog appears. The message states that ‘The following tables and Views in the store model will no longer be mapped. Do you want them deleted?’. Be sure to choose ‘No’, since we still need that mapping to the properties we just moved to the Album entity.

So now we have merged two tabled via mapping into one entity.