Filtering on related entities properties with OData data source

Question

Originally we were using a Sql Server data source and for some queries in Preprocess query we do something like this:

 

        Private Sub ItemsByPlant_PreprocessQuery(PlantId As System.Nullable(Of Integer), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Item_))
            query = From i In query
                    Where i.ItemsPlants.Where(Function(ip) ip.Plant.Id = PlantId).Any = True
                    Select i
                    Order By i.Code
       End Sub

ItemsPlants is a collection property of Item_ entity.

This allows filtering by related entities properties; the query searches for Item_ records where Item_ has a relation with the Plant entity with specified id (PlantId).

Now we moved to a OData data source for some reasons and this 'query modification' causes a 'not supported' error.

Same things works with WCF RIA services data sources.

Is there any way to overcome the problem?

Thanks.

---

Robin Poltronieri

Answer 1

I would suggest to go through the LINQ query consideration with OData. 

Read this article on MSDN : http://msdn.microsoft.com/en-us/library/ee622463.aspx

For the OData the Linq should generate query URI e.g.: http://localhost:12345/northwind.svc/Orders()?$filter=Freight gt 30M.

So now you must consider changing a lot of things for usage of linq with your new datasource.

Answer 2

Thank you for your answer.

Checked out the page you suggested; in the section 'unsupported LINQ methods' I can see that, for example, the any set operator is clearly not supported. But... it seems that this limit is for .net framework 4.5 and lower; if you select 'WCF Data services 5' version (using the drop down filter on top of page) you can see that the any operator is no more an unsupported operator.

So now my question would be: am I using the version 5 already? If not, is there any way to 'upgrade' to this version? (the OData assembly version is 5.x).

---

Robin Poltronieri

Answer 3

Please could you update your query to

"Where i.ItemsPlants.Any(Function(ip) ip.Plant.Id = PlantId) = True"

Answer 4

Thank you Mr. Eda.

It works and seems that the produced sql server query is correct.

---

Robin Poltronieri