Can I execute a LINQ to SQL query from within a LINQ object?

Question

I currently have a LINQ query implemented as a method of my DataContext class. This method calculates the permissions a user has for a page within a Wiki/CMS. What I'd like to do is relocate this method to the Page and User LINQ data objects.

However, when I move this query to the individual data objects it executes using LINQ to Objects instead of LINQ to SQL. This means that it pulls several full collections from the database server every time it executes.

Here is a fragment of the code that uses LINQ to Objects. I'd like this to use LINQ to SQL:

partial class WikiPage
{
    public void GetUserPermissions(Guid? userId) {
        var usersPlusAnon =
            (
                from user in this.Wiki.WikiWikiUsers
                select new { user.WikiId, WikiUserId = (Guid?)user.WikiUserId }
            ).Union(
                from wiki in new Wiki[]{this.Wiki}
                select new { wiki.WikiId, WikiUserId = (Guid?)null }
            );
    }
}

Here is a fragment of the code that works, using LINQ to SQL. I'd rather have this method on the WikiPage class than the DataContext:

partial class WikiDataContext
{
    public void GetUserPermissions(Guid? userId) {
        var usersPlusAnon =
            (
                from user in this.WikiTomeUsers
                join wikiUser in this.WikiWikiTomeUsers on user.WikiTomeUserId equals wikiUser.WikiTomeUserId into tmp_wikiUser
                from wikiUser in tmp_wikiUser.DefaultIfEmpty()
                select new { WikiId = wikiUser.WikiId, WikiTomeUserId = (Guid?)wikiUser.WikiTomeUserId }
            )
            .Union(
                from wiki in this.Wikis
                select new { WikiId = wiki.WikiId, WikiTomeUserId = (Guid?)null }
            );
    }
}

When the LINQ query is run from the DataContext, it executes as LINQ to SQL, but when run from the WikiPage LINQ object it runs as LINQ to Objects. Is something off with my syntax here, or is this simply not possible with LINQ?

Answer 1

The reason you are getting different behaviors from both sets of code is because you are using the 'this' keyword.

When you write your code as part of the data context, it is using the LINQ to SQL classes in the DataContext object (because the 'this' keyword is pointing to the DataContext).

If you want the code outside of the DataContext to use LINQ to SQL, you have to change your reference to 'this' to an instance of your WikiDataContext. It should then use LINQ to SQL to pull your data from the database rather than LINQ to Objects.