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


    ...
}

Answer 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.