LINQ InvalidOperationException when making a query

Question

Ok, last edit, promise:

With the following class:

public partial class MembershipModule : BaseConnection<MembershipModule>
{
    private const string AccessPrivilege = "Access";

    public bool Accessible()
    {
        return this.Privileges().Any(p => p.Name.Equals(AccessPrivilege));
    }

    public IQueryable<MembershipAction> Privileges()
    {
        var privileges = from p in LinqUtil.Context.MembershipModuleActions
                         where
                             p.MembershipModule.Id.Equals(this.Id) &&
                             p.MembershipRolePrivileges.Any(rp => rp.ModuleActionId.Equals(p.Id))
                         select p.MembershipAction;

        return privileges;
    }
}

Why does this work

    public static List<MembershipModule> Collection()
    {
        List<MembershipModule> collection = new List<MembershipModule>();

        if(!MembershipUser.Connected)
            return collection;

        foreach(MembershipModule m in LinqUtil.Context.MembershipModules)
        {
            if(m.Accessible())
                collection.Add(m);
        }

        return collection;
    }

While this doesn't?

    public static List<MembershipModule> Collection()
    {
        if(!MembershipUser.Connected)
            return new List<MembershipModule>();

        return LinqUtil.Context.MembershipModules.Where(m => m.Accessible()).ToList();
    }

Answer 1

looks to me like you are trying to execute a linq to object operation and it is trying to convert it to a sql statement.

Keep in mind: If this were to succede, I expect that the sql related to the folowing call would happen once per MembershipModule:

 return this.Privileges().Any(p => p.Name.Equals(AccessPrivilege)); 

Have you tried skipping using the Accesible method, and hitting the Privileges collection directly? Something like this the code below. I have changed the return statement. This is the gist, may not be 100% correct:

public static List<MembershipModule> Collection()      
{      
    if(!MembershipUser.Connected)      
        return new List<MembershipModule>();      

    var mod = MembershipModule.SearchById(new Guid("b012d35f-6af1-47de-9e54-e5df957c07e1"));      

    var y = from p in LinqUtil.Context.MembershipModuleActions      
            where      
                p.MembershipModule.Id.Equals(mod.Id) &&      
                p.MembershipRolePrivileges.Any(rp => rp.ModuleActionId.Equals(p.Id))      
            select p.MembershipAction;      

    var u = y.Any(p => p.Name.Equals(AccessPrivilege));      

    return LinqUtil.Context.MembershipModules.Where(m => m.Privileges().Any(p => p.Name.Equals(AccessPrivilege)).ToList();  
}  

Answer 2

Could it have to do with a static method (public static List<_membershipModule> Collection()) of a class trying access instance members (AccessPrivilege)?