Executing Raw Sql Queries using Entity Framework Core and returns result to Generic Data Model !
As it is known, in raw sql queries such as executing stored procedures with entity framework core (versions such as core 3.0, core 3.1 and Core Net 5.0), the result types must be defined as DbSet, that means we will either have an entity class as the database table and our query will return that type, or, in our DbContext or we will need to define our own model as DbSet in another dbcontext that we derived from it. Yes, this is a way, but since you think it is an unnecessary one, you searched for this article.
In this article, we will make the Execute Raw SQL command generic, and we will return to our model classes in the return type we want.
While doing this, in our example, we will create our -db first- entity classes with scaffold command, then derive another db context from it, and again derive a generic db context from it. And we will implement all of these on the data access layer, we will never infect the repository layer.
First, when we create our entity base from the database with dotnet ef core, we first create our dbsets and db context like this,
Yes as you can see, this is our main entity dbcontext.
Now, I will derive one more entity context from this, I can actually derive a generic context directly, but if I do, I cannot override the onConfiguring method to build db context with the connection string from appsettings, which is a another article topic (* I will put a link here after I write that article)
Our derived AppDbContext looks like this,
As you can see here, yes we can write our custom dbsets, some of developers prefer this solution, but that’s not the point of our article and we don’t want to do it that way !
We will create a solution by creating a generic dbset (generic entities).
Ok, let’s continue.
We will create a generic dbcontext from our derived AppDbContext, again by inheriting it.
We created our generic dbcontext, and defined only one generic type dbset for it, namely entities for a generic nomenclature.
Ok, now is the time to use this.
Let’s execute a stored procedure using fromRawSql method , and return the result to the model (DTO etc.).
#1 For example, let’s code a method that retrieve products , and collect them into tProduct DTO class , using generic db context ;
public static List<tProduct> GetProducts(string pcode)
{
var result = new List<tProduct>();
using (var ctx = new GenericContext<tProduct>(_appDbContext.Database.GetConnectionString()))
{
result = ctx.Entities.FromSqlRaw<tProduct>(“select , exec etc… your query here “)
.Where(p => p.Code == pcode).ToList();
}
return result;
}
Great ! Even if we code an extension that executes a stored procedure, it will be even better, good idea..
#2 Example , let’s write an extension method to execute procedures on dbcontext,
It looks so good, let’s make a test for the generic raw sql extension method ,
Yes, we easily used the extension method we wrote for the base DbContext class in Microsoft.EntityFrameworkCore, so now we can easily get the result to the desired dto object, here dto model is LoginResult, it is not an entity or any DbSet, good job !
Enjoy code !
