Reengineer SQL into SubSonic LINQ

Question

I have 11 records in the Menu table, only 1 with a PageID set however if I use

var test = Menu.All().Where(
   x => x.WebPages.Any(
      pages => pages.Roles.Contains(Roles.GetRolesForUser()[0])

I get 11 records as the SQL run is this

SELECT [t0].[CategoryID], [t0].[CreatedBy], [t0].[CreatedOn], 
  [t0].[ID], [t0].[ImageID], [t0].[ImageIDHover], [t0].[Locale], 
  [t0].[ModifiedBy], [t0].[ModifiedOn], [t0].[OrderID], [t0].[PageID], 
  [t0].[ParentID], [t0].[Title], [t0].[URL], [t0].[Visible]
FROM [dbo].[Menu] AS t0
WHERE EXISTS(
  SELECT NULL 
  FROM [dbo].[WebPage] AS t1
  WHERE ([t1].[Roles] LIKE '%' + 'User' + '%')
  )

If I run this I get the 1 record

var test = Menu.All().Where(
   x => x.WebPages.Any(
      pages => pages.Roles.Contains(
         Roles.GetRolesForUser()[0]) && pages.ID == x.PageID));

The SQL for this is

SELECT [t0].[CategoryID], [t0].[CreatedBy], [t0].[CreatedOn], 
   [t0].[ID], [t0].[ImageID], [t0].[ImageIDHover], [t0].[Locale], 
   [t0].[ModifiedBy], [t0].[ModifiedOn], [t0].[OrderID], [t0].[PageID], 
   [t0].[ParentID], [t0].[Title], [t0].[URL], [t0].[Visible]
FROM [dbo].[Menu] AS t0
WHERE EXISTS (
  SELECT NULL 
  FROM [dbo].[WebPage] AS t1
  WHERE (([t1].[Roles] LIKE '%' + 'User' + '%') AND 
        ([t1].[ID] = [t0].[PageID]))
)

The problem with Any() is that in the SQL as long as one record exits, doesn't matter which record it will return data.

I think effectively I am wanting an UNION SQL like below but I don't know how I re-engineer that into C#/Subsonic

select m.* from menu m where pageid is null
union
select m.* from menu m   
join webpage p
on p.id = m.pageid
where p.roles like '%User%'

I want to return all menu records and for those with a PageID set that the corresponding WebPage has the user's role in it. If the user's role is not in the WebPage then I don't want to see it in my results.

Here are my cutodwn classes that Subsonic generates

public partial class Menu: IActiveRecord
    {
       public int ID {get; set;}
       public int CategoryID {get;set;}
       public bool Visible {get;set;}
       public int PageID {get;set;}
       public IQueryable<WebPage> WebPages
        {
            get
            {

                  var repo=NorthCadburyWebsite.Models.WebPage.GetRepo();
                  return from items in repo.GetAll()
                       where items.ID == _PageID
                       select items;
            }
        }
}

public partial class WebPage: IActiveRecord
    {
       public int ID {get;set;}
       public string Roles {get;set;}
}

Answer 1

It sure seems like you could just add an alternative test to the where clause to do this.

var test = Menu.All()
               .Where(x => x.PageID == null
                       || x.WebPages
                           .Any(pages => pages.Roles.Contains(Roles.GetRolesForUser()[0])
                                            && pages.ID == x.PageID));

EDIT:

Try using the the Intersect method to see if there is an overlap between the roles in the DB and the roles for the user.

var test = Menu.All()
               .Where(x => x.PageID == null
                       || x.WebPages
                           .Any(pages => pages.Roles.Intersect(Roles.GetRolesForUser().Split(','))
                                            && pages.ID == x.PageID));