Code First Entity Framework & Stored Procedures

Code First for EF and Stored Procedures don’t work well together. After some searching and experimenting, the best solution I have come up with is the following:

1. Create your sproc(s) in SQL Server Manager.

2. Select your stored procedure(s) in the Object Explorer tab, Select “Script Stored Procedure as” -> “DROP and CREATE To” -> and save the resulting text in a file in your data model project. In my example, the file name is

CreateSprocs.sql.

3. [Optional]: Add a Post-build event command line: sqlcmd -i CreateSprocs.sql.

4. [Optional]: Change the CreateSprocs.sql file’s Properties for the “Copy to Output Directory” attribute to “Copy always”.

To test that it is working properly:

1. rename the stored procedures that the script will create.

2. if you followed the optional steps build your data model project . If you did not follow the optional steps, you will have to run the scripts in your CreateSprocs.sql file.

The stored procedures should be recreated with the appropriate names.

Note:

1. Steps 3 & 4 are only necessary if you want to automatically update all sprocs on developers' machines each time the project is built. This may cause loss of work if several developers are working in stored procedures at once.

2. If you do not follow the optional steps, each developer will have to manually run the CreateSprocs.sql file each time it is updated.