How do i get an inner join in a WCF Data Service

Question

Lets say i have 2 tables, table1 and table2, with a shared key "id"

if i want an inner join of those two tables using sql, i'd do something like

select id, x, y, z
from table1
inner join table2
on table1.id = table2.id

I now get rows in table 1 that only intersect occur in table 2.

how do i get the equivalent in wcf data service/odata linq syntax?

i'm expecting something like:

var q = (from t in svc.Table1.Expand("Table2")
    where t.Table2.Any()
    select t) as DataServiceQuery<Table1>;

but that gets me an exception about Any().
I've tried .Join and that isn't supported either. I've tried .Count and that fails too.
.Intersect looks like it only takes another enumerable, so that doesn't look like what i want...

i think i'm missing something really obvious or simple...

Edit: this appears to be a dup of this How do I use OData Expand like a SQL join?

Sainfoin

possible duplicate of How do I use OData Expand like a SQL join? – Sainfoin

Answer 1

Take a look at the answers to this type of question. The current version of WCF Data Services (OData) does not support joins even if your underlying data contract does (i.e. if you're layering on top of Entity Framework 4 for instance).

Parka

it appears the mostly correct answer so far is "you can't". oh well. From Vitek's answer it looks like there might at least be hope for some of it in the future. – Sainfoin

Just stumbled across this myself. What a nightmare. How did this code ship without JOIN support? I could cry. – Marigolde

Answer 2

Currently the OData protocol (and thus WCF Data Services) doesn't support any/all operations. It also doesn't support arbitrary joins, although some joins can be expressed as navigations. Your query is currently not supported, but we're looking into adding support for the any/all operations. Take a look at this proposal if that would fulfill your needs: http://www.odata.org/blog/support-for-any-and-all

Woollyheaded

it would probably help. i don't think that would cover ALL of the things i need, but it would cover some of the things i was trying to do. – Sainfoin

Answer 3

The more recent releases of WCF Data Services now include Any/All support. See What's New in WCF Data Services 5.0

Deuce

Answer 4

Is WCF in play here? To link two objects from two tables/lists, I'd do this:

var result = 
  from o1 in Table1
  join o2 in Table2 on o2.id equals o1.id

Horseman

i'll add more detail. this is through a wcf data service, which doesn't support everything in linq. – Sainfoin

I don't know if it depends on the OData implementation/config but I've had trouble trying to make joins with OData. Either it isn't supported out of the box or not supported at all. – Ashia