Map stored procedure with multiple resultset returned in EF Core

Question

I am currently migrating an already established website from old ASP.NET to ASP.NET Core 2.2. The database is a shared database across other platforms and is also established therefore I cannot just changed anything from it.

I came across to this problem when I had to call a stored procedure from EF Core where this stored procedure returns 3 result sets. The result set of the stored procedure is like this.

Table 1

ColumnName1 | ColumnName2 | SomeColumn | AndMoreColumns
------------+-------------+------------+---------------
Value1      | value 2     | value 3    | Value 4

Table 1

Column1    | Column2
-----------+----------
value1     | value 2

Table 3

Column1
-------
Value1

Table 1 and table 2 are related to each other. This table will always return 1 row on table 1 and table 2 can return multiple rows while table 3 is static.

Now, my question is. How do I map this result set to an EF Core Query<T> model builder? I want to map it per column name since I want to make the column names more friendly. An example that I had that did not work is like this.

modelBuilder.Query<MyModel>(a => 
{
    a.Property(b=>b.ModelId).HasColumnName("ColumnName1");
    a.Property(b=>b.ModelName).HasColumnName("ColumnName2");
    ...
});

but unfortunately, the above mapping did not work. Especially for the 2nd table.

Answer 1

First at all, the result from stored procedure is not a table, I mean an user table.

So, I think there are two possible solutions for your question:

1. Work with pure ADO.NET, execute data reader and cast the result to result models

2. Use an external approach from EF Core

For point 1, please read this question: EF Core query stored procedure map to types

For point 2, please read this question: Working with multiple resultset in .net core

Also you can read this issue: Support multiple resultsets #8127

Let me know if this answer is useful.