Aegis Aegis - 2 months ago 16
C# Question

Combining LINQ with OData AddQueryOption

Limitations of OData (listed here) prevent me from adding dynamic where clauses to the data set from my OData source. I found a previous post that answered my query for dynamic filters, which is to use the AddQueryOption method with a custom built query string. However there does not seem to be a way to combine this query string with a standard LINQ query.

Using the aforementioned method produces a valid query:

https://api-dev.company.com/odata/Assets?$filter=(Levels/any(l:l/LevelId eq 18)) or (Levels/any(l:l/LevelId eq 19))


The reason this has to be produced dynamically is becuase there are a variable number of level filters which cannot be determined before run time and simply using multiple Where clauses produces "and" filters instead of "or" filters, like this:

https://api-dev.company.com/odata/Assets?$filter=(Levels/any(l:l/LevelId eq 18)) and (Levels/any(l:l/LevelId eq 19))


My current attempts to use LINQ after this method produces an output of:

https://api-dev.company.com/odata/Assets?$filter=DisplayOnline and Status eq Tools.Services.Models.EPublishStatus'Active', and (Levels/any(l:l/LevelId eq 18)) or (Levels/any(l:l/LevelId eq 19))


Note that with the second query to only thing wrong with it is the comma between the Levels filters and the rest of the filters.

The additional Where clauses are as follows:

// Filter by assets that can be displayed online
assets = assets.Where(a => a.DisplayOnline);

// Filter by assets that are active
assets = assets.Where(a => a.Status == EPublishStatus.Active);


I would to know if there is a way to manually edit the string or if there is a proper way to combine both query string generation methods. Thanks for your time.

Answer

After some trial and error I found that utilising the solution answered here helped to work around this issue. The solution for me was to build the dynamic filter query AFTER the LINQ Where clauses and then to build an entirely new query using the combined result of the two:

// Filter by assets that can be displayed online
assets = assets.Where(a => a.DisplayOnline);

// Filter by assets that are active
assets = assets.Where(a => a.Status == EPublishStatus.Active);

// Addtional filters..
assets = assets.Where(a => x == y);

// Get the string for the dynamic filter
string dynamicQuery = GetDynamicQuery(assets);

// Get base OData Asset call (https://api-dev.company.com/odata/Assets)
IQueryable<Asset> serviceCall = _container.Assets;

// Apply the new dynamic filter
serviceCall = serviceCall.AddQueryOption("$filter", dynamicQuery);

// Resultant OData query (Success!)
https://api-dev.company.com/odata/Assets?$filter=DisplayOnline and Status eq Models.Status'Active' and (Levels/any(l:l/LevelId eq 18)) or (Levels/any(l:l/LevelId eq 19))

The trick here was to ensure that there was only one "$filter" option in the query, otherwise an exception would be thrown.