Odata to Sql Query

[rbalagangadharan]

Hi,

Is it possible to convert ODataQueryOptionParser or ODataQueryOptions to SQL query.

If we have static tables, we can use EF core, which magically converts the OData to SQL. In our case all the tables are dynamic and can't use any ORM.

We tried using the libraries like DynamicODataToSql, but it has limitations.

So, wondering if SQLKATA supports any like this.

[fairking]

I am not 100% sure what is your question, but just for reference I managed to implement odata endpoints using sqlkata query builder and entity framework. See the example bellow:

public class ProductsController : ODataController
{
        private readonly MyDbContext _db; // You must register ProductOdataModel in your db context.

        public ProductsController(MyDbContext db)
        {
                _db = db;
        }

        [EnableQuery]
        public IQueryable<ProductOdataModel> Get()
        {
                var compiler = new SqlServerCompiler();
                var query = SqlKata.Query("Products").Select("Id", "Name"); // Your custom query
                var sql = compiler.Compile(query);
                return _db.Set<ProductOdataModel>()
                        .FromSqlRaw(sql.Sql, sql.NamedBindings.Select(x => new SqlParameter(x.Key, x.Value)).ToArray()) // Returns IQueryable<ProductOdataModel>
                        .AsNoTracking(); // Stop tracking changes
        }
...
}

Please note, that ProductOdataModel is not necessary an Entity, it could be any result (DTO) of the query, which you must register in order to make it work (in your DbContext: builder.Entity<ProductOdataModel>()). A little bit ugly but I think it would do the job. And of course you can use your regular entities as well.

[rbalagangadharan]

@fairking Thanks for the update.

In my case we don't have the pre-defined Entities like ProductOdataModel at compile time. The Entity is dynamic which comes to know at runtime. So, registering the Entity at compile time is not possible as the Entity is defined at run time.

[fairking]

Based on my knowledge it is not possible, because you have to define models as a $metadata response. See examples bellow:

• https://services.odata.org/V3/Northwind/Northwind.svc/$metadata#Orders
• https://services.odata.org/TripPinRESTierService/$metadata#Airlines

[fairking]

However there is something like Open Types. I would recommend you to read this: https://learn.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/use-open-types-in-odata-v4
But I am not sure how to combine that with EF and SQLKata. IQueriable<T> requires T in order to determine the $metadata. In this case I assume you must build a custom response manually. Or even give up with EF and build your own SQLKata query based on the OData request, which is a not trivial task to do I suppose.

[rbalagangadharan]

Thanks for the update

[fairking]

Just bear in mind I am not saying there are no other solutions. There could be some package somewhere which handles OData query parameters and converts them into SQLKata query. But I haven't seen such so far.