Raw SQL Mapping Stored Procedure Results to POCO/DTO Using Entity Framework Core 2

Question

I've pretty much looked everywhere I can, and I'm having a hard time trying to find the solution. it took me a week to create an immensely complex calculation query using a stored procedure, and I'd like to fetch the results from this query and place into a POCO class, similar to what I've done before using EF 6.

Map Stored Procedure Column names to POCO DTO

Basically this:

var p1 = new SqlParameter { ParameterName = "Param1", Value = 1 };
var p2 = new SqlParameter { ParameterName = "Param2", Value = 2 };
string query = "EXEC Calcs_Selections @Param1, @Param2";
var calcs = context.Database.SqlQuery<CalcViewPOCO>(query, p1, p2).ToList();

I've read literature found here:

EF Core Querying Raw SQL

Raw SQL Query without DbSet - Entity Framework Core

And discovered there is no more "free sql" in EF Core anymore. The FromSQL basically projects the results into a real entity found in the database, which I don't want because I don't have a table that has the same columns found. Instead, one solution is to extend the DBContext, and create a new DBSet

But I'm really not sure how to do this. I have a database first model, and used Scaffold-DbContext:

Getting Started with EF Core on ASP.NET Core with an Existing Database

I don't want to add to the context that was created automatically, in my case ProjectAContext, since if I make any more changes to the database, and run scaffolding again, it will overwrite my changes.

Answer 1

Though I couldn't wait any longer for the newer versions of EF Core to add functionality to what I asked, I found a library that helped me solve this solution.

https://github.com/snickler/EFCore-FluentStoredProcedure

It allowed me to take a result set, and map to a list of DTOs.

Sample shown on from the repo:

 var dbContext = GetDbContext();
  dbContext.LoadStoredProc("dbo.SomeSproc")
           .WithSqlParam("fooId", 1)              
           .ExecuteStoredProc((handler) =>
            {                  
                var fooResults = handler.ReadToList<FooDto>();      
                // do something with your results.
            });