可以在EF Core 3.1中的每个层次结构表上调用存储过程吗？

问题描述：

我正在从EF Core 2.2迁移到3.1。一个重大更改（＃15392）是它不再由存储过程组成，因此您必须添加 AsEnumerable。通常可以，但是我在TPH表上有一个存储过程调用，但失败了：

I'm moving from EF Core 2.2 to 3.1. One breaking change (#15392) was that it no longer composed over stored procedures, so you had to add 'AsEnumerable'. That usually works, but I have a stored procedure call on a TPH table where that fails:

我对SPROC的调用是：

My call to the SPROC is:

 SqlParameter authorizedUserID_p = 
          new SqlParameter("@authorizedUserID", authorizedUser.ID);
 IEnumerable<Post> query = 
          context.Posts.FromSqlRaw<Post>("Post.USP_ReadPost @ID, @AuthorizedUserID",
            parameters: new[]{ parentID_p, authorizedUserID_p }
        ).AsEnumerable<Post>();
 Post targetPost = query.ToList<Post>().FirstOrDefault<Post>();

它会产生此错误，建议使用AsEnumberable（上面我已经在使用）：

And it produces this error, recommending using AsEnumberable (which I'm already using above):

System.InvalidOperationException：使用不可组合的SQL以及包含查询的SQL调用FromSqlRaw或FromSqlInterpolated。
考虑在FromSqlRaw或FromSqlInterpolated方法之后调用 AsEnumerable 在客户端执行组合。

System.InvalidOperationException: FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it. Consider calling AsEnumerable after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side.

我相信原因是因为我的Posts表是Table-hi-hichychy，因为同一应用程序中对SPROCS的其他调用工作正常。将不胜感激！

I believe the reason is because my Posts table is Table-per-hiearchy, as other calls to SPROCS in the same application are working fine. Would appreciate any help possible!

答

由>＃18232跟踪的EFC 3：无法使用与继承另一个实体的实体相关的存储过程。

原因是SP调用不可组合，并且EF Core始终尝试为TPH基础实体编写SQL，以添加区分条件。与全局查询过滤器类似，但是您至少可以使用 IgnoreQueryFilters ，而在这里您别无选择。

The reason is that SP calls are not composable, and EF Core always try to compose SQL for TPH base entities in order to add discriminator condition. Similar to Global Query Filters, but there you can at least use IgnoreQueryFilters, while here you have no option.

好消息是，它已在EFC存储库中修复。坏消息是它将在EFC 5.0之前发布。

The good news is that it's already fixed in EFC repository. The bad news is that it won't be released until EFC 5.0.

由于 AsEnumerable（）不会发布帮助，您所能做的就是等待EFC 5.0。或者，如果可能，将这样的SP转换为可组合的TVF（表值函数）。通常，将标量函数或带有输出参数的存储过程用于非查询返回调用（将通过 ExecuteSql * 执行），并将表值函数用于单个查询返回调用（与 FromSql * 一起使用）。请注意，当前EFC仍然不支持多个查询返回存储过程。

Since AsEnumerable() doesn't help, all you can do is to wait for EFC 5.0. Or, if possible, convert SPs like this to TVF (table valued functions) which are composable. In general, use scalar functions or stored procedures with output parameter(s) for non query returning calls (to be executed with ExecuteSql*), and table valued functions for single query returning calls (to be used with FromSql*). Note that currently EFC does not support multiple query returning stored procedures anyway.

可以在EF Core 3.1中的每个层次结构表上调用存储过程吗？

相关推荐