Use stored procedure with parameters in Entity Framework

Question

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:

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.

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:

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'

What am I missing? Thank you.

I'm not sure but I think you need to import the stored procedure as a Function. Have a look at this post:stackoverflow.com/questions/32140774/… — Salah Akbari
– Salah Akbari, Commented Mar 11, 2016 at 17:29
Thanks, @S.Akbari. What's weird is that when I ran the Entity Framework tool initially, it pulled the stored procedure into my TestSelection.edmx. So it's there, but how do you call it and use its results? — Alex
– Alex, Commented Mar 11, 2016 at 17:30
Figured it out... The stored proc returns a List and so I needed to change it to use a List<FindEmployee_Result>. — Alex
– Alex, Commented Mar 11, 2016 at 17:36

Alex · Accepted Answer · 2016-03-11 17:43:51Z

5

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

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

answered Mar 11, 2016 at 17:43

Alex

36k14 gold badges81 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jellz77 Over a year ago

Would you put this in the controller or model? Btw...this was very helpful :) +1

Alex Over a year ago

@jellz77, glad the code was helpful! Create a data access layer/service that sits on top of your EF models. Then your controller calls this data service to fetch the data via the stored proc; then hands the data to the view. The data service is just a class that contains the above method; the controller calls it and gets a List. Later, if your db changes to something else, your controller won't have to be changed. A recommended approach would be to have a logic layer that's called by your controller; the logic layer calls the data service.

Collectives™ on Stack Overflow

Use stored procedure with parameters in Entity Framework

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related