How can I convert OData to ServiceStack AutoQuery?

Question

I have a Web API project that uses OData to query the database. But now I want to transform this project using ServiceStack AutoQuery. The problem is that I don't have access to the frontend. So the incomming requests are using the OData filter expressions. Is there a way to transform the ODataQueryOptions so I can query that database using AutoQuery or OrmLite?

Edit (@mythz):

so I should transform the url to an OrmLite query? For example:

Categories?$filter=(Id eq 11) and (Deleted eq false)&$expand=CategoryTranslations

[HttpGet]
public IQueryable<Category> Categories(ODataQueryOptions odataQueryOptions)
{
    var query = ErpDb.From<Category>();

    #region ordering

    if (odataQueryOptions.OrderBy != null)
    {
        query.UnsafeOrderBy(odataQueryOptions.OrderBy.RawValue);
    }

    #endregion

    #region paging

    var inlineCount = ErpDb.Count(query);

    if (odataQueryOptions.Skip != null)
    {
        query.Skip(odataQueryOptions.Skip.Value);
    }

    if (odataQueryOptions.Top != null)
    {
        query.Take(odataQueryOptions.Top.Value);
    }

    #endregion


    ...
}

I think one way is to write your own converter – Betsq9 Jun 20 '22 at 08:20 — Betsq9, Jun 20 '22 at 08:20

score 1 · Accepted Answer · answered Jun 20 '22 at 10:10

1

AutoQuery RDBMS uses OrmLite to query an RDBMS directly, so you'd need to essentially query OData APIs to populate an InMemory SQLite database on Startup.

Alternatively you can use an AutoQuery Memory Data Source to call a 3rd Party API and return a List of POCOs which you can then query with AutoQuery, e.g:

Plugins.Add(new AutoQueryDataFeature { MaxLimit = 100 }
    .AddDataSource(ctx => ctx.MemorySource(() =>  
      $"https://host/{ctx.Request.GetParam("table")}?{MyODataQuery(ctx.Request)}"
       .GetJsonFromUrl(req => req.UserAgent="AutoQuery").FromJson<List<Model>>(),
      HostContext.LocalCache, 
      TimeSpan.FromMinutes(5))
);

The example also shows how you can cache the results to improve performance.

answered Jun 20 '22 at 10:10

mythz

141,670
29
246
390

I've did an edit on my original question. – Nesse Jun 20 '22 at 11:26
@Nesse Your question doesn't looks like it's using AutoQuery, it's just using OrmLite to implement an OData API which I'm assuming could work if you return `ErpDb.Select` or `ErpDb.LoadSelect` to include POCO references. – mythz Jun 20 '22 at 12:29
1

For now I only can change the backend and I was wondering what would be the best approach. In the end I will alter the whole project using AutoQuery as API platform. – Nesse Jun 20 '22 at 12:57
How should I handle multi-level includes? I know that OrmLite only goes 1 level down when using LoadSelect. `$expand=StockItems/StockUnits/WarehouseLocation` – Nesse Jun 20 '22 at 12:59
1

@Nesse Only way would be to create a [Custom AutoQuery impl](https://docs.servicestack.net/autoquery-rdbms#custom-autoquery-implementations) to populate any additional DTO Data manually, [Merging POCO results](https://docs.servicestack.net/ormlite/reference-support.html#merge-disconnected-poco-result-sets) may be useful for this. – mythz Jun 20 '22 at 13:02

How can I convert OData to ServiceStack AutoQuery?

1 Answers1