实体框架不会显示存储过程

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.

我整个上午一直在搞乱这个,但我找不到答案。我试图使用EF来引用存储过程,但无论我尝试什么,我都无法在模型浏览器中显示它。

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

我已经使用以下步骤尝试将过程放入模态中:

add procedure to Entity model

将过程添加到实体模型
right click on model and choose add new --> Function Import

右键单击模型,然后选择添加新 - >功能导入
Give it a name and select my procedure

给它一个名字,然后选择我的程序
generate a new complex collection (I have also tried using an entity, neither work)

生成一个新的复杂集合(我也试过使用一个实体,既不工作)
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.

我已经多次这样做了,我可以在模型中的“函数Imports”文件夹中看到该函数,但它从未出现在模型中,所以我无法引用它。

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.

但是,如果没有大的解决方法,我无法将其转换为IQueryable 。

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

有没有人知道我缺少哪些步骤才能正确添加?

Thanks

P.S. VS 2012, asp.net 4.0

附: VS 2012,asp.net 4.0

5 个解决方案

#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.

验证您用于生成EF模型的SQL登录是否具有执行您尝试导入的存储过程的权限。

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.

转到App.config并查找connectionStrings条目(通常在底部)。如果您有多个连接字符串,那么您想要的是您的上下文使用的字符串。转到您的edmx文件并深入查找实体类。例如,如果您有MyDbModel.edmx,那么您将拥有MyDbModel.Context.tt,而MyDbModel.Context.tt又包含MyDbModel.Context.cs。在MyDbModel.Context.cs文件中,您将拥有一个继承自DbContext的类,构造函数将调用base(“name = <您的连接字符串名称> ”) <您的连接字符串名称> 是您要在其中查找的那个的app.config。

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)

您的连接字符串显示用户(集成安全性将表示已登录的AD用户。这仅在使用您的程序的每个人都具有正确的数据库访问权限时才有效。这在生产环境中可能是一个冒险的假设)

Go to SQL Management Studio and add this stored proc to the user's "Securables"

转到SQL Management Studio并将此存储过程添加到用户的“Securables”

#2

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

转到Sql Server Management Studio中的SP,右键单击属性,转到权限将public设置为Execute。

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

以上都可能是权限问题的答案,上面的答案让我看看为什么并得出结论。

#3

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=.

在.NET项目的app.config或web.config文件中,检查哪个用户标识正在访问数据库。你会在用户id =之后的connectionString之后看到它。

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.

如果它与用于编写存储过程的用户标识(即在SQL中)不同,请与您的数据库管理员联系,以查看.NET(以及随后的实体框架)中的用户标识是否具有执行存储过程的权限。

#4

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

@Limey您的问题可能是由导入功能的访问级别引起的。试试这个:

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.

在模型浏览器中找到文件夹Function Inports。现在右键单击您的函数,然后选择“属性”。在属性窗口中,第一个选项应该是Access将其更改为Public并保存您的model.edmx。

Go back in your code and see if this work.

返回代码,看看是否有效。

#5

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

我有一个类似的问题,从实体框架5升级到6工作来解决这个问题。

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.

对我来说,它没有在存储过程下显示我的自定义sql server架构,尽管它在表部分中正确显示了相同的架构。

Hope this helps someone else.

希望这有助于其他人。

#1