Query many-to-many tables

[Marcel]

Hello,

 I just strated out with the OpenAccess framework and modeled, the following data structure:

User <--> UserRole <--> Module

So a many to many relation exists between User and UserRole and a many to many relation exist between UserRole and Module.​

The OpenAcess framework has hidden the two many-to-many junction tables.

 

I now want to run a query which gives me all Modules for a given user.

When I execute the following query I get the runtime exception "Join left side undefined".

 

List<Module> modules = (from geb in Context.Gebruikers
                        from gr in Context.GebruikerRoles
                        from mod in Context.Modules
                        where geb.GebruikerID == gebruikerId
                        select mod).ToList();

 

What am I missing here?

Marcel 

 

[Viktor Zhivkov]

Hi Marcel,

I am afraid that the best way to express a query spanning two many-to-many relationship is to hand craft a stored procedure. LINQ is not particularly suited for such scenario and you may have to split the query into several pieces, execute them one by one and merge the results in-memory which in most cases is a lot slower and inefficient.
Here is a link to our online documentation that can guide you how to execute a stored procedure.

If you need any further assistance do not hesitate to contact us again.

Regards,
Viktor Zhivkov
Telerik