Calling existing Stored procedure using Entity Framework 6 with C#

Question

I need to know how to call the existing stored procedure in the Entity framework 6 Code First using c#.

Below is the procedure that I am using:

CREATE PROCEDURE proc_getEmployees 
    @departmentname varchar(50),
    @sortCol varchar(30),
    @sortdir varchar(25),
    @searchString varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    declare @strSQl varchar(1000);
    declare @strSQlwhere varchar(500);
    declare @strSelectEndPart varchar(100);
    
    set @strSQl = ';WITH employeetable as 
                (
                    select ROW_NUMBER() OVER (ORDER BY '+@sortCol+' '+@sortdir+' ) AS RowNumber,COUNT(*) over() as TotalRecords, ID,FirstName,LastName,Designation,DepartmentName,Contact,EmailAddress,Location from Employees ';
    set @strSQlwhere = 'where DepartmentName = '''+@departmentname+'''';
    set @strSQlwhere = @strSQlwhere+ ' and (Id like ''%' + @searchString + '%'' Or FirstName like ''%' + @searchString + '%'' Or LastName like ''%' + @searchString + '%'' Or Designation like ''%' + @searchString + '%'' Or DepartmentName like ''%' + @searchString + '%'' Or Contact like ''%' + @searchString + '%'' Or EmailAddress like ''%' + @searchString + '%'' Or Location like ''%' + @searchString + '%'')';
    set @strSelectEndPart =') select * from employeetable';
                    
    set @strSQl = @strSQl +@strSQlwhere+@strSelectEndPart;
    execute (@strSQl);
END
GO

Table I am querying is Employees having the structure as:

Column          Type    Length
ID              int       4
FirstName       varchar   50
LastName        varchar   50
Designation     varchar   50
DepartmentName  varchar   50
Contact         varchar   50
EmailAddress    varchar   50
Location        varchar   50

DBContext Class is as below:

public class DevelopmentTestDatabaseContext :DbContext
{
    public DevelopmentTestDatabaseContext() : base("name =DevelopmentTestDatabaseContext")
    {

    }
    public virtual DbSet<Employee> EmployeeData { get; set; }
    
}

Method for calling the stored procedure as below:

public void GetEmployeeDataUsingProcedure()
{
    object[] parameters = new SqlParameter[4];
    List<EmployeeResultSet> lstEmployees = new List<EmployeeResultSet>();
    try
    {
        using (var db = new DevelopmentTestDatabaseContext())
        {
            SqlParameter param = new SqlParameter("@departmentname", "IT");
            parameters[0] = param;
            param = new SqlParameter("@sortCol", "ID");
            parameters[1] = param;
            param = new SqlParameter("@sortdir", "asc");
            parameters[2] = param;
            param = new SqlParameter("@searchString", "ope");
            parameters[3] = param;

            var results = db.Database.SqlQuery<EmployeeResultSet>("proc_getEmployees @departmentname, @sortCol, @sortdir, @searchString", parameters);
            db.Database.Log = query => System.Diagnostics.Debug.Write(query);
            lstEmployees = results.ToList();
        }
    }
    catch (Exception ex)
    {

    }
}

Defined the class for the stored procedure resultset as below:

public class EmployeeResultSet
{
    public int rowNumber { get; set; }
    public int totalRecords { get; set; }
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Designation { get; set; }
    public string DepartmentName { get; set; }
    public string Contact { get; set; }
    public string EmailAddress { get; set; }
    public string Location { get; set; }
}

Please, let me know if anything else needs to be done before calling the stored procedure. I am new to EF6 and running into issues. What is missing in the code? Do I need to make some changes in any of the class?

Answer 1

One thing I can tell an issue with is in your method where you call the procedure. You are setting all elements of the array equal to param but you are constantly changing param. All of your elements will be equal to the final state of param. Try this instead:

public void GetEmployeeDataUsingProcedure()
{
    object[] parameters = new SqlParameter[4];
    List<EmployeeResultSet> lstEmployees = new List<EmployeeResultSet>();
    try
    {
        using (var db = new DevelopmentTestDatabaseContext())
        {
            parameters[0] = new SqlParameter("@departmentname", "IT");
            parameters[1] = new SqlParameter("@sortCol", "ID");
            parameters[2] = new SqlParameter("@sortdir", "asc");
            parameters[3] = new SqlParameter("@searchString", "ope");

            var results = db.Database.SqlQuery<EmployeeResultSet>("proc_getEmployees @departmentname, @sortCol, @sortdir, @searchString", parameters);
            db.Database.Log = query => System.Diagnostics.Debug.Write(query);
            lstEmployees = results.ToList();
        }
    }
    catch (Exception ex)
    {
        //log it or something
    }
}

There may be other issues but without looking too much into it I'd need more information about specific errors or behavior you are experiencing.

You may also try typing out the full name of your database:

"MyDatabase.MySchema.proc_getEmployees @departmentname, @sortCol, @sortdir, @searchString"

EDIT Per your comments:

A quick ducking and I found this. Essentially it states that if you cast your numbers as int in your query you should be fine. So instead of:

select * from employeetable

Try:

select CAST(RowNumber as int) as RowNumber, 
    CAST(TotalRecords as int) as TotalRecords, 
    CAST(ID as int) as ID, 
    FirstName, 
    LastName, 
    Designation, 
    DepartmentName, 
    Contact, 
    EmailAddress, 
    Location 
from employeetable