Stuck on a LINQtoSQL Query

[daokfella]

I have four tables:

Applications
- ApplicationID
- Name

Roles
- RoleID
- ApplicationID
- Name

Users
- UserID
- Name

UserRoles
- UserID
- RoleID

I'm trying to get applications by UserID but I can't figure out a
simple LINQ statement to do so. Can anybody help me? Basically, we
need to get the UserRoles by UserID, then get the roles from that,
then get the applications from that.

This gets the UserRoles:
var UserRoles = DataContext.Users.Single(u => u.UserID ==
UserID).UserRoles;

How do I get the apps?
var apps = DataContext.Applications.Where(a => a.Roles ...?)

 

[Chris Jobson]

I have four tables:

Applications
- ApplicationID
- Name

Roles
- RoleID
- ApplicationID
- Name

Users
- UserID
- Name

UserRoles
- UserID
- RoleID

I'm trying to get applications by UserID but I can't figure out a
simple LINQ statement to do so. Can anybody help me? Basically, we
need to get the UserRoles by UserID, then get the roles from that,
then get the applications from that.

This gets the UserRoles:
var UserRoles = DataContext.Users.Single(u => u.UserID ==
UserID).UserRoles;

How do I get the apps?

Maybe something like:
var appIDs = DataContext..Roles.Join(userRoles, r => RoleID, ur => RoleID,
(r, ur) => r.ApplicationID).Distinct();
var apps = DataContext.Applications.Where(a =>
appIDs.Contains(a.ApplicationID))

(but I'm just learning LINQ myself and there may well be a better way).

Chris Jobson

 

[Chris Jobson]

Sorry, I should have said:
var appIDs = DataContext..Roles.Join(userRoles, r => r.RoleID, ur =>
ur.RoleID, (r, ur) => r.ApplicationID).Distinct();

Chris Jobson