CODEWITHSUNDEEP
asp.net-web-apiodataasp.net-web-api-odata
Richard Barraclough
Richard Barraclough

Reputation: 2964

Translate OData queries to SQL

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

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

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.

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.

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?

Upvotes: 3

Views: 6754

Answers (1)

Dale Anderson
Dale Anderson

Reputation: 1701

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.

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.

Upvotes: 0

Related Questions