Linq - Excluding users from list in IQueryable expression - What is the correct way?

Question

In order to ease up a number of things, I have created a base class for user "management". This is not the same as account management, which is already provided.

This is my base class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using Microsoft.AspNet.Identity.EntityFramework;

namespace ARSoftwareV2.UserLibrary
{
    // This is a base class for all user needs. If you want to implement your own set of checks,
    // you can simply inherit from this class and you get pretty much everything you need already set up.
    public class UserClassBase
    {
        private Models.ApplicationDbContext DefaultContextInstance;
        // We only want to show the "final results" here, not what should be hidden.
        protected Models.ApplicationDbContext CreateNewContext()
        {
            return new Models.ApplicationDbContext();
        }

        protected Models.ApplicationDbContext DefaultContext
        {
            get
            {
                if ( DefaultContextInstance == null)
                {
                    DefaultContextInstance = CreateNewContext();
                }
                return DefaultContextInstance;
            }
        }
        // This lists users based on a number of criteria.
        // Called without arguments, the query lists all users which are:
        // - Enabled
        // - Not deleted
        // - Not employees (this isn't done yet)
        public IQueryable<Models.ApplicationUser> GetUserListQuery(bool IncludeDisabled = false, bool IncludeDeleted = false,bool IncludeEmployees = false )
        {
            IQueryable<Models.ApplicationUser> result;
            using (DefaultContext)
            {
                IQueryable<Models.ApplicationUser> UserQueryIncludeAll = (from user in DefaultContext.Users select user);
                result = UserQueryIncludeAll;
                if (!(IncludeEmployees == true))
                {
                    // I need to make a join here to exclude employees by the user list.
                }
                if ((IncludeDisabled == false) && (IncludeDeleted == false))
                {
                    result.Where(u => u.Enabled == true && u.Deleted == false);
                }
                else if (IncludeDisabled == true)
                {
                    result.Where(u => u.Deleted == true);
                }
                else if (IncludeDeleted == true)
                {
                    result.Where(u => u.Enabled == true);
                }
            }
            return result;
        }

        public bool UserEmailExists( string EmailToCheck )
        {
            IQueryable<Models.ApplicationUser> Query = GetUserListQuery().Where(u => u.Email == EmailToCheck && u.Roles.Contains();
            IList<Models.ApplicationUser> resultList = Query.ToList<Models.ApplicationUser>();
            return (resultList.Count >0);
        }

        public Models.ApplicationUser FindUserByID( string ID)
        {
            IQueryable<Models.ApplicationUser> Query = GetUserListQuery().Where(u => u.Id == ID);
            Models.ApplicationUser result = Query.FirstOrDefault();
            return result;
        }

        public string[] UserRoles( string UserID )
        {
            Models.ApplicationUser User;
            User = FindUserByID(UserID);
            IList<string> result = new List<string>;
            foreach( var r in User.Roles )
            {
                result.Add(r.ToString());
            }
            return result.ToArray<string>();
        }
    }
}  

The idea behind this is that I can then use it both from the registration/login infrastructure and from a web service.
So, the problem I have is that - if employees have to be excluded - I need a way to identify the role. This, I would think, has to be done via a join. Unless you know of a better way to do it, of course :)

Sooo, my question is: what is the best way to exclude users which have an employee role? If - as I think - that has to be a join, can you please suggest a syntax that works for my case? I have googled and VS2015 seems intent on marking any attempt with a squiggly red mark :)

So far, I have tried the following way:

if (!(IncludeEmployees == true))
                {
                    // I need to make a join here to exclude employees by the user list.
                    result.Join(DefaultContext.Roles, r => Roles, Users => Users, Users.ID = r.UserID);
                }  

And I also tried this way:

if (!(IncludeEmployees == true))
                {
                    innerResult = (from u in UserQueryIncludeAll select u).Join(DefaultContext.Roles, r => Roles, u.Id = r.UserID);
                }

Looking at the similar questions, there's an answer that implies I need multiple contexts to do this. Is that correct?

Can you please point me at what I am doing wrong?

Answer 1

Use navigation properties instead of manual joins, like this

result = result.Where(user => !user.Roles.Any(role => role.Id == "Employee"));

Btw, your code is missing the result assignments like above, so it does not do any filtering at all. The other thing that is wrong is the using (DefaultContext) statement because you are disposing a cached instance member. The whole procedure can be simplified like this

public IQueryable<Models.ApplicationUser> GetUserListQuery(bool IncludeDisabled = false, bool IncludeDeleted = false, bool IncludeEmployees = false )
{
    IQueryable<Models.ApplicationUser> result = DefaultContext.Users;
    if (!IncludeDisabled)
        result = result.Where(user => user.Enabled);
    if (!IncludeDeleted)
        result = result.Where(user => !user.Deleted);
    if (!IncludeEmployees)
        result = result.Where(user => !user.Roles.Any(role => role.Id == "Employee"));
    return result;
}