Entity Framework Stored Procedure Generation
April 26th 2008 02:24 pm
At 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.
Syndication
Partial sprocs? « Ryan Hauert responded on 28 Apr 2008 at 7:09 am #
[...] and I have written a T4 template to generate Entity Framework-friendly INSERT, UPDATE, and DELETE stored procedures. [...]
Stéphane responded on 30 Apr 2008 at 4:32 am #
Hello,
This will really be helpful in many cases. I just wish the database I’m working on would have primary keys defines… (sad, and horrifying, I know…)
Thank you for your article, and for the template you provide to developers
I noticed an error in the Stored Procedures, you called the DELETE one “Account_insert”, it’s kind of confusing, that might be good to correct it.
And haha, I just saw that you linked to Colin Meek’s post on the MSDN Forum, which is actually in a thread that I started
Stéphane responded on 30 Apr 2008 at 4:33 am #
And your link to http://www.rhauert.com is dead, it seems it is “rhauert.wordpress.com” according to the ping back just above the comments.
David DeWinter responded on 30 Apr 2008 at 6:21 am #
Hi Stéphane, I’m glad that the template will help! I’ve also fixed the typos in the post; thanks for letting me know!
Maybe there is something I can do to generate stored procedures for the tables that don’t have primary keys? I didn’t include them because there is really no way to identify a row, so I’m not sure what to put in the WHERE clauses in the UPDATE and DELETE stored procedures. In your case, if you are using sprocs for those tables, you would know what the key is or is supposed to be, right? Could you then define the primary keys on the tables?
(I’m guessing defining the primary keys on the DB is out of your control, though.)
Regards,
David
Stéphane responded on 01 May 2008 at 1:26 pm #
You’re right, I do know which rows are supposed to be keys. I can’t modify the database/table structure as they are used in many many other apps than mine. In fact, I used a little tool that generated the stored procedures (at least the INSERT ones), and I modified some lines in it.
But, when I see your stored proc, they seem to be a bit more advanced. I don’t have a select statement after inserting a row. I guess it’s returning the inserted value? Is that necessary? What does it return in your code then? an entity object?
I have some issues with insertion, and I’m wondering if that could come from my stored procedures. I’m not at work right now, and I can’t tell you exactly what is the exception, but I might need a hand with that tomorow, in case you are available…
David DeWinter responded on 01 May 2008 at 4:40 pm #
The reason the insert stored procedure selects information after the insert occurs is to retrieve values that the database generates (e.g. identity columns, computed columns) for use in the application. The stored procedure doesn’t return an EntityObject; instead it utilizes the ResultBinding element (search for ResultBinding on the linked page) to map some values to properties in the EntityObject I insert.
Here’s an example:
Here, when I insert StyleAttribute into the database, the database generates the StyleAttributeId. When it comes back into my code, StyleAttributeId is populated with what the database generated.
As for your troubles with your stored procedures, you are free to email me at david —dot— dewinter —at— rev-net —dot— com. I’ll get back to you as soon as I can.
Stéphane responded on 02 May 2008 at 12:59 am #
Hi David,
Actually, I think my problem is coming exactly from what you just explained. When I’m inserting a new row, it works the 1st time, on the next insertion, it tells me that there is a conflict, that an object with the same key already exists in the ObjectContext. I knew it could return a value generated by the database, but I didnt know it would populate the object in the ObjectContext!
I go try this right now to confirm!
So appartently, it’s mandatory to design the stored procedure that return the key value of the inserted object
Thank you !!
Stéphane responded on 02 May 2008 at 2:14 am #
Working… So NEVER do an insert without a result binding :D, that just doesn’t work! (at least not more than 1 time)
GarethJ's WebLog : A rash of bloggers responded on 13 May 2008 at 9:35 pm #
[...] article on using T4 to generate a complete set of sProcs for the new ADO.Net Entity Framework. It appears that he co-conspired on it with Ryan [...]
Function Mapping with Inherited Types « Ryan Hauert responded on 20 May 2008 at 1:07 pm #
[...] map the entities in the hierarchy to the same three stored procedures, which I generated using the T4 template David DeWinter and I developed. The Insert, Update, and Delete stored procedures are defined [...]
Ariful Ambia Noman responded on 03 Jul 2008 at 3:40 am #
very helpfully article. i like it
Emir Treviño responded on 04 Jul 2008 at 1:26 pm #
please, check my realese about code generation with T4 template from Entity Framework model,
http://www.codeplex.com/sfs
Vitaly A. Shulgin responded on 17 Jul 2008 at 1:41 am #
Thank you for article and T4 template.
I have tested it and found incorrect behaviour on table with IDENTITY column. It generates UPDATE SP wich allow to change value of that column, even it’s not possible at SQL-server level. Attemp to call such SP will results to SQL error. Is there something I’ve missed?
David DeWinter responded on 28 Jul 2008 at 12:10 pm #
Hello Vitaly,
Thank you for your comments. I re-read the code and noticed that it is not updating any identity columns as you describe.
Can you post the CREATE TABLE script of the table for which the SPs would break?
Thanks!
David
PeterDer responded on 20 Aug 2008 at 10:14 pm #
Where did you got that nice syntax coloring from???
Fred responded on 25 Sep 2008 at 2:26 pm #
Any chance this otherwise excellent tool can be made to work with SQL Server 2008 Developer Edition? I’d be glad to give it go and report back.
David DeWinter responded on 28 Sep 2008 at 12:37 pm #
Hi PeterDer,
The syntax highlighting is from a beta version of Clarius Consulting’s T4 Editor plugin for Visual Studio.
David
David DeWinter responded on 28 Sep 2008 at 12:39 pm #
Hi Fred,
I believe the only changes which need to be made include changing the version number to 10.0.0.0 on the following two lines, as well as adding a new reference.
Before:
< #@ assembly name="Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"#>
< #@ assembly name="Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
After:
< #@ assembly name="Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"#>
< #@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"#>
< #@ assembly name="Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" #>
This is what I have done in the projects I am working on now and have been successful generating stored procedures for SQL Server 2008.
Hope that helps,
David
Designing Out Loud in the .NET Space : How to Pass Parameters to the T4 Code Generator: Stuff Found on the Web responded on 23 Oct 2008 at 9:32 am #
[...] DeWitter on Entity Framework Stored Procedure Generation: some interesting techniques here but it is aimed at parsing the Entity Framework files. This [...]
Fred responded on 07 Nov 2008 at 11:57 am #
To work with SQL Server 2008, I made a copy of the tt file and made the changes you suggested, but it didn’t work for a ASP.Net Dynamic Data web site because the TT file wrongly assumes that it needs an app.config file. Modifying the following two lines switches it correctly look in web.config instead of app.config.
// Get the connection string out of the EF connection string from the web.config.
XDocument configFile = XDocument.Load(Path.Combine(Path.GetDirectoryName(edmxFilePath), “web.config”));
Even then, when I added an ADO.Net Entity Data Model based upon the AdventureWorksLT sample database, it generates numerous amounts of bad SQL such as the following:
Msg 137, Level 15, State 2, Procedure Product_Insert, Line 54
Must declare the scalar variable “@ThumbNailPhoto”.
The real error is on the line just below the one mentioned in the error because the table defines the column size using the word “max” as you can see from this generated SQL:
——————————————————————————————
–
– “SalesLT”.”Product” Stored Procedures
–
——————————————————————————————
IF OBJECT_ID(’”SalesLT”.”Product_Insert”‘) IS NOT NULL
DROP PROC “SalesLT”.”Product_Insert”
GO
CREATE PROCEDURE “SalesLT”.”Product_Insert”
(
@Name Name,
@ProductNumber nvarchar(25),
@Color nvarchar(15),
@StandardCost money,
@ListPrice money,
@Size nvarchar(5),
@Weight decimal(8,2),
@ProductCategoryID int,
@ProductModelID int,
@SellStartDate datetime,
@SellEndDate datetime,
@DiscontinuedDate datetime,
@ThumbNailPhoto varbinary(-1),
@ThumbnailPhotoFileName nvarchar(50),
@ModifiedDate datetime
)
AS
BEGIN
INSERT INTO “SalesLT”.”Product”
(
“Name”,
“ProductNumber”,
“Color”,
“StandardCost”,
“ListPrice”,
“Size”,
“Weight”,
“ProductCategoryID”,
“ProductModelID”,
“SellStartDate”,
“SellEndDate”,
“DiscontinuedDate”,
“ThumbNailPhoto”,
“ThumbnailPhotoFileName”,
“ModifiedDate”
)
VALUES
(
@Name,
@ProductNumber,
@Color,
@StandardCost,
@ListPrice,
@Size,
@Weight,
@ProductCategoryID,
@ProductModelID,
@SellStartDate,
@SellEndDate,
@DiscontinuedDate,
@ThumbNailPhoto,
@ThumbnailPhotoFileName,
@ModifiedDate
)
SELECT INSERTED.”ProductID”, INSERTED.”rowguid”
FROM “SalesLT”.”Product” AS INSERTED
WHERE “ProductID” = SCOPE_IDENTITY()
END
GO
The table in AdventureWorksLT is defined as:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [SalesLT].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[Color] [nvarchar](15) NULL,
[StandardCost] [money] NOT NULL,
[ListPrice] [money] NOT NULL,
[Size] [nvarchar](5) NULL,
[Weight] [decimal](8, 2) NULL,
[ProductCategoryID] [int] NULL,
[ProductModelID] [int] NULL,
[SellStartDate] [datetime] NOT NULL,
[SellEndDate] [datetime] NULL,
[DiscontinuedDate] [datetime] NULL,
[ThumbNailPhoto] [varbinary](max) NULL,
[ThumbnailPhotoFileName] [nvarchar](50) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [AK_Product_Name] UNIQUE NONCLUSTERED
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [AK_Product_ProductNumber] UNIQUE NONCLUSTERED
(
[ProductNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [AK_Product_rowguid] UNIQUE NONCLUSTERED
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
There are dozens of other errors that I won’t bother to enumerate here because you can easily see for yourself by simply connecting to AdventureWorksLT and pull in all the tables.
Let me know if you need help testing any future corrected editions of the TT file and I’ll be glad to help.
Fred responded on 07 Nov 2008 at 12:10 pm #
Follow up to my previous post about using the T4 template with SQL Server 2008. The T4 template cannot handle columns defined as either of the following:
varbinary(max)
nvarchar(max)
See tables [SalesLT].[Product] and [SalesLT].[SalesOrderHeader] in database AdventureWorksLT.
David DeWinter responded on 09 Nov 2008 at 11:30 am #
Thanks for the bug report, Fred. I knew there was probably something that I missed.
I’ve updated the template to account for both app.config and web.config files, as well as the “MAX” specification on varchar/nvarchar/varbinary data types. If you just want to change your local version, find the ToSqlString() method and change the line that evaluates the varchar/nvarchar/varbinary data types to the following:
return String.Format(”{0}({1})”, dataType.Name, dataType.MaximumLength == -1 ? “MAX” : dataType.MaximumLength.ToString());
Hope that helps!
hrvoje responded on 11 Mar 2009 at 1:54 am #
Hi!
I’m having problem with reading config file. I’ve tried:
XDocument configFile = XDocument.Load(configurationFile);
// as well as this one:
// XDocument configFile = XDocument.Load(Path.Combine(Path.GetDirectoryName(edmxFilePath), “web.config”));
// and it finally breaks at this one:
IEnumerable connectionStrings = configFile.Element(”configuration”) // element
Any help would be really appreciated!
Thanks in advance,
Hrvoje
David DeWinter responded on 15 Mar 2009 at 10:14 am #
Hrvoje, what error message do you see?
David Nussdorfer responded on 07 Apr 2009 at 12:21 pm #
David DeWinter -
I am also getting a similar error that hrvoje reported. I am enclosing the error line:
Error 2 Running transformation: System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.VisualStudio.TextTemplatingBE516C5F9BB210222975230EABA728B0.GeneratedTextTransformation.d__3.MoveNext() in g:\Projects\Temp\ScheduleLiveDC\ScheduleLiveDC\ScheduleLiveSprocs.tt:line 487
at Microsoft.VisualStudio.TextTemplatingBE516C5F9BB210222975230EABA728B0.GeneratedTextTransformation.TransformText() in g:\Projects\Temp\ScheduleLiveDC\ScheduleLiveDC\ScheduleLiveSprocs.tt:line 102 G:\Projects\Temp\ScheduleLiveDC\ScheduleLiveDC\ScheduleLiveSprocs.tt 1 1 ScheduleLiveDC
I created the Entity, removed the sysdiagrams table (don’t know why the entity framework makes that table), copied your tt file into a new tt file, changed the SQL Server for 2008 as you suggested above, then hit the save button.
I can send you my table script if needed.
Thanks,
David nussdorfer
David Nussdorfer responded on 07 Apr 2009 at 12:25 pm #
Additional info Dave, it appears that the sysdiagrams table does not get removed from the entity framework code, therefore your tt extracts and tries to build something from it.
Therefore, it most likely is not an issue with your script, but an issue in the Entity Framework. Had I just spent one more minute looking at the error, I would have found the issue……
Great job!
Dave Nussdorfer
David DeWinter responded on 08 Apr 2009 at 5:43 pm #
Thanks for the info Dave.
Using T4 to Generate Stored Procs for Entity Framework | Trent Jones responded on 11 May 2009 at 11:08 am #
[...] Entity Framework Stored Procedure Generation [...]