包含在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< Entity>实体的正常方式是，我包含另一个实体，例如：

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）

，这也有效。两者都向我返回与AnotherEntity连接的对象的相同集合。然后，我使用了一个存储过程，该存储过程由相同的查询 SELECT * FROM实体命名为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）

猜怎么着？这也有效。显然，它给了我相同的输出，但没有加入AnotherEntity。但是，如果我尝试像这样添加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 .otherother）

我正在获取：

FromSqlRaw或FromSqlInterpolated是通过不可组合的SQL
以及包含查询的SQL调用的。考虑在FromSqlRaw或FromSqlInterpolated方法之后调用 AsEnumerable
在客户端执行
组合。

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）相同。

我找不到我可以做或不能做的证明

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.

此外，如果还有其他方法可以使用存储的方法来获取连接的实体，那么使用EF Core 3.1可以做到这一点。我可能会接受它作为我的问题的解决方案。

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文档中-原始SQL查询-使用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（哈希联接））


• 在SQL Server上，一个 ORDER BY 子句不与 OFFSET 0或TOP 100 PERCENT 一起使用在 SELECT 子句

• 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 或<$之后，立即使用 AsEnumerable 或 AsAsyncEnumerable 方法c $ c> FromSqlInterpolated 方法，以确保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屋！