Entity Framework will not show stored procedures

Question

I have been messing with this all morning, but I can't find the answer. I am trying to use EF to reference a stored procedures, but no matter what I try I cannot get it to show up in the model browser.

I have used the following steps to try to get the procedures into the modal:

1. add procedure to Entity model

2. right click on model and choose add new --> Function Import

3. Give it a name and select my procedure

4. generate a new complex collection (I have also tried using an entity, neither work)

5. click OK

I have done this many times, and I can see the function in the "function Imports" folder in the model, but it never shows up in the model, so I can't reference it.

I find I can reference the stored procedure directly (without the import) by doing the following:

    DBEntities db = new DBEntities();
    var test = db.gsp_GetGroups();

However, I cannot convert this to IQueryable<T> without a big workaround.

Does anybody know what steps I'm missing to get this to add properly?

Thanks

P.S. VS 2012, asp.net 4.0

Answer 1

Verify that the SQL log-in you are using to generate your EF model has permission to execute the stored procs you are trying to import.

1. Go to your App.config and look for the connectionStrings entry (usually at the bottom). If you have more than 1 connection string, the one you want is the one your context uses.
   • Go to your edmx file and drill down to find the entities class.
   • For example, if you have MyDbModel.edmx, then under that you'll have MyDbModel.Context.tt which in turn will contain MyDbModel.Context.cs.
   • In the MyDbModel.Context.cs file you will have a class that inherits from DbContext and the constructor will call base("name=<your connection string name>")
   • <your connection string name> is the one you are looking for in your app.config.
2. Your connection string shows the user (Integrated security will mean the AD user that is logged in. This will only work if everyone who uses your program will have the correct DB access. That can be a risky assumption in a production environment)
3. Go to SQL Management Studio and add this stored proc to the user's "Securables"