Welcome to today’s post.
I will discuss how to use SQL stored procedures within a .NET Core application using Entity Framework Core.
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 utilising 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.
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. 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 our .NET Core application, we define the interface for a produce model as shown:
using System;
using System.Collections;
using System.Collections.Generic;
namespace DataTest.DAL
{
public interface IProductModel
{
IEnumerable<ProductEntry> GetProductRecords(string Type);
}
}
Create a class for the product entry as shown:
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() {}
}
}
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; }
}
}
Define an interface for a 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);
}
}
Define a service class extended from previous interface to expose the stored procedure:
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();
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.
