SyndicationEntity Framework Stored Procedure Generation
Saturday, April 26th, 2008At work my team and I are utilizing the ADO.NET Entity Framework as a means to mitigate the object-relational impedance mismatch. Simply put, we’re utilizing it as an object-relational mapper for our latest project. So far we have been really impressed with the flexibility it provides us, especially surrounding the area of mapping the same table to multiple entity types and creating inheritance relationships among those entity types. It has proven itself as a much more viable candidate than .netTiers or LINQ to SQL for us.
While I have a lot of praise for the Entity Framework, there are some quirks that take getting used to. For example, you can’t update properties that are part of a primary key, even though you can update (non-identity) columns that are part of the primary key in SQL Server 2005. Another is the requirement that if an entity will be inserted into the database using a stored procedure, it must also be updated and deleted through a stored procedure. Yet another is that each stored procedure mapped to a particular entity type must have parameters mapped to every association key, regardless of the type of stored procedure (INSERT, UPDATE, or DELETE). For example, consider the following model:
If I created stored procedures for the Account entity to use in my EF model, they’d look like this:
CREATE PROC dbo.Account_Insert
(
@customerId INT
)
AS
BEGIN
INSERT INTO Account (customerId)
VALUES (@customerId)
SELECT SCOPE_IDENTITY() as accountId
END
GO
CREATE PROC dbo.Account_Update
(
@accountId INT,
@customerId INT
)
AS
BEGIN
UPDATE Account
SET customerId = @customerId
WHERE accountId = @accountId
END
CREATE PROC dbo.Account_Delete
(
@accountId INT,
@customerId INT
)
AS
BEGIN
DELETE FROM Account
WHERE accountId = @accountId
END
Notice the delete stored procedure—it must take in both the ID of the Account and the ID of the Customer it is associated with. Based on Colin Meek’s post here, I can understand why this is necessitated by the Entity Framework, but I can’t dispute the fact that it is different from I’m used to writing stored procedures.
As we moved towards implementing sprocs for our persistence layer to utilize, we ran into a few problems. At first, we thought we could utilize old templates from CodeSmith and .netTiers to generate sprocs, and our EF model would consume them gracefully. Unfortunately, if we used these templates, we would end up with sprocs like this for the Account table:
CREATE PROC dbo.Account_Insert
(
@accountId INT OUTPUT,
@customerId INT
)
AS
BEGIN
INSERT INTO Account (customerId)
VALUES (@customerId)
SET @accountId = SCOPE_IDENTITY()
END
GO
CREATE PROC dbo.Account_Update
(
@accountId INT,
@customerId INT
)
AS
BEGIN
UPDATE Account
SET customerId = @customerId
WHERE accountId = @accountId
END
CREATE PROC dbo.Account_Delete
(
@accountId INT
)
AS
BEGIN
DELETE FROM Account
WHERE accountId = @accountId
END
Notice the slight differences:
- The INSERT sproc takes in an output parameter for the identity column and then sets the value of that parameter to the ID that was just inserted. The EF version of the sproc does not take in an output parameter; instead it just selects SCOPE_IDENTITY() for the EF to merge back into the Account entity that was inserted.
- The DELETE sproc does not take the parameter for the Customer ID, because it is not part of the primary key.
This causes some vexation for us because our new business domain houses about forty tables for which we need to write CUD sprocs that fit the Entity Framework’s rules. We could write them by hand (which would be painful and subject to error), try to modify CodeSmith’s templates to generate them correctly, or try to generate them using some other way.
From the title of this blog entry, you can probably infer that we didn’t write them by hand. Furthermore, Ryan Hauert and I were toying around with the idea of using T4 Templates to generate the SQL we needed. In all honesty, we didn’t even consider modifying the CodeSmith templates; I don’t think this was a bad choice, as T4 templates can benefit people who don’t want to purchase CodeSmith, but it may’ve been easier for our particular situation. But I digress…
The T4 template we created is designed to be a general purpose way to generate CUD procedures for an Entity Framework model. In case you didn’t read the links I provided on the templates, they are code-generation files which are intrinsically recognized by Visual Studio 2008. (They required the VS SDK in VS2005.) They have a .tt extension, and Visual Studio will nested generated code files underneath them in the Solution Explorer, similar to how designer files are nested under Entity Framework and LINQ to SQL models.
It’s a very simple process to use the T4 template:
- Add the file into your project. (The file is linked at the end of the blog entry.)
- Simply save the template file and CUD sprocs will be generated for every EDMX file in the current directory.
If you want to change what directory the template looks in or what files it generates sprocs for, look at the configuration parameters on lines 24 and 25 in the picture above. You can specify the directory explicitly by initializing the directoryName variable to anything other than the empty string or null. Furthermore, if you want to match a specific EDMX file only, you can use a regular expression to filter out unwanted files. I could use it like this:
Regex edmxFileFilter = new Regex(@"Entities\.edmx");
Then only the Entities.edmx file would be processed.
The template not only generates EF-compliant sprocs, but it also considers only the tables that are in the SSDL of the model file. Unfortunately, the template must query the database (SQL Server 2005 only!) in order to make all the decisions necessary to create the output file. However, this is usually quite fast on generations other than the first one.
As you can see from the picture above, the template requires a few different assemblies to be installed. Most of them shouldn’t be a problem when generating EF sprocs, because it strongly suggests that you have the .NET Framework 3.5 installed. Something you may not have are the SQL Server Server Management Objects (SMO), which offer an abstraction layer over the metadata of a SQL Server instance (e.g. databases, tables, columns, keys, indexes, data types), and keep the template cleaner than if we had to make ad hoc queries directly. The SMO installation is available on this page, where its installer is called "SQLServer2005_XMO.msi." There are other installers for 64-bit and Itanium 64-bit.
Once that is installed the template is very simple to run and gives us output like this:
Now we can take this script and run it against SQL Server and then import the sprocs into our Entity Framework model. Finally we can map the stored procedures to the entity types they encapsulate and voilà! We’re done.
There are a few limitations with the template the moment, but it will account for most cases:
- No stored procedures are generated for tables that do not have primary keys (in the database, not in the EF model). There is no way we can infer what you meant for the key to be for the update and delete sprocs; while we could generate insert procedures, it wouldn’t help the end user because the EF requires that an entity type be mapped to all three, or none at all.
- The template is available only in C# at the moment.
- It has not been tested in all scenarios, so any feedback you have would be great.
- It does not account for entity inheritance.
Special thanks to Bob Pace for giving Ryan and me the idea and providing helpful resources on T4.
Also, if you want to get started writing your own T4 templates, check out Hilton Giesenow’s excellent video here.
May 22, 2008 - Updates:
Ryan and I ran into an issue recently where we were accidentally comparing all of the parameters against values in database rows for the delete stored procedures. This was a mistake, because we really only need to check for equality on the primary key columns. The new template has been uploaded.
