Part 32 : Calling stored procedure using EF core

#3 Minutes of Readin

In this article I will show you how to call stored procedure using EF core in asp.net Core 3.1.
Before creating web application, create database table and stored procedure in MS SQL server.

CREATE TABLE [dbo].[Users] (
[id] INT IDENTITY (1, 1) NOT NULL,
[UserName] NVARCHAR (MAX) NULL,
[Password] NVARCHAR (MAX) NULL
);

CREATE PROCEDURE getUserByUserName
@username varchar(50)
AS
SELECT id, username, Password from dbo.Users
where username = @username
RETURN 0

Once table and stored procedure created, create asp.net web application.
Create User model under Models folder.

public class User
{
[Key]
public int id { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
}

Create SqlDbContext.cs class
public class SqlDbContext:DbContext
{
public SqlDbContext(DbContextOptions options) : base(options)
{
}
public DbSet Users { get; set; }
}

Open AppSettings.Json and create connection string configuration under appsettings json file.

Open Startup.cs class and replace below piece of code.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllersWithViews();
services.AddDbContextPool(options => options.UseSqlServer(Configuration.GetConnectionString("SQLConnection")));
}

Create Home controller with below piece of code. GetUser method is responsible to get user from User table. In asp.net core 3.1, we can call stored procedure but with limitation. Users is DBSet property of actual database User table and result return from stored procedure should contains property which is same as user model.
EF core not support SP result which contains join or multiple table result.

Create View Index.cshtml with below piece of code.

Run application and check in browser window.