Welcome to today’s post.
I will discuss how to use SQL stored procedures within a .NET Core application using Entity Framework Core.
In a previous post I showed how to use Entity Framework Core within a .NET Core application. In that post I showed how to configure entity framework core within a .NET Core application, and then connect to a SQL server database connection through the application startup. I also showed how to define the entities from your SQL database within the data context, then associate each table entity to a model class or DTO class within your application. This would then setup our application to access the entities within our SQL database from within the application by means of LINQ based syntax from the data context.
Even though Entity Framework Core is an ORM provider whose intended purpose is to make the task of interacting with the backend SQL data store transparent by utilizing LINQ queries, you can still use EF Core to both call raw SQL queries and in addition, also allow Stored Procedures to be executed from a .NET Core class.
In the legacy Entity Framework 4.x to 6.x to allow stored procedures to be executed we had to expose a stored procedure through an EDMX designer, regenerate the data context class and call the stored procedure through a partial class of the context class. That is quite a fair amount of overhead to call a stored procedure. In EF Core it is more straightforward.
Steps to call a Stored Procedure with Entity Framework Core
Even though EF Core supports a model-based code-first approach to creating data access to SQL data, it is possible to execute calls to stored procedures from within an EF Core SQL data context.
I will show how this is done in the following sections.
Create or Use an Existing a Stored Procedure
We have an example of a product database with a table, ProductEntry.
The schema is below:
CREATE TABLE [dbo].[ProductEntry] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[ProductName] NVARCHAR (50) NOT NULL,
[Category] NVARCHAR (50) NULL
);
We define a simple stored procedure, spGetProductList to select data based on a parameter:
CREATE PROCEDURE [dbo].[spGetProductList]
@type varchar(100) = ''
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM ProductEntry
WHERE Category = @Type
END
In the next section I will show how to define the interfaces, classes, and members within the data context to enable access to the stored procedure.
Create an Interface and Implementation for the Stored Procedure
The next step for us is to create an interface to be able to access the records returned from the stored procedure, and a model / DTO class for each record within the list of records. We do this first by creating an interface within our .NET Core application.
A model class for the Product records with a method returning a list of record entries is shown below:
using System;
namespace DataTest.DAL
{
public class ProductEntry
{
public int Id { get; set; }
public string ProductName { get; set; }
public string Category { get; set; }
public ProductEntry() {}
}
}
We define the EF Core data context for the product as shown:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
namespace DataTest.DAL
{
public class EFProductDBContext : DbContext
{
public EFProductDBContext(DbContextOptions options) : base(options)
{ }
protected EFProductDBContext()
{ }
public DbSet<ProductEntry> ProductEntry { get; set; }
}
}
We then define an interface for the product service:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataTest.DAL;
namespace DataTest.Business
{
public interface IProductService
{
List<ProductEntry> GetProductList(string producttype);
}
}
With the above structures our application still does not know how to execute the stored procedure. In the next section I will create a service class that exposes the stored procedure from the data context to allow it to be used.
A Service Class to Expose the Stored Procedure
We then implement a service class extended from previous interface to expose the stored procedure. This is shown below:
using System;
using System.Collections.Generic;
using DataTest.DAL;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System.Data.SqlClient;
namespace DataTest.Business
{
public class ProductService: IProductService
{
private EFProductDBContext db;
public ProductService(EFProductDBContext _db)
{
db = _db;
}
public List<ProductEntry> GetProductList(string producttype)
{
List<ProductEntry> lst = new List<ProductEntry>();
var rslt = db.ProductEntry.FromSql(
"spGetProductList @p0",
parameters: new[] { producttype });
return rslt.ToList();
}
}
}
Note: The alternative to the above SP in LINQ is:
var productList = (from e in db.ProductEntry
where e.Category == producttype
select e).ToList();
Finally, you should check that your startup has the connection strings to your SQL database setup correctly, and optionally, if your database is being created each time the application is run, then check that is created through the data context. This is outlined in the next section. This will avoid some inconvenient unhandled “500” errors.
Checking your Startup Configuration
In our start up we configure the database and service as shown:
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
public void ConfigureServices(IServiceCollection services)
{
…
var connection = Configuration.GetConnectionString("connstr");
services.AddDbContext<EFProductDBContext>
(options => options.UseSqlServer(connection));
}
public void Configure(IApplicationBuilder app,
IHostingEnvironment env)
{
…
using (var serviceScope = app.ApplicationServices
.GetService <IServiceScopeFactory>().CreateScope())
{
var context = serviceScope.ServiceProvider
.GetRequiredService<EFProductDBContext>();
context.Database.EnsureCreated();
Initialize(context);
}
}
…
}
We will also need to include the database connection in the appSettings.json file.
We must also be aware of some limitations within the EF Core on the DbSet<TEntity>.FromSql() extension method. These are as follows:
- The result must include all columns of the target entity. In this case, all columns of table ProductEntry.
- The result cannot include joins to other tables.
- CRUD procedures cannot be mapped with the entity.
This is quite a useful method to use when we are faced with replacing existing LINQ queries that might not be performing as we expect. In these situations, we can fall back to utilize a stored procedure that has a tuned parametrized query.
That’s all for today’s post.
I hope you found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
