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

linqodatawcf-data-services

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?

Best Answer

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

Related Solutions

C# – Return anonymous type results

I tend to go for this pattern:

public class DogWithBreed
{
    public Dog Dog { get; set; }
    public string BreedName  { get; set; }
}

public IQueryable<DogWithBreed> GetDogsWithBreedNames()
{
    var db = new DogDataContext(ConnectString);
    var result = from d in db.Dogs
                 join b in db.Breeds on d.BreedId equals b.BreedId
                 select new DogWithBreed()
                        {
                            Dog = d,
                            BreedName = b.BreedName
                        };
    return result;
}

It means you have an extra class, but it's quick and easy to code, easily extensible, reusable and type-safe.

Sql – How to use OData Expand like a SQL join

The right way to do this would be something like:

http://odata.stackexchange.com/stackoverflow/atom/Users(1569)?$expand=Comments

The problem is that there seem to be no users in the data source (don't know why), so the above query will return a 404. But it is the right syntax.

The idea is that if you want information about just one user you "navigate" to it by using the /Users(1569) (the stuff in parethesis is the primary key of the entity set). Then if you also want to include all the comments, you simply add $expand=Comments. If you want just the comments and not the information about the user you can do /Users(1569)/Comments.

Note that the service you used doesn't define navigation properties, so the above won't work as "joins" are not really supported. But the stackexchange odata endpoint does have the navigation properties defined.

Basically the joins are defined on the server/service so that the client doesn't have to know which column is a foreign key to which primary key.

It also helps with data sources which don't use relational databases as their storage, as it doesn't force them to create fake foreign keys.

You can expand down further "layers" of the graph. If the entity returned in the expand also defines further navigation properties, then you can specify a comma-separated list of the navigation properties.

Here's an example for a made-up service, note that this is expanding each customer in the collection, which is similar to a multiple join.

.../Customers?$expand=Orders,OrderDetails

Best Answer

Related Solutions

C# – Return anonymous type results

Sql – How to use OData Expand like a SQL join

Related Topic