使用实体框架从存储过程中检索表数据
问题描述:
我正在使用Entity Framework v6.我有一个如下所示的存储过程
I'm using Entity Framework v6. I have a stored procedure as shown below
CREATE PROCEDURE [dbo].[GetCountryList]
(
@CustomerName VARCHAR(MAX),
@SearchCriteria VARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT ON
SELECT CountryID, CountryName FROM dbo.Table1
WHERE CustomerName = @CustomerName AND CountryName = @SearchCriteria
END
现在我有一个模型课
public class CountryName
{
public int CountryId { get; set; }
public string CountryName { get; set; }
}
所以我想在 List< CountryName>
类型
List<CountryName> countryList = null;
using (DbEntities dbContext = new DbEntities())
{
countryList = //my code to collect the result
}
好吧,我本可以直接在表上运行LINQ to SQL,但是不幸的是,我的要求是从存储过程中获取数据.那么,我该怎么办呢?
Well, I could have run a LINQ to SQL directly on the table but unfortunately my requirement in to get the data from stored procedure. So, how can I do it?
答
- 您需要将存储过程导入为函数.右键单击实体模型的工作区,然后选择
Add->.函数导入
. - 在添加函数导入"对话框中,输入要在模型中引用存储过程的名称,例如
GetCountryListSP
,从下拉列表中选择过程,然后选择返回值该过程的名称为实体
,然后从下拉列表中选择CountryName
. -
然后在代码中:
- You need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose
Add -> Function Import
. - In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model for example
GetCountryListSP
, choose your procedure from the drop down list, and choose the return value of the procedure to beEntities
and chooseCountryName
from the drop down list. Then in the code:
var result = db.GetCountryListSP();//Send parameters too
使用这种方法,可以防止返回存储过程的 -1
.请查看这篇帖子,以获取有关存储过程问题的更多详细信息实体框架.
With this approach you prevent returning -1
of the stored procedure. Please check this post for more details about stored procedure problem with Entity Framework.