Entity Framework Include in Where Subquery

Question

I believe I'm missing something obvious here and I can not figure it out. I receieve the exception :

LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[Privilege] Include[Privilege,PrivilegeType](System.Linq.IQueryable1[Privilege], System.Linq.Expressions.Expression1[System.Func2[Privilege,PrivilegeType]])' method, and this method cannot be translated into a store expression.

When I run this EF query:

return _context.Categories.Where(c =>
             _context.Privileges
               .Include(p => p.PrivilegeType)
              .Where(p => p.PrivilegeType.Code == "code").Any()).ToList();

The end result I'm after is to determine if a user has a privilege based on the categoryId. At the moment, I have omitted the conditions in the Any() clause to simplify the statement.

Is this type of query possible?

Edit: Here is the FulentApi configuration:

public class PrivilegeConfiguration : EntityTypeConfiguration<Privilege>
{
    public PrivilegeConfiguration()
    {
        ToTable("ObjectPrivileges");

        HasKey(p => new { p.ObjectTypeId, p.ObjectId, p.PrivilegeTypeId });

        Property(p => p.ObjectTypeId)
            .HasColumnName("ObjectType")
            .HasMaxLength(2);

        Property(p => p.PrivilegeTypeId)
            .HasColumnName("PrivilegeID")
            .IsRequired();

        HasRequired(p => p.PrivilegeType);
    }
}

 public class PrivilegeTypeConfiguration : EntityTypeConfiguration<PrivilegeType>
{
    public PrivilegeTypeConfiguration()
    {
        ToTable("PrivilegeType");

        HasKey(p => p.PrivilegeTypeId);

        Property(p => p.PrivilegeTypeId)
            .HasColumnName("PrivilegeID");

        Property(p => p.ObjectTypeId)
            .HasColumnName("ObjectType")
            .HasMaxLength(2);

        Property(p => p.Code)
            .HasMaxLength(50);

        Property(p => p.Description)
            .HasMaxLength(255);
    }
}

Edit 2:

The where condition is something I would like to drop into the query to limit the amount of records that will come back. I'm updating a legacy database and trying to match what they currently already have in place to keep it as close to possible.

Here is the full query I am attemping:

var query = _context.Categories.AsQueryable();

        if (!string.IsNullOrEmpty(privilege))
        query = query.Where(c => (!_context.Privileges
         .Include(p => p.PrivilegeType)
         .Any(p => p.PrivilegeValue == 1
            && p.PrivilegeType.Code == privilege
            && p.ObjectTypeId == objectTypeId
            && p.ObjectId == c.CategoryId))
         ||
         _context.Privileges
             .Include(p => p.PrivilegeType)
             .Any(p => p.PrivilegeValue == 1
                 && p.PrivilegeType.Code == privilege
                 && p.ObjectTypeId == objectTypeId
                 && p.ObjectId == c.CategoryId
                 && (p.UserId == userId || _context.UserGroups.Select(ug => ug.UserID).Contains(userId))));

Here is the SQL representation of what I would like to do:

SELECT * FROM Categories WHERE(
(NOT EXISTS 

    (
        SELECT P.ObjectType 
        FROM ObjectPrivileges P 
            INNER JOIN PrivilegeType PT ON P.PrivilegeID = PT.PrivilegeID 
        WHERE 
            (
                P.PrivilegeValue > 0 
                AND PT.Code ='code' 
                AND P.ObjectType = 'SELECT'  
                AND P.ObjectID = 1 -- CategoryId
            )
    )
) OR

EXISTS 
    (
        SELECT P.ObjectType 
        FROM ObjectPrivileges P 
            INNER JOIN PrivilegeType PT ON P.PrivilegeID = PT.PrivilegeID 
        WHERE 
            (
                P.PrivilegeValue > 0 
                AND PT.Code = 'code' 
                AND P.ObjectType = 'SELECT' 
                AND P.ObjectID = 1 -- CategoryId
                AND (P.UserID = 57 OR P.GroupID IN (SELECT GroupID FROM Group_User WHERE UserID = 57))
            )
    ))

Answer 1

The problems:

1. As said in the comments, there's no foreign key relationship between Category and Privilege. There is a soft relationship: Privilege.ObjectID points to a primary key of a table. The problem is that your subquery doesn't relate categories and privileges, because CategoryID isn't used. The condition is either true or false for any category.

2. Include only works if it populates navigation properties of entities in the query result. In other words, Privileges.Include(p => p.PrivilegeType) is only effective if Privileges are returned. Apart from that, Includes can't be filtered so they can't be used as filter conditions.

So the first thing to do is: match CategoryID. Any query of this nature should look like ...

_context.Categories
        .Where(c => !_context.Privileges
                             .Any(p => p.ObjectId == c.CategoryId
                                    && ...))

The second this is to use PrivilegeType in a way that allows it to be filtered: p.PrivilegeType.Code == "code".

Applying these fixes, the entire query will look like this:

var userId = 57;
return _context.Categories
   .Where(c => 
       !_context.Privileges
                .Any(p => p.ObjectId == c.CategoryId
                       && p.PrivilegeValue > 0
                       && p.PrivilegeType.Code == "code"
                       && p.ObjectType = 'SELECT')
     || _context.Privileges
                .Any(p => p.ObjectId == c.CategoryId
                       && p.PrivilegeValue > 0
                       && p.PrivilegeType.Code == "code"
                       && p.ObjectType = 'SELECT'
                       && (p.UserId == userId
                             || _context.GroupUsers
                                        .Any(gu => gu.UserId == userId
                                                && gu.GroupID == p.GroupID)))
         );

Answer 2

Your current query is not actually performing any kind of filtering related to the different entities:

return _context.Categories.Where(c =>
               _context.Privileges
               .Include(p => p.PrivilegeType)
               Where(p => p.PrivilegeType.Code == "code").Any()).ToList();

This code will go to the database and get all Categories in the database if there are any Privilege entities in the database with a PrivilegeType.Code value of code. This doesn't filter anything, it just returns all Categories.

Also as mentioned in the comments by @dcg you are not using the c variable in your first lambda expression, which I do believe is the cause of the actual exception you are receiving as a C# object (in this case _context) cannot be passed into a where expression like that.

The resolution would be this:

_context.Categories.Where(c => c.Privileges.Any(p => p.PrivilegeType.Code == "code")).ToList();

This assumes you have a foreign key relationship between Categories and Privileges. If you do not, then you will need to restructure how those two tables are related or provide more details on what you are attempting to join the two entities using.

EDIT:

After your clarification in your post yes the above query is what you are looking for, but you need to actually map a relationship between your Categories and Privileges entities. On your Categories entity mapping configuration do this:

public class Category
{
    public int CategoryId { get; set; }
    public int PrivilegeId { get; set; }

    public virtual ICollection<Privilege> Privilege { get; set; }
}

public class Privilege
{
    public int PrivilegeId { get; set; }
    public int ObjectId { get; set; }
}

public class CategoryMap : EntityTypeConfiguration<Category>
{
    public CategoryMap()
    {
        ToTable("Categories");
        HasKey(x => x.CategoryId);

        HasMany(x => x.Privilege)
            .WithMany()
            .Map(x =>
            {
                x.MapLeftKey(nameof(Category.PrivilegeId));
                x.MapRightKey(nameof(Privilege.ObjectId));
            });
    }
}

public class PrivilegeMap : EntityTypeConfiguration<Privilege>
{
    public PrivilegeMap()
    {
        ToTable("Categories");
        HasKey(x => x.PrivilegeId);
    }
}

This will allow you to define a relationship that is 1-* between Category and Privilege, without requiring that Privilege actually has any kind of relationship back to Category. You will be able to write complex queries like this as a result.

EDIT:

I have attempted to recreate your SQL query (though I had to build my own local context from the structures of your above code, it may not be exactly accurate)

I believe that this will generate the same result set as your query. It may not generate the same SQL, but the results should be the same.

public IQueryable<Category> Query(int userId)
{
    var db = new Context();

    var groupUsers = db.GroupUsers.Where(x => x.UserId == userId).Select(gu => gu.GroupId);

    var first = db.Privileges
        .Join(db.PrivilegeTypes, p => p.PrivilegeTypeId, pt => pt.PrivilegeTypeId, (p, pt) => new { P = p, PT = pt })
        .Where(join => join.P.PrivilegeValue > 0 &&
                        join.PT.Code == "code" &&
                        join.P.ObjectTypeId == "SELECT" &&
                        join.P.ObjectTypeId == "1");

    var second = first.Where(join => join.P.UserId == userId || groupUsers.Contains(join.P.UserId));

    return db.Categories.Where(c => first.All(join => join.P.ObjectId != c.CategoryId) ||
                                    second.Any(join => join.P.ObjectId == c.CategoryId));
}

Query(57); // Look up UserID 57