OT: LINQ to SQL help

MBursill posted on Wednesday, December 17, 2008

Slightly off topic post, but I hope somebody can help me with this.

I'm using the CSLA framework to do something similar to what Rocky described in a recent blog post: http://www.lhotka.net/weblog/PermissionbasedAuthorizationVsRolebasedAuthorization.aspx

Instead of calling them permissions, I call them tasks, but same thing.

I have three tables, Users, Roles, and Tasks. A user belongs to one or many roles, and a role belongs to one or many tasks. This of course results in many to many relationships between users and roles, plus roles and tasks. I used the tables UserRoles and RoleTasks to resolve the Many to Many's.

I'm using LINQ to SQL, and I want to avoid T-SQL (long story as to why, but it's not an option).

The SQL to grab a task when you know of the user is:

SELECT Tasks.TaskName
FROM Users INNER JOIN (Tasks INNER JOIN ((Roles INNER JOIN RoleTasks ON Roles.RoleName = RoleTasks.RoleName)
INNER JOIN UserRoles ON Roles.RoleName = UserRoles.RoleName) ON Tasks.TaskName = RoleTasks.TaskName) ON Users.Username = UserRoles.UserName
WHERE (((Users.Username)="admin"));

I'm absolutely lost as to how to do the equivalent LINQ expression against my LINQ to SQL entities. Can somebody point me in the right direction?

Thanks.

-Mike.

KJosh replied on Thursday, December 18, 2008

Hi,
 Write Linq query like the below:
DatabaseContext ctx = new DatabaseContext();
var q = (from u in ctx.Users
    Join t in Tasks on (your join conditions)
    Join r in ctx.Roles on (your join conditions like t.col1 equals r.col1)
    Join ur in ctx.UserRoles on new { ur.RoleName, ur.UserName} equals new { ur.RoleName,     U.UserName}
    Join rt in ctx.RoleTasks on t.TaskName equals rt.TaskName
    Where u.UserName.Contains("admin")
    select new { u.columnname, .... })
Hope this helps

MBursill replied on Monday, December 22, 2008

Thanks, I was able to figure it out from this. :-)

For anyone who's interested:

var data = from t in ctx.DataContext.Tasks
           join rt in ctx.DataContext.RoleTasks on t.TaskName equals rt.TaskName
           join r in ctx.DataContext.Roles on rt.RoleName equals r.RoleName
           join ur in ctx.DataContext.UserRoles on r.RoleName equals ur.RoleName
           join u in ctx.DataContext.Users on ur.Username equals u.Username
           where u.Username == "admin"
           select t;

-Mike.