映射具有EF Core中返回的多个结果集的存储过程 [英] Map stored procedure with multiple resultset returned in EF Core

问题描述

我目前正在将一个已经建立的网站从旧的ASP.NET迁移到ASP.NET Core 2.2。该数据库是跨其他平台的共享数据库，并且也已建立，因此我不能仅对其进行任何更改。

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.

我不得不调用存储时遇到了此问题。 EF Core的存储过程返回，其中该存储过程返回3个结果集。存储过程的结果集是这样的。

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.

表1

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

表1

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

表3

Column1
-------
Value1

表1和表2相互关联。该表将始终在表1上返回1行，而表2在表3静态时可以返回多行。

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.

现在，我的问题是。如何将此结果集映射到EF Core Query< T> 模型构建器？我想按列名称映射它，因为我想使列名称更友好。我有一个不起作用的例子是这样的。

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.

推荐答案

首先，存储过程的结果不是表，我的意思是用户表。

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. 使用纯ADO.NET，执行数据读取器并将结果转换为结果模型

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

使用EF Core的外部方法

Use an external approach from EF Core

对于第1点，请阅读以下问题： EF核心查询存储过程映射到类型

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

针对第2点，请阅读以下问题：在.net core中使用多个结果集

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

您也可以阅读以下问题：支持多个结果集＃8127

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

让我知道此答案是否有用。

Let me know if this answer is useful.

这篇关于映射具有EF Core中返回的多个结果集的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！