System.NotSupportedException: Can only project the last entity type in the query being translated.

Hi,

In OData joining the two tables can be done in two ways: Navigations, something like this: /TyrePatterns(id)/Tyres?$filter=Diameter eq 195 But this assumes you know the id of the TypePattern you're looking for (Which doesn't seem to be what you're looking for)

Or expansions: /TyrePatterns?$expand=Tyres But then you can only apply filters to the TyrePatterns, not to the exapnded Tyres.

So the right way to go about this query is the other way round: /Tyres?$filter=Diameter eq 195&$expand=TyrePattern This will return all tires with Diameter 195 and it will also include their TypePattern (assuming the navigation property is bi-directional. It's not exactly the one you wanted, but it's the closest you can get.

Translated to LINQ this would look something like

from t in Tyres.Expand("TyrePatterns") where t.Diameter == 195 select t

You can then select just the TyrePatterns on the client easily.

The limitation is that you can only apply filter to the last segment in the navigation (the path part of the URL).

I had this idea but unfortunately it's not what I need. I want a column from TyrePatterns table (TyrePattern.Name). There is a one to many relationship between TyrePatterns and Tyres (many Tyres per TyrePattern). By selecting from Tyres and not TyrePatterns, I get duplicate results, because I can't do a Distinct, as using Distinct gives a different run-time error.

Zac 2010-09-16 13:40:32

You could do distinct on the client (it would download a bit more). To run it on the client just add .AsEnumerable() after whatever should run on the server. Everything after the AsEnumerable() will be executed locally on the client (where Distinct is definitely supported).

Vitek Karas MSFT 2010-09-16 16:28:56

Thanks for the answer, I wish I could mark it up as useful! As a solution, this still seems unsatisfactory however; it can mean pulling in a LOT of redundant data (a couple of orders of magnitude more). I also tried to do a group as a means of getting rid of the duplicates, but this gave a not-supported-exception too.

Zac 2010-09-27 10:04:15

If you control the server (which you seem to do), you can create a service operation which will execute the more complex query for you against the server side provider (EF or whatever). It's not as flexible as the URI, but it works. If you service operation returns IQueryable<TEntity> then you can even add more query operators on it just like you would for an entity set. And thus you can provide the basic functionality on the server and leave the filters up for the client for example.

Vitek Karas MSFT 2010-09-27 14:26:23

Sounds like a service operation is the best way to go. Thanks :)

Zac 2010-10-04 08:31:16

ansaurus

tags:

views:

answers:

System.NotSupportedException: Can only project the last entity type in the query being translated.

related questions