EF Model First with Stored Procedures

Question

I'm using EF 4 with model-first...I generate the database from the model. How would I be able to use stored-procedures in that scenario? They should also be auto-generated with the database..?

Maimonides

Answer 1

You don't have any stored procedures with model first and there will not be generated. How could they be? Stored procedure is logic defined in the database and EF has no knowledge of that procedure so it cannot create them for you.

If you want to use stored procedures create your database from model and swap to database first. Then you can manually define your procedures and map them to the model. Once you do that you cannot go back to the model first.

Edit:

There is probably one more solution but it is not nice. You will maintain custom SQL script to with all your stored procedures (you will have to write them anyway) and you will modify workflow for database creation to run your script after database is created. Model first uses T4 templates to generate SQL and Workflow foundation 4 to handle whole process of database creation. Both T4 template and workflow can be customized. The problem is that this step will add stored procedures for you but it will not map them in EDMX.

Disadvantaged

I have a T4 template that generates the SQL for all necessary SPs from the EDMX file. – Maimonides

Answer 2

Ladislav is right except that I have found a probable workaround. I have tested this and it works although cannot tell if there are side effects to it.

1. Download and install the Entity Designer Database Generation Power Pack (if you do not already have it of course).
2. Update your database to the latest version of the model by doing the following (My apologies if you already know the following, this is for those that do not):
   A. Right click in the designer and select Generate Database From Model
   B. In the resultant Dialog box, select the Generate Migration SQL and Deploy option from the left hand side. (be sure to click the "Select Workflow ...." button at the top after you select this option).
   C. Click Next and you should see it do its stuff.
3. Create your stored procedures in the database.
4. Update your model from

Now, your model and the database are synced. The trick is that in the future whenever you want to update either the database or the model first, just repeast steps 2, 3 and 4.

You now have the best of both worlds! One word though is I would make sure you have the latest version of the power pack (you have to uninstall the old version from windows control panel then redownload the latest version and install it. If you do not update and you have VS2010 SP1 then the power pack will not display the necessary dialog box when you choose Generate Database and you would lose your stored procedures more than likely.

Hope you like this. 3.

Kazukokb