System.NotSupportedException - Cannot compare elements of type 'System.Linq.IQueryable

Question

I am currently getting the below error

An exception of type 'System.NotSupportedException' occurred in >EntityFramework.SqlServer.dll but was not handled in user code

Additional information: Cannot compare elements of type 'System.Linq.IQueryable`1[[System.Int32, mscorlib, Version=4.0.0.0, >Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only primitive types, >enumeration types and entity types are supported.

My code is

public List<User> GetActiveUsers(IEnumerable<int> officeIDs, string roleID, string query)
{
    return (from user in GetDBContext.User
            join userRole in GetDBContext.UserRole
                on user.UserID equals userRole.UserID
            join userOffice in GetDBContext.UserAuthorizedOffice
                on user.UserID equals userOffice.UserID
            where user.IsActive == true &&
                    user.UserTypeID == 1 &&
                    userOffice.IsAuthorized &&
                    userOffice.Office.IsActive &&
                    (officeIDs == null || officeIDs.Contains(userOffice.OfficeID)) &&
                    string.Equals(userRole.RoleID, roleID) &&
                    (user.FirstName + user.LastName).Contains(query)
            select user).ToList();
}

The issue seems to be thrown from the line

(officeIDs == null || officeIDs.Contains(userOffice.OfficeID))

If I remove the first condition, officeIDs == null, the query executes perfectly.

Could anyone please explain what I am missing or as to why this error is thrown.

Answer 1

officeIDs == null can't be coverted to a sql statement Change the below:

(officeIDs == null || officeIDs.Contains(userOffice.OfficeID))

First check if it's null at top of your function: add parentheses after

officeIDs = officeIDs ?? Enumerable.Empty<int>();

And the replacement for that query:

(!officeIDs.Any() || officeIDs.Any(id => id == userOffice.OfficeID))

Answer 2

officeIDs == null is a statement which should not be part of the linq, as its not mappable to database query (is not a query).

Make sure that the officeIDs are not null, and remove it from the linq query.

public List<User> GetActiveUsers(IEnumerable<int> officeIDs, string roleID, string query)
    {
        officeIDs = officeIDs ?? new List<int>();

        return (from user in GetDBContext.User
                join userRole in GetDBContext.UserRole
                    on user.UserID equals userRole.UserID
                join userOffice in GetDBContext.UserAuthorizedOffice
                    on user.UserID equals userOffice.UserID
                where user.IsActive == true &&
                        user.UserTypeID == 1 &&
                        userOffice.IsAuthorized &&
                        userOffice.Office.IsActive &&
                        officeIDs.Contains(userOffice.OfficeID) &&
                        string.Equals(userRole.RoleID, roleID) &&
                        (user.FirstName + user.LastName).Contains(query)
                select user).ToList();
    }