在 EF Core 3.1 中包含 FromSqlRaw 和存储过程 [英] Include with FromSqlRaw and stored procedure in EF Core 3.1

问题描述

所以这是交易 - 我目前正在使用 EF Core 3.1，假设我有一个实体:

So here's the deal - I am currently using EF Core 3.1 and let's say I have an entity:

public class Entity
{
    public int Id { get; set; }
    public int AnotherEntityId { get; set; }
    public virtual AnotherEntity AnotherEntity { get; set; }
}

当我访问DbSet时实体正常方式，我包括另一个实体，如:

When I access the DbSet<Entity> Entities normal way, I include AnotherEntity like:

_context.Entities.Include(e => e.AnotherEntity)

这是有效的.为什么不会，对吧?然后我去:

and this works. Why wouldn't it, right? Then I go with:

_context.Entities.FromSqlRaw("SELECT * FROM Entities").Include(e => e.AnotherEntity)

这也有效.两者都返回给我与另一个实体连接的相同对象集合.然后我使用一个存储过程，它由相同的查询 SELECT * FROM Entities 组成，名为 spGetEntities:

and this also works. Both return me the same collection of objects joined with AnotherEntity. Then I use a stored procedure which consists of the same query SELECT * FROM Entities named spGetEntities:

_context.Entities.FromSqlRaw("spGetEntities")

你猜怎么着?这也有效.显然，它给了我相同的输出，但没有加入另一个实体.但是，如果我尝试像这样添加 Include:

guess what? This also works. It gives me the same output but without joined AnotherEntity, obviously. However if I try to add the Include like this:

_context.Entities.FromSqlRaw("spGetEntities").Include(e => e.AnotherEntity)

我得到:

FromSqlRaw 或 FromSqlInterpolated 被不可组合的 SQL 调用并通过查询组成它.考虑调用 AsEnumerable在 FromSqlRaw 或 FromSqlInterpolated 方法执行之后客户端的组合.

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.

即使 _context.Entities.FromSqlRaw("SELECT * FROM Entities") 和 _context.Entities.FromSqlRaw("spGetEntities") 的输出是相同的.

Even though the output of _context.Entities.FromSqlRaw("SELECT * FROM Entities") and _context.Entities.FromSqlRaw("spGetEntities") is identical.

我找不到证明我可以或不能使用 EF Core 3.1 做到这一点的证据，但如果有人能给我任何这种方法的可能性的暗示，那就太好了.

I couldn't find a proof that I can or I can not do this with EF Core 3.1 but if someone could give me any hint of possibility of this approach it would be nice.

此外，如果有另一种方法可以使用存储过程获得加入的实体，我可能会接受它作为我的问题的解决方案.

Also if there is another way to get joined entities using stored procedure I would probably accept it as the solution of my issue.

推荐答案

很快，你不能这样做(至少对于 SqlServer).解释包含在 EF Core 文档中 - Raw SQL Queries - 使用 LINQ 编写:

Shortly, you can't do that (at least for SqlServer). The explanation is contained in EF Core documentation - Raw SQL Queries - Composing with LINQ:

使用 LINQ 进行组合要求您的原始 SQL 查询是可组合的，因为 EF Core 会将提供的 SQL 视为子查询.可以组成的 SQL 查询以 SELECT 关键字开头.此外，传递的 SQL 不应包含任何对子查询无效的字符或选项，例如:

Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. SQL queries that can be composed on begin with the SELECT keyword. Further, SQL passed shouldn't contain any characters or options that aren't valid on a subquery, such as:

• 尾随分号
• 在 SQL Server 上，尾随查询级提示(例如，OPTION (HASH JOIN))
• 在 SQL Server 上，ORDER BY 子句未与 SELECT 子句中的 OFFSET 0 OR TOP 100 PERCENT 一起使用莉>

• A trailing semicolon
• On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
• On SQL Server, an ORDER BY clause that isn't used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause

SQL Server 不允许组合存储过程调用，因此任何将附加查询运算符应用于此类调用的尝试都将导致 SQL 无效.在 FromSqlRaw 或 FromSqlInterpolated 方法之后使用 AsEnumerable 或 AsAsyncEnumerable 方法来确保 EF Core 不会尝试组合一个存储过程.

SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL. Use AsEnumerable or AsAsyncEnumerable method right after FromSqlRaw or FromSqlInterpolated methods to make sure that EF Core doesn't try to compose over a stored procedure.

另外，由于Include/ThenInclude 需要EF Core IQueryable<>, AsEnumerable/AsAsyncEnumerable 等不是一个选项.您确实需要可组合的 SQL，因此无法选择存储过程.

Additionally, since Include / ThenInclude require EF Core IQueryable<>, AsEnumerable / AsAsyncEnumerable etc. is not an option. You really need composable SQL, hence stored procedures are no option.

不过，您可以使用表值函数 (TVF) 或数据库视图代替存储过程，因为它们是可组合的(select * from TVF(params) 或 select * from db_view) .

Instead of stored procedures though, you can use Table-Valued Functions (TVF) or database views because they are composable (select * from TVF(params) or select * from db_view) .

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