title: Subsonic 3 Union Possible?
body: <p>I have a schema like so. Menu->Pages->PageRoles->ASPNetRoles</p>
  
  <p>Menu has a CategoryID.</p>
  
  <p>I want to return all Menu items with a CategoryID of 6.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>The only way I can think is to do a union but when I do this in Subsonic it fails. The following works.</p>
  
  <pre><code>    var dd = (from menu in Menu.All().Where(x =&gt; x.PageID == null &amp;&amp; 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) &amp;&amp;
                menu2.CategoryID == 6
                select menu2;
  </code></pre>
  
  <p>How do I combine the results?</p>
  
  <p>Doing this it fails:</p>
  
  <pre><code>var dd = (from menu in Menu.All().Where(x =&gt; 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);
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I can get the results in SQL via LEFT OUTER JOINS (See Below) but again translating this into LINQ/Subsonic fails.</p>
  
  <pre><code>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')))
  </code></pre>
  
  <p>Even something simple like this fails</p>
  
  <pre><code>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;
  </code></pre>