如何通过FromSqlRaw在EF Core 3.0中调用存储过程 [英] How to call a stored procedure in EF Core 3.0 via FromSqlRaw

问题描述

我最近从EF Core 2.2迁移到EF Core 3.0。

I recently migrated from EF Core 2.2 to EF Core 3.0.

不幸的是，我还没有找到一种调用存储过程以返回实体的方法。

Unfortunately, I haven't found a way to call a stored procedure that returns an entity.

在EF Core 2.0中，可能是：

In EF Core 2.0 it was possible:

var spParams = new object[] { "bla", "xx" };
var createdPath = ModelContext.Paths.FromSql("AddNodeWithPathProc  @p0, @p1", spParams).Single();

在EF Core 3.0中，方法 FromSQL 是替换为 FromSqlRaw 。但是，我无法成功调用存储过程然后处理该值。当存储过程将数据插入数据库时​​，这很有用。

In EF Core 3.0 the method FromSQL is replaced with FromSqlRaw. However, I didn't manage to successfully call a stored procedure and then process the value. This is useful when the stored procedure inserts data into the database.

因此在EF Core 3.0中，我使用以下代码：

So in EF Core 3.0, I use this code:

var createdPath = ModelContext.Paths.FromSqlRaw("AddNodeWithPathProc @p0, @p1", spParams).Single();

但它会引发异常，因为生成的SQL无效并且看起来像这样：

but it will throw an exception, because the generated SQL is invalid and looks something like this:

exec sp_executesql N'SELECT TOP(2) [p].[PathId], [p].[Level], [p].[NodeId], [p].[NodePath], [p].[NodePathString]
FROM (
     @sql @p0, @p1
) AS [p]',N'@p0 nvarchar(4000),@p1 nvarchar(4000), @sql nvarchar(100)',@p0=N'1a',@p1=N'', @sql=N'AddNodeWithPathProc'

我尝试了很多变化，但没有成功。

I tried quite a few variations, but without success.

我开始认为不可能使用 ModelContext运行存储过程。[IQueryable] .FromSqlRaw 。在我看来，这种方法克服了 FromSqlRaw 的主要原因之一，因为对于普通的select语句，LINQ通常足够好。

I'm starting to think that it is not possible to run stored procedures with ModelContext.[IQueryable].FromSqlRaw. In my opinion this kind defeats one of the major reasons for FromSqlRaw because, for normal select statements, LINQ is normally good enough.

有人知道如何将存储过程与EF Core 3.0中的 FromSqlRaw 结合使用吗？

Does anyone know how to use stored procedures in combination with FromSqlRaw in EF Core 3.0? Any help is greatly appreciated.

预先感谢

PS：我知道您可以使用以下命令执行存储过程 this.Database.ExecuteSqlRaw（SQL，parameters）。但是，用这种方法不可能检索存储过程查询的任何实体。

PS: I know you can execute a stored procedure with this.Database.ExecuteSqlRaw(SQL, parameters). However, that way it is not possible retrieve any entities that the stored procedure queries.

推荐答案

解决方案（感谢David Browne，您应该将其发布为答案）：

Solution (thanks to David Browne, you should have posted it as an answer):

用ToList替换单个作品:-)

Replacing Single with ToList works :-)

var createdPath = ModelContext.Paths.FromSqlRaw("AddNodeWithPathProc  {0}, {1}", nodeTitle, parentPathString).ToList();

这篇关于如何通过FromSqlRaw在EF Core 3.0中调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！