LINQ IQueryable Help

Question

I have a Menu class that has a IQueryable property called WebPages. In the following statement I am returning Menu items based on a match but I need to include the Webpages property. Here is what I have at the moment.

var allCategories = Menu.All().Where(x => x.CategoryID == 4 && x.Visible)

I need to extend it to check a property in the WebPage class, something like this..

var allCategories = Menu.All().Where(x => x.CategoryID == 4 && x.Visible && x.WebPages.Roles.Contains(User.Identity.Name))

That won't compile but I hope you get the jist of what I am trying to do.

NOTE: The Webpage property is filled by the PageID not CategoryID but not sure if that makes a difference??

Here are a brief outline of my classes.

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

Try changing the

public IQueryable<WebPage> WebPages

to

public IEnumerable<WebPage> WebPages

I think LINQ queries return IEnumerable...

Answer 2

This should do it for you mate. You just need to say WebPages.Any, this will return true if any menus contain a webpage with your specified role.

var allCategories = menus.Where(menu => menu.CategoryID == 1 && menu.Visible && menu.WebPages.Any(webPage => webPage.Roles.Contains(roleToSearchFor)));

So the key bit that you need to add is this.

menu.WebPages.Any(webPage => webPage.Roles.Contains(roleToSearchFor))

Using the Any() function is very efficient as it will stop looking as soon as it finds a match.

If you used Where() and then Count() it would iterate through all the Webpages to find all matches and then iterate through the results to count them, so that would be much less efficient.

Below is a full source example for you to try.

    namespace DoctaJonez.TestingBrace
    {
        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; set; } 
        } 

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

        public static class Launcher
        {
            /// <summary>
            /// The Main entry point of the program.
            /// </summary>
            static void Main(string[] args)
            {
                Menu myMenu1 = new Menu
                {
                    ID = 1,
                    CategoryID = 1,
                    PageID = 1,
                    Visible = true,
                    WebPages = new List<WebPage>()
                    {
                        new WebPage { ID = 1, Roles = "Role1" },
                        new WebPage { ID = 1, Roles = "Role2" },
                        new WebPage { ID = 1, Roles = "Role3" },
                    }.AsQueryable()
                };

                Menu myMenu2 = new Menu
                {
                    ID = 1,
                    CategoryID = 1,
                    PageID = 1,
                    Visible = true,
                    WebPages = new List<WebPage>()
                    {
                        new WebPage { ID = 1, Roles = "Role3" },
                        new WebPage { ID = 1, Roles = "Role4" },
                        new WebPage { ID = 1, Roles = "Role5" },
                    }.AsQueryable()
                };

                Menu myMenu3 = new Menu
                {
                    ID = 1,
                    CategoryID = 1,
                    PageID = 1,
                    Visible = true,
                    WebPages = new List<WebPage>()
                    {
                        new WebPage { ID = 1, Roles = "Role5" },
                        new WebPage { ID = 1, Roles = "Role6" },
                        new WebPage { ID = 1, Roles = "Role7" },
                    }.AsQueryable()
                };

                List<Menu> menus = new List<Menu>() { myMenu1, myMenu2, myMenu3 };

                string roleToSearchFor = "Role3";

                var allCategories = menus.Where(menu => menu.CategoryID == 1 && menu.Visible && menu.WebPages.Any(webPage => webPage.Roles.Contains(roleToSearchFor))).ToList();

                return;
            }
        }

Answer 3

DoctorJonez thanks!!!

I'm putting this here as I have more space. 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]))
  )

Is this a bug in Subsonic or am I not understanding it correctly?

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.

Answer 4

If I understand the problem correctly, you want something like this:

var menuItems =
    from menuItem in Menu.All()
        where menuItem.Visible
            and (
                menuItem.WebPages.Contains(
                    webPage => webPage.Roles.Contains(
                        "role"
                    )
                )
                or menuItem.PageIsNull
            )
        select menuItem;

This should select only menu items joined to pages with the appropriate role.