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

问题描述

我正在使用实体框架数据库优先方法将项目从 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 文件，并且能够使用我的存储过程，并且在执行任何插入或更新操作时效果很好.但是当我在我的存储过程之一中使用选择查询时，真正的问题发生了.

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 类绑定时，我收到了 Age 值的错误 属性是预期的，但它在结果集中不可用.

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 中映射存储过程?

既然你在做Database First Approach并且你有一个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. 选择从数据库更新模型

您将看到类似于下面的对话框:

You will see the dialog similar to below:

1. 确保您已选中如图所示的框.

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

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

1. 如果您想更改 EF 自动生成的类名，请这样做.我强烈建议您这样做，并为您的类指定一个符合 .NET 命名约定的有意义的名称.我遵循的约定是从存储过程名称中删除任何动词并将单词 result 附加到末尾.所以你会得到如下所示的名字:

1. 按确定

<小时>

一些注意事项

这比手动编写类要好得多，以防您的存储过程名称、它需要的参数或它返回的结果发生变化.这种方法也适用于用户定义的函数.

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.

问题

在向导对话框的选择中，有时会出现存储过程不会出现的情况，那是因为这个.只需将此添加到存储过程的开头:

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屋！