C# : how to retrieve records into stored procedure using Entity Framework Core?

entity framework core execute stored procedure with parameters
entity framework core 2.1 stored procedure
how to use stored procedure in entity framework code first
how to call stored procedure in repository pattern using entity framework
how to call stored procedure with join on multiple tables in entity framework core
ef core stored procedure multiple result sets
entity framework core stored procedure output parameter
ef core stored procedure return value

I have created a new ASP.NET Core 2.1 web application with Angular

Visual Studio creates a project that uses the Entity Framework Core with ASP.NET Core MVC.

I have this problem: I have to read records from a stored procedure:

CREATE PROCEDURE GetEmployees
    (@PageIndex INT,
     @PageSize INT)
AS
BEGIN
    SELECT * 
    FROM employee 
    ORDER BY id 
        OFFSET @PageSize * (@PageIndex - 1) ROWS 
        FETCH NEXT @PageSize ROWS ONLY;

    SELECT COUNT(*) AS totalCount 
    FROM employee;
END

I found a question that almost solves my question, but unfortunately there is still something that does not work.

This is my code:

namespace Angular.Controllers
{
    //[Produces("application/json")]
    [Route("api/Employees")]
    public class EmployeesController : Controller
    {
        private readonly ApplicationDbContext _context;

        public EmployeesController(ApplicationDbContext context)
        {
            _context = context;
        }

        // GET: api/Employees/pageIndex/1/pageSize/1
        [HttpGet("pageIndex/{pageIndex}/pageSize/{pageSize}")]
        public Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
        {
            SqlParameter pageIndexParam = new SqlParameter("@PageIndex", SqlDbType.Int);
            pageIndexParam.Value = pageIndex;

            SqlParameter pageSizeParam = new SqlParameter("@pageSize", SqlDbType.Int);
            pageSizeParam.Value = pageSize;

            // SqlParameter pageIndexParam = new SqlParameter("@PageIndex", pageIndex);
            // SqlParameter pageSizeParam = new SqlParameter("@pageSize", pageSize);

            var cmd = _context.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = "GetEmployees"; // The name of the stored procedure
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            // the 2 parameters to be passed to the procedure
            var param = cmd.CreateParameter();
            param.ParameterName = "@PageIndex";
            param.Value = pageIndexParam;
            cmd.Parameters.Add(param);

            var param2 = cmd.CreateParameter();
            param2.ParameterName = "@pageSize";
            param2.Value = pageSizeParam;
            cmd.Parameters.Add(param2);

            try
            {
                // connection.Open();
                // _context.Database.GetDbConnection().Open(); // it crashes after this line
                _context.Database.OpenConnection(); // it crashes after this line
                var dr = cmd.ExecuteReader(); // ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.
                List<Employee> listEmp = new List<Employee>();

                while (dr.Read())
                {
                    // Is there a faster way to read the whole record?
                    // Or should I write a line for each field?
                    Employee emp = new Employee();
                    emp.ID = System.Int32.Parse(dr["id"].ToString());
                    emp.Fname = dr["FName"].ToString();
                    emp.email = dr["email"].ToString();
                    emp.Lname = dr["LName"].ToString();
                    listEmp.Add(emp);

                    dr.NextResult();
                }

                return Ok(listEmp);
            }
            catch (Exception ex)
            {
                // how can I return an error in json format?
                throw;
            }
        }
    }
}

The problem is in the line where the script ExecuteReader:

ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.

I use Microsoft SQL Server In the Startup.cs file, I configured the connection in this way:

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer("Server=localhost\\SQLEXPRESS;Database=master;Trusted_Connection=True;"));

Can you help me?

Maybe I managed to pass the parameters correctly (see my solution, in the answer) but I can not extract the records

I am using Entity Framework Core in my current project and using following approach to get data through stored procedure.

First of all Create a Model with Same number and name of properties in your Stored procedure.

lets assume you are only selecting EmployeeName ,EmployeeId through your stored procedure.

Model.

 public class EmployeeModel
  {
    public string EmployeeName { get; set; }
    public int EmployeeId{ get; set; }
  }

Go to your Db Context file and register your custom model.

 public partial class ApplicationDbContext: DbContext
 {
 public virtual DbSet<EmployeeModel> EmployeeModel{ get; set; }
 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EmployeeModel>(entity =>
        { 
        });
        }
    }

Your Controller

 [HttpGet("pageIndex/{pageIndex}/pageSize/{pageSize}")]
    public Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
    {
    List<EmployeeModel> employeeList = new List<EmployeeModel>();
    employeeList = _context.EmployeeModel.FromSql("GetEmployees 
    @PageIndex,@PageSize", 
    new SqlParameter("@PageIndex", pageIndex),
    new SqlParameter("@PageSize", pageSize)).ToList();
    return Ok(employeeList);
    }

Working with Stored Procedure in Entity Framework Core, Learn how to create and execute the stored procedures in Entity Framework Core​. EF Core provides the following methods to execute a stored procedure: used to execute the stored procedure which returns entity data, but with some limitations You can also pass a parameter value using C# string interpolation syntax,  I am using Entity Framework Core in my current project and using following approach to get data through stored procedure. First of all Create a Model with Same number and name of properties in your Stored procedure. lets assume you are only selecting EmployeeName ,EmployeeId through your stored procedure. Model.

Based on what @bricelam answered here, can you try this?

     public Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
    {
        var cmd = _context.Database.GetDbConnection().CreateCommand();
        cmd.CommandText = "GetEmployees";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
        cmd.Parameters.AddWithValue("@PageSize", pageSize);

        try
        {
          //...
        }
   }

Let me know if anything happens.

Use Stored Procedure in Entity Framework, You can use stored procedures either to get the data or to add/update/delete the records for one or multiple database tables. EF API creates a function instead of  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 be Entities and choose CountryName from the drop down list.

Partially solved riddle:

    // GET: api/Employees/pageIndex/1/pageSize/1
    [HttpGet("pageIndex/{pageIndex}/pageSize/{pageSize}")]
    public async Task<IActionResult> GetEmployeeP([FromRoute] int pageIndex, int pageSize)
    {
        _context.Database.OpenConnection();

        var cmd = _context.Database.GetDbConnection().CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        /*
         * it's the same, it also works with these lines
                    SqlParameter pageIndexParam = new SqlParameter("PageIndex", pageIndex);
                    SqlParameter pageSizeParam = new SqlParameter("PageSize", pageSize);
                    cmd.Parameters.Add(pageIndexParam);
                    cmd.Parameters.Add(pageSizeParam);
        */

        cmd.CommandText = "GetEmployees"; // The name of the stored procedure

        // the 2 parameters to be passed to the procedure
        var param = cmd.CreateParameter();
        param.ParameterName = "@PageIndex";
        param.Value = pageIndex;
        cmd.Parameters.Add(param);

        var param2 = cmd.CreateParameter();
        param2.ParameterName = "@PageSize";
        param2.Value = pageSize;
        cmd.Parameters.Add(param2);




        try
        {
            System.Data.Common.DbDataReader dr = cmd.ExecuteReader();
            List<Employee> listEmp = new List<Employee>();

            while (dr.Read())
            {
                // Is there a faster way to read the whole record?
                // Or should I write a line for each field?
                Employee emp = new Employee();
                //emp.ID = System.Int32.Parse(dr["id"].ToString());
                emp.ID = 1; //  it crashes after this line
                emp.Fname = dr["FName"].ToString(); // System.IndexOutOfRangeException: FName

                /*
                 * it doesn't see the columns :-(
                                    emp.email = dr["email"].ToString();
                                    emp.Lname = dr["LName"].ToString();
                */
                listEmp.Add(emp);
                dr.NextResult();
            }
            return Ok(listEmp);
        }
        catch (Exception ex)
        {
            // how can I return an error in json format?
            throw;
        }



    }

Now it crashes later When it tries to read the record

emp.Fname = dr["FName"].ToString();

System.IndexOutOfRangeException: FName

it doesn't see the columns :-(

ASP.NET Core - Entity Framework Call Store Procedure, This article is about calling store procedure using entity framework in ASP. In today's article, I will demonstrate a stored procedure call by using entity framework I have taken the data sample from AdventureWorks for SQL server 2014 access SQL database tables, stored procedures or queries in order  In this article, you will learn about two basic ways to get data from Entity Framework and display data in Angular UI-Grid using MVC application with Web API. LINQ; Stored Procedure; In this article, I will show how to get data using LINQ query and stored procedure using Entity Framework.

Execute Stored Procedures In MVC Core Using Entity Framework , The first method, FromSql() is used to execute a Stored Procedure that retrieves records from database tables and the second method,  The following example uses dbo.InsertEmployee stored procedure by passing require input parameters and a output paramter. This output parameter will returned the increamented PK to the caller. This is one of the very good technique to insert the record in a table and retrieve the newly inserted record Pk Value using output parameter.

How to execute Stored Procedure in Entity Framework Core, But there are still workaround exists in EF Core to execute stored procedures to get the data and for CRUD operations. In this post, we will see  Now we will need to import the Stored Procedure into the Entity Framework so that it can be called as a Function using Entity Framework. Thus you will need to Right Click the Entity Model, click Add and then click Function Import. This will open the Add Function Import dialog window.

Raw SQL Queries, The following example passes a single parameter to a stored procedure by including a parameter placeholder in the SQL query string and  Stored Procedure in Entity Framework Entity Framework has the ability to automatically build native commands for the database based on your LINQ-to-Entities or Entity SQL queries, as well as build the commands for inserting, updating, and deleting data.

Comments
  • What error do you get? Perhaps the connection is already open?
  • ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.
  • When creating your SqlParameter objects, try specifying the data type and setting the value later. For example: var pageIndexParam = new SqlParameter("@PageIndex", SqlDbType.Int); pageIndexParam.Value = pageIndex;
  • No. it doesn't even work like that
  • Same error? Different error? You need to learn to stop saying "it doesn't work" and give more details or nobody can help you.
  • It doesn't compile: CS1061 C# does not contain a definition for and no extension method accepting a first argument of type could be found (are you missing a using directive or an assembly reference?) See 2 lines before try
  • AddWithValue doesn't exists
  • Please never use AddWithValue, it is awful and potentially dangerous. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…
  • Check if FName column exists in result?
  • Yes the column exists :-(
  • You just mousehover on dr and see what it contains?