在Entity Framework中使用带有参数的存储过程

I'm using Entity Framework 6.1.3 and have used the database-first approach to let it generate the model files and the .EDMX. I also have the following stored procedure on SQL Server 2008 R2, which Entity Framework brought into the EDMX:

我正在使用Entity Framework 6.1.3并使用数据库优先方法让它生成模型文件和.EDMX。我还在SQL Server 2008 R2上有以下存储过程，Entity Framework将其引入EDMX：

CREATE PROCEDURE [dbo].[FindEmployee] 
    @LastName nvarchar(50), 
    @employeeID nvarchar(50),
    @securityID nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    select * 
    from Employee
    where 
        (lastName = dbo.trim(@LastName) AND dbo.trim(@LastName) IS NOT NULL)
        OR  (employeeID = dbo.trim(@employeeID) AND dbo.trim(@employeeID) IS NOT NULL)
        OR  (securityID = dbo.trim(@securityID) AND dbo.trim(@securityID) IS NOT NULL)
    order by
        case when dbo.trim(@LastName) is not null then CONVERT(char(50), lastName) + CONVERT(char(50), firstName) 
                when dbo.trim(@employeeID) is not null then employeeID 
                when dbo.trim(@securityID) is not null then securityID
            end
END

In a Windows WPF app, I let the user select the column to search on (lastName, employeeID, or securityID) from a combobox. The user provides a search value which will get plugged into that parameter in the call to the stored procedure. The stored procedure then returns a resultset from its SELECT which I'll use to populate a DataGrid.

在Windows WPF应用程序中，我让用户从组合框中选择要搜索的列（lastName，employeeID或securityID）。用户提供一个搜索值，该值将在对存储过程的调用中插入该参数。然后，存储过程从SELECT中返回一个结果集，我将使用它来填充DataGrid。

I'm trying to call the stored procedure in this code; Note that the FindEmployee_Result is an auto-generated class in the EDMX for the stored procedure:

我正在尝试在此代码中调用存储过程;请注意，FindEmployee_Result是EDMX中用于存储过程的自动生成的类：

public FindEmployee_Result SearchEmployees(string lastName, string employeeID, string securityID)
{
    var results = new FindEmployee_Result();

    using (var ctx = new TestSelectionEntities())
    {
        results = ctx.FindEmployee(lastName,employeeID,securityID);
    }

    return results;
}

This code blows up with this error:

这段代码爆炸了这个错误：

Cannot implicitly convert type 'System.Data.Entity.Core.Objects.ObjectResult' to 'TestSelection.data.FindEmployee_Result'

无法将类型'System.Data.Entity.Core.Objects.ObjectResult'隐式转换为'TestSelection.data.FindEmployee_Result'

What am I missing? Thank you.

我错过了什么？谢谢。

1 个解决方案

#1

The solution is to use a List, as the stored procedure returns a resultset of FindEmployee_Result objects:

解决方案是使用List，因为存储过程返回FindEmployee_Result对象的结果集：

public List<FindEmployee_Result> SearchEmployees(string lastName, string employeeID, string securityID)
{
    using (var ctx = new TestSelectionEntities())
    {
        return ctx.FindEmployee(lastName,employeeID,securityID).ToList();
    }
}