Linq to Entities - is it possible to achieve this in single query to DB?

Question

I have multiple entity objects which I would like to secure by using custom Access Lists (ACL) stored in a SQL Server database. All my "securables" implement an ISecurable interface:

public interface ISecurable
{
    int ACLId { get; }
    ACL ACL { get; }
}

AccessList (ACL) entity looks like this:

public class ACL
{
    public int Id { get; set; }
    public virtual ICollection<ACE> ACEntries { get; set; }
}

... and each ACL has multiple ACE entries:

public class ACE
{
    public int Id { get; set; }

    public int ACLId { get; set; }
    public virtual ACL ACL { get; set; }

    public int PrincipalId { get; set; }
    public virtual Principal Principal { get; set; }

    public int AllowedActions { get; set; }  // flags
}

Actions can be granted both to Users and to Workgroups.

Principal is an abstract class, and both User and Workgroup inherits from it:

public abstract class Principal
{
    public int Id { get; set; }
    public string DisplayName { get; set; }

    public virtual ICollection<ACE> ACEntries { get; set; }
}

public class User : Principal
{
    public string Email { get; set; }

    public virtual ICollection<Workgroup> Workgroups { get; set; }
}

public class Workgroup : Principal
{
    public virtual ICollection<User> Users { get; set; }
}

User and Workgroup are obviously in many-to-many relation.

My DbContext is looks like this:

public class MyDbContext : DbContext
{   
    public DbSet<User> Users { get; set; }
    public DbSet<Workgroup> Workgroups { get; set; }
    public DbSet<ACL> ACLs { get; set; }
    public DbSet<ACE> ACEntries { get; set; }
    public DbSet<SecurableClass> SecurableClassItems { get; set; }
}

Finally, my question : I would like to write extension method to filter out all my ISecurable classes by ACL, based on user and required action. And I would like to have single query to the DB:

    public static IQueryable<ISecurable> FilterByACL(this IQueryable<ISecurable> securables, User user, int requiredAction)
    {
        var userId = user.Id;

        return securables.Where(s =>
            s.ACL.ACEntries.Any(e =>
                (e.PrincipalId == userId || user.Workgroups.Select(w => w.Id).Contains(userId)) &&
                (e.AllowedActions & requiredAction) == requiredAction));

    }

This does not work, due to error:

Unable to create a constant value of type 'Test.Entities.Workgroup'. Only primitive types or enumeration types are supported in this context

even though I select only IDs from Workgroup:

user.Workgroups.Select(w => w.Id)

Is there any way to handle this scenario ? Sorry for long question, but existing simplified code will probably best explain my intention.

UPDATE

After @vittore suggestion, my extension method might be something like this:

public static IQueryable<ISecurable> FilterByACL2(this IQueryable<ISecurable> securables, User user, int requiredAction, MyDbContext db)
{
    var userId = user.Id;

    var workgroups = db.Entry(user).Collection(u => u.Workgroups).Query();

    return securables.Where(s =>
        s.ACL.ACEntries.Any(e =>
            (e.PrincipalId == userId || workgroups.Select(w => w.Id).Contains(e.PrincipalId)) &&
            (e.AllowedActions & requiredAction) == requiredAction));

}

.. but I'll have to reference 'MyDbContext' as additional parameter ?

On the other side, if I write SQL function (TVF) which will return all allowable ACLIDs based on 'UserId' and 'RequiredAction':

CREATE function [dbo].[AllowableACLs]
(
    @UserId int,
    @RequiredAction int
)
returns table
as
return 
    select
        ace.ACLId
    from
        dbo.ACEntries ace
    where
        (@UserId = ace.PrincipalId or @UserId in (select wu.User_Id from dbo.WorkgroupUsers wu where ace.PrincipalId = wu.Workgroup_Id))
        and
        ((ace.AllowedActions & @RequiredAction) = @RequiredAction)

.. theoretically I could just make 'inner join' or 'exist (select 1 from dbo.AllowableACLs(@UserId, @RequiredAction)' from my ISecurable Entity.

Is there any way to get this working from code-first ?

Answer 1

There are number of options you have in order to get that information in one trip.

1. the most obvious one would be to use join and linq syntax:

Something close to:

var accessbleSecurables = from s in securables
                          from u in ctx.Users
                          from g in u.Workgroups
                          from e in s.ACL.entries
                          where u.id == userId ...
                          select s

so it will use user and her groups from db while querying securables

2. convert your workgroups to the query, using CreateSourceQuery

This way you will let EF know that it needs to do your contains on db level.

UPDATE: CreateSourceQuery is old name from EF3 - EF4 days. Right now you can use DbCollectionEntry.Query() method, like that:

  var wgQuery = ctx.Entry(user).Collection(u=>u.Workgroups).Query();

Then you'll be able to use that in your query, but you need reference to EF context in order to do that.

3. Create store procedure that will do the same sql query

UPDATE 2: One of the unique PROs of creating store procedure is ability to map multiple return result sets for it (MARS). This way you might for instance query all workgroups AND all securables for a user in one trip to db.

Answer 2

user.Workgroups is a local variable consisting of a sequence of c# objects. There is no translation of these objects into SQL. And EF requires this translation because the variable is used within an expression.

But it is simple to avoid the exception: first create a list of the primitive ID values:

var workGroupIds = user.Workgroups.Select(w => w.Id).ToArray();

and in the query:

e.PrincipalId == userId || workGroupIds.Contains(userId)

After your comments it seems that you can also do...

return securables.Where(s =>
    s.ACL.ACEntries.Any(e =>
        (e.PrincipalId == userId 
             || e.Principal.Workgroups.Select(w => w.Id).Contains(userId))
    && (e.AllowedActions & requiredAction) == requiredAction));

...which would do everything in one query.

Answer 3

You need to move "user.Workgroups.Select..." out of expression. EF tries to translate that expression to sql and fails because "user" is local variable. So, this should help to fix the error:

public static IQueryable<ISecurable> FilterByACL(this IQueryable<ISecurable> securables,
    User user, int requiredAction)
{
    var userId = user.Id;
    var groupIds = user.Workgroups.Select(w => w.Id).ToArray();
    return securables.Where(s =>
        s.ACL.ACEntries.Any(e =>
            (e.PrincipalId == userId || groupIds.Contains(userId)) &&
            (e.AllowedActions & requiredAction) == requiredAction));

}

Note that to ensure this is only one query you need to load user's Workgroups property when retrieving user from database. Otherwise, if you have lazy loading enabled, this will first load Workgroups for given user, so 2 queries.