将OData查询转换为SQL [英] Translate OData queries to SQL

问题描述

我在ODataController中，我有一个ODataQueryOptions(以及所有其他内容).

I'm in an ODataController and I have an ODataQueryOptions (and all the rest of it).

我知道数据的来源是SQL Server中的一个表.

I know that the source of the data is a table in SQL Server.

但是，我的EF模型(由WEB API框架用来将HTTP请求路由到我的控制器)是无类型的，即我没有POCO类.

However, my EF model (that was used by the WEB API framework to route the HTTP request to my controller) is typeless, i.e., I don't have a POCO class.

通常-如果我确实有POCO类-该框架将神奇地从OData查询编写SQL，以便分页，过滤和排序在数据库上完成，而不是在Web服务器上的内存中完成.您可以在DbContext上使用Database.Log来查看从涉及POCO类的LINQ语句生成的SQL.

Normally -- if I did have a POCO class -- the framework would magically write SQL from the OData query so that the paging, filtering, and sorting is done on the database and not in memory on the web server. You can use Database.Log on the DbContext to see the SQL that's generated from LINQ statements involving the POCO classses.

我该如何做到这一点?我有EF模型，有OData请求，但是如何结合使用它们来查询SQL Server?

How do I get this to happen for me? I have the EF model, I have the OData request, but how do I combine them to query SQL Server?

推荐答案

我写了一个 package 到将OData查询转换为可直接查询基础数据库的IQueryable对象.您仍然需要提供有关表的元数据，但是可以在运行时传递它，而不需要POCO.

I wrote a package to convert OData queries into IQueryable objects that can be used to directly query the underlying database. You still need to provide metadata about the table, but it can be passed at runtime rather than requiring a POCO.

就生成SQL而言，返回的IQueryable对象是EntityFramework ObjectQuery对象，因此您可以调用ToString()来显示SQL，但是当前无法检索参数和关联值，因此最好使用生成的IQueryable直接查询数据库.

As far as generating SQL goes, the returned IQueryable objects are EntityFramework ObjectQuery objects, so you can just call ToString() to show the SQL, but there is currently no way to retrieve the parameters and associated values, so you're just better off using the generated IQueryable to directly query the database.

这篇关于将OData查询转换为SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！