如何通过 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 替换 Single 有效:-)

Replacing Single with ToList works :-)

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

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