实体框架数据库存储过程的第一种方法 [英] Stored procedure in Entity Framework database first approach

问题描述

我正在使用Entity Framework数据库第一种方法将项目从Webforms转换为MVC应用程序，并将数据库准备就绪，并附带所有存储过程。

I am doing transition for a project from Webforms to MVC application using Entity Framework database first approach and have database ready along with all stored procedures in place.

我成功创建了一个 .edmx 文件，并且能够使用我的存储过程，任何插入或更新操作执行。但是当我在我的一个存储过程中使用select query时，会出现真正的问题。

I successfully created an .edmx file and was able to use my stored procedures and it worked great when there was any insert or update operation to perform. But the real problem occurred when I was using select query in one of my stored procedures.

例如，有一个 Employee 表具有以下列：

For example, there is an Employee table which has following columns:

EmpId, FirstName, LastName, Age, Salary

我有一个存储过程 GetAllEmpDetails ，它具有以下选择查询。

I have a stored procedure GetAllEmpDetails which has following select query.

Select 
    EmpId, (FirstName + ' ' + LastName) as FullName, Salary 
from 
    Employee

现在，当我试图将这个存储过程的结果与根据表结构具有5个属性的 Employee 类绑定时，那么我会收到年龄属性的值的错误，但在结果集中不可用。

Now when I am trying to bind the result of this stored procedure with the Employee class which has 5 properties as per the table structure, then I am getting an error that value for Age property is expected but it is not available in the resultset.

我知道没有 FullName 属性，所以我的问题是如何解决这个问题与模型生成的类（如这种情况 Employee ），以便它可以解决这些动力？

I know there is no FullName property as well, so my question is how to solve this problem with the model class generated (as in this case Employee) so that it can tackle these dynamism?

推荐答案

如何在EF中映射存储过程？

由于您正在执行数据库优先方法并且您有一个EDMX文件，让EF为您生成存储过程结果的类。您可能有许多存储过程，并且您希望避免手动创建类：完全是使用ORM工具的全部。还有一些您的存储过程可能有参数。这样做下面的方式将处理所有这些为你。

Since you are doing Database First Approach and you have an EDMX file, let EF generate the class of the stored procedure result for you. You may have many stored procedures and you want to avoid creating the classes manually: After all that is the whole point of using an ORM tool. Also some of your stored procedures may have parameters. Doing it the way below will handle all that for you. It is actually pretty simple.

若要让EF为您执行此操作，请按照以下步骤操作：

To get EF to do this for you, follow the steps to below:

1. 双击您的EDMX文件


2. 选择从数据库更新模型

1. Double click your EDMX file
2. Choose Update Model from Database

您将看到如下对话框：

1. 确保您已选中所示框。

这将添加存储过程，您将在模型浏览器中看到它，如下所示：

That will add the stored procedure and you will see it in your model browser as shown below:

1. 如果要更改由EF自动生成的类名称，然后执行所以。我强烈建议您这样做，并给您的类一个有意义的名称，遵循.NET命名约定。我遵循的约定是从存储过程名称中删除任何动词，并将结果字添加到最后。所以你会得到如下所示的名字：

1. 按确定

---

某些Notes

这比手动编写类更好，如果您的存储过程名称或它需要的参数，或返回的结果更改。这种方法也适用于用户定义的功能。

This is much better than writing the classes manually in case your stored procedure name, or the parameters it needs, or the result it returns changes. This approach will work for user defined functions as well.

A Gotcha

有时，存储过程将不会出现在向导对话框中的选择中，这是因为此。只需将其添加到您的存储过程的开始：

There will be times when the stored procedure will not appear in the selection in the wizard dialog, that is because of this. Simply add this to the beginning of your stored procedure:

SET FMTONLY OFF -- REMEMBER to remove it once the wizard is done.

这篇关于实体框架数据库存储过程的第一种方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！