How do I refactor a common LINQ subquery into a method?

Question

I'm struggling to come up with the right words to summarize this problem, so any input on what I can add to clarify it would be appreciated.

The basic scenario is this: I have a basic CMS (with pages, users, etc.). Page is a LINQ data object, which maps directly to a Page table.

I've added a method to the Page class called GetUserPermissions. This method accepts a UserId and returns a non-LINQ class called PagePermissionSet, which describes what the user can do. PagePermissionSet is calculated via a LINQ query.

Now I want to get the list of Pages which a user has access to. The ideal implementation would be as follows:

from page in mDataContext.Pages
where page.GetUserPermissions(userId).CanView
select page

This fails, stating that there is no SQL equivalent for GetUserPermissions (which is reasonable enough), or after some refactoring of the method, that the CanView member can't be invoked on an IQueryable.

Attempt two was to add a method to the DataContext, which returns all of the permissions for each Page/User as an IQueryable:

IQueryable<PagePermissionSet> GetAllPagePermissions()

I then tried to join to this result set:

IQueryable<Page> GetAllPages(Guid? userId) {
    var permissions = mDataContext.GetAllPagePermissions();

    var pages =
        from page in mDataContext.WikiPages
        join permission in permissions on Page.FileName equals permission.PageName
        where permission.CanView && permission.UserId == userId
        select page;

    return pages;
}

This produces the error: "The member 'WikiTome.Library.Model.PagePermissionSet.PageName' has no supported translation to SQL."

PagePermissionSet is pretty much just a shell holding data from the select clause in GetUserPermissions, and is initialized as follows:

select new PagePermissionSet(pageName, userName, canView, canEdit, canRename)

With all of that out of the way... How can I reuse the LINQ query in Page.GetUserPermissions in another query? I definitely don't want to duplicate the code, and I would prefer not to translate it to SQL for inclusion as a view at this point.

Answer 1

Maybe you need a compiled query?

http://msdn.microsoft.com/en-us/library/bb399335.aspx

Answer 2

You have a few options.

1) The quick and dirty solution is to use AsEnumerable() with your query to bring the entire Pages table down to the client side then operate on it. For small tables this should be fine, however for large tables it will be inefficient and lead to performance issues depending on the size. If you choose to use this be mindful of how it actually operates. This means updating your code to:

from page in mDataContext.Pages.AsEnumerable()
where page.GetUserPermissions(userId).CanView
select page

2) A more involved solution would be to create a stored procedure or UDF on the SQL server that you would call from the DataContext and pass parameters to. Take a look at Scott Gu's blog post: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures).

You could then write something like:

mDataContext.GetUserPermissions(userId)

All the logic you do in your code would written in SQL and you would return the viewable pages for the given user. This bypasses the use of the PagePermissionSet properties that have no supported translation to SQL.

Answer 3

I was able to solve the bulk of this problem today.

The error "The member 'WikiTome.Library.Model.PagePermissionSet.PageName' has no supported translation to SQL." was caused by HOW I was initializing my PagePermissionSet objects.

I had been initializing them using a constructor, like this:

select new PagePermissionSet(pageName, userName, canView, canEdit, canRename)

However, in order for LINQ to properly track the properties, it needs to be initialized like this:

select new PagePermissionSet { PageName=pageName, UserName = userName, CanView = canView, CanEdit = canEdit, CanRename = canRename }

With this change in place, I can create a method which returns an IQueryable<PagePermissionSet>, and then join my query to that (as in the second example).