12

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

Improve this question
3
  • I have the same problem, did you ever figure out what the problem was? Commented Nov 19, 2013 at 15:59
  • For my project, I ended up not using EF for stored procs, but i do remember discussing the problem with another developer, and as i remember, he said it was an issue with the proc not having any model associated with it. I will try and hunt him down and ask him. Commented Nov 19, 2013 at 19:16
  • FWIW, Stored proc results can't be further extended through IQueryable on the server side. You may need to use a table value function import if you want additional querying capabilities passed to the dataset. Commented Mar 6, 2014 at 18:56

5 Answers 5

Reset to default
14

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"
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

As I commented in Mark's answer, don't give execute permissions to everyone on everything if you can help it. Keep your DB secure by limiting it to specific log-ins; in this case, the one you assigned to the EF model.
In case you are wondering the whereabout of user's Securable. In SQL Server, under database, Security, Users, then find the user that is specified in connection string of App.config file.
8

Go to SP in Sql Server Management Studio, right click on properties, go to permissions Set public to Execute.

The above could all be the answer it is a permissions issue, the above answers made me look at why and concluded this.

Improve this answer

2 Comments

Be careful with this--granting execute to public can be a security vulnerability. It's better to just grant a specific log-in permissions on the SPs it needs access to and leave it at that.
Good spot, make sure you are granting EXECUTE to the correct user/group AND stored procedure. In my case I had copied EXECUTE permissions from another sp, and didn't change it
6

This may be because Entity Framework is signing on to the database with a user id that does not have permission to execute stored procedures.

To find out:

In your app.config or web.config file on your .NET project, check to see which user id is accessing the database. You'll see it after connectionString just after user id=.

If it is different than the user id you used to write the stored procedure (i.e. in SQL), check with your database administrator to see if that user id in from your .NET (and consequently Entity Framework) has permission to execute stored procedures.

Improve this answer

1 Comment

ID has full access to the DB.
2

I had a similar issue, upgrading from Entity Framework 5 to 6 worked to resolve the issue for me.

For me it was not displaying my custom sql server schema under the stored procedures, although it was showing the same schema correctly in the tables section.

Hope this helps someone else.

Improve this answer

Comments

1

@Limey Your problem is probably caused by the Access level to your import function. Try this:

In the Model Browser find the folder Function Inports. Now right click in your function and choose Properties. In properties windows the first option should be Access change it to Public and save your model.edmx.

Go back in your code and see if this work.

Improve this answer

1 Comment

Sorry, but that is not it. The access was already set to Public.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.