Linq to Entities : Many-to-Many join a Table and a View

Question

I have several tables and a view :

Tables :
- aspnet_Roles (with RoleId & other columns)
- Status (with StatusId & other columns)
- RoleStatus (with RoleId and StatusId and relationships)

When I create the Linq To Entities EDMX file, it creates 2 entities with a Status List property in aspnet_Roles and a aspnet_Roles list in Status.
So far OK.

But now I have a view with a StatusId column.

What I want is to create a LINQ query with a join between that view and the aspnet_Roles table to add a RoleName column in the result set.

In SQL it would be something like:

Select a.*, aspnet_Roles.RoleName From SearchView a
INNER JOIN RoleStatus ON a.StatusId = RoleStatus.StatusId
INNER JOIN aspnet_Roles ON RoleStatus.RoleId = aspnet_Roles.RoleId

But I can't find how to do that in LINQ to Entities. The problem is that I never have access to RoleId or StatusId as these fields are "replaced" by the aspnet_Roles and Status list properties.

So I get a LINQ like that :

from avis in ctx.SearchView 
join joinedRoles in ctx.aspnet_Roles
on avis.StatusId equals joinedRoles.Status. (and then what ??)

As Status is a list I can't find nothing that matches StatusId.

I think I'm doing things wrong but I don't know where.

Answer 1

First, I recommend upgrading to EF4; they added "foreign key associations", so we now have the *Id fields in addition to the references.

Secondly, is your view mapped to an entity in the designer? It should be possible to add an association between the view and the Status table. (I have not actually done this with a view, but I believe this would work).