I have a schema like so. Menu->Pages->PageRoles->ASPNetRoles
Menu has a CategoryID.
I want to return all Menu items with a CategoryID of 6.
Some Menu items have a foreigh key of PageID. Pages can have 1 or more roles against them. I can check the currently logged in users roles and make sure that they are in the results by joining the tables.
I want to return all Menu items with a CategoryID of 6 and for those that have PageID's the users role must be in those roles assigned to the page.
The only way I can think is to do a union but when I do this in Subsonic it fails. The following works.
var dd = (from menu in Menu.All().Where(x => x.PageID == null && x.CategoryID == 6) select menu);
var ss = from menu2 in Menu.All()
join pages in WebPage.All() on menu2.PageID equals pages.ID
join pagesRoles in PageRole.All() on pages.ID equals pagesRoles.PageID
join roles in aspnet_Role.All() on pagesRoles.RoleId equals roles.RoleId
where Roles.GetRolesForUser().Contains(roles.RoleName) &&
menu2.CategoryID == 6
select menu2;
How do I combine the results?
Doing this it fails:
var dd = (from menu in Menu.All().Where(x => x.PageID == null) select menu).Union(
from menu2 in Menu.All()
join pages in WebPage.All() on menu2.PageID equals pages.ID
join pagesRoles in PageRole.All() on pages.ID equals pagesRoles.PageID
join roles in aspnet_Role.All() on pagesRoles.RoleId equals roles.RoleId
where Roles.GetRolesForUser().Contains(roles.RoleName)
select menu2);
EDIT:
I can get the results in SQL via LEFT OUTER JOINS (See Below) but again translating this into LINQ/Subsonic fails.
SELECT * FROM MENU M
LEFT OUTER JOIN WEBPAGE P
ON P.ID = M.PAGEID
LEFT OUTER JOIN PAGEROLES R
ON R.PAGEID = P.ID
LEFT OUTER JOIN ASPNET_ROLES A
ON A.ROLEID = R.ROLEID
WHERE ((CATEGORYID = 1) OR ( CategoryID = 1 AND A.ROLENAME IN ('ADMINISTRATOR','USER')))
Even something simple like this fails
var resu = from p in db.Menus
join pages in db.WebPages on p.PageID equals pages.ID
into temp from pages in temp.DefaultIfEmpty()
select p;