EF核心可以从存储过程/视图/表值函数返回IQueryable吗？

Question

我们需要将ODATA-V4查询搜索、order子句直接传递给数据库。

情况如下：

1. 表之间有连接，我们使用SQL调用(内联)表值函数来获取所需的记录。
2. ODATA，其中的子句需要应用于结果集，然后我们应用分页跳转、接受和订购。

我们从Dapper开始，但是Dapper只支持IEnumerable，因此Dapper将从DB中获取整个记录，然后只有OData (查询选项ApplyTo)分页应用，从而破坏性能增益:-(

        [ODataRoute("LAOData")]
        [HttpGet]
        public IQueryable<LAC> GetLAOData(ODataQueryOptions<LAC> queryOptions)
        {
            using (IDbConnection connection = new SqlConnection(RoutingConstants.CascadeConnectionString))
            {
                var sql = "<giant sql query";
                IQueryable<LAC> iqLac = null;
                IEnumerable<LAC> sqlRes = connection.Query<LAC>(sql, commandTimeout: 300);
                **IQueryable<LAC> iq = sqlRes.AsQueryable();
                iqLac = queryOptions.ApplyTo(iq) as IQueryable<LAC>;
                return iqLac;**
            }
        }

我们在存储过程上看到的大多数示例都支持视图返回列表。https://hackernoon.com/execute-a-stored-procedure-that-gets-data-from-multiple-tables-in-ef-core-1638a7f010c

我们是否可以配置EFCore2.2来返回IQueryable，以便ODATA可以进一步过滤，然后只产生想要的计数，比如10？

Answer 1

好吧，是和不是。您当然可以返回一个IQueryable，而且您似乎已经在这样做了。而且，您当然可以在内存中通过LINQ对该IQueryble进行进一步的查询。

我认为您真正要问的是，如果您可以在数据库级别进一步查询，那么只需要从数据库返回最终的结果集。答案是很难拒绝。必须首先计算存储过程。完成此操作后，所有结果都将从数据库中返回。您可以在内存中进一步筛选，但对于数据库来说已经太晚了。

尽管如此，您应该理解，OData与使用存储过程之类的东西的想法根本不兼容。重点是通过URL参数来描述查询--整个查询。您可以使用视图代替，但是存储过程不应沿OData使用.

Answer 2

EF不能从存储过程返回IQueryable，因为数据库引擎本身并不提供有选择地查询或操作脚本执行的机制，例如，您不能在SQL中执行以下操作：

SELECT Field1, Field2
EXEC dbo.SearchForData_SP()
WHERE Field2 IS NOT NULL
ORDER BY Field3

存储过程是引擎的黑匣子，因此可以在SPs中使用某些类型的表达式和操作，这些表达式和操作不能在基于普通集的SQL查询或表达式中使用。例如，您可以执行其他存储过程。在处理结果之前，必须完整地执行SPs。

如果数据库引擎本身不能做任何事情来优化存储过程的执行，那么ORM框架将很难做到这一点。这就是为什么大多数通过EF执行SPs的文档和示例都返回一个List，因为这表明该列表的全部内容都在内存中，将List转换为带有.AsQueryable()的IQueryable并不会改变数据是在该list对象中维护的事实。

1. 表之间有连接，我们使用SQL调用(内联)表值函数来获取所需的记录。

您在这里描述的类似于OData和EF试图为您提供的组合复杂查询的机制。为了充分利用OData和EF，您应该考虑用linq语句复制或替换您的TVF。EF与RDBMS无关，因此它试图使用和执行可应用于许多数据库引擎的通用标准，而不仅仅是SQLSERVER。当涉及到CTE、TVFs和SPs时，每个数据库引擎的实现和语法变得更加具体，甚至在某些情况下对特定版本也是如此。与其试图成为每个人的一切，EF团队还必须执行一些限制，这样他们才能保持他们为我们提供的服务的质量。

然而，有一种愉快的媒介是可以实现的，在这里你可以利用这两个引擎的力量：

1. 设计SPs，以便将筛选变量作为参数传递，并将对存储过程的依赖限制在输出结构与通常需要的结构相同的情况下。然后，您可以在Action中将SP公开为一个OData端点，被调用者可以将参数值直接传递给SP。

- You can still wrap the response in an `IQueryable<T>` and decorate this action with the `EnableQuery` attribute, this will perform in memory $select, $expand and simple $filter operations but the service will still load the **entire recordset** into memory before constructing the response payload. This mechanism can still reduce bandwidth between the server and the client, just not between the database and the service layer.
- Make different versions of your SP if you need to have different result structures for different use cases.

​

1. 只有当查询太复杂，无法使用Linq表示，或者需要使用表提示、CTE、递归CTE或无法在linq中轻松复制的窗口函数时，才使用TVFs或Views。

- In many cases where CTEs (non recursive) are used, the expression can be easier to construct in Linq.
- To squeeze the most performance from indexes you can use _Table Hints_ in SQL, because we don't have tight control over how our Linq expressions will be composed into SQL it can take a lot of work to construct some queries in a way that the database can optimise them for us. In many scenarios, as with CTEs above, going through the process of rewriting your query in Linq can help avoid scenarios where you would traditionally have used table hints. 

    - There are limits, when you want or need to take control, using specialised SQL Server concepts that EF doesn't support, you are making a conscious decision to have one and not the other.  

​

​

我不同意OData和存储过程根本上是不兼容的--在很多情况下，两者相处得很好，但是您必须找到平衡。如果您觉得需要遍历查询选项，如$select、$expand $filter、$top、$skip.对于存储过程，要么将实现更改为纯用Linq构造(所以没有SP)，要么更改客户端实现，以便传递可以在SP中直接处理的正式参数。