EntityFramework Core filter Parent records to include all Childrens based on Childrens user permissions

Question

We save emails (.msg files) to our web application (a sort of joint email archive). On transfer each email gets assigned one or more tags. Some tags are public and some are limited to only some users (private). I'm having problems putting together an EF query expression (preferably lambda) to get all emails that satisfy possible search criteria.

Classes:

public class Email
{
    [Key]
    public int Id { get; set; }
    public string From { get; set; }
    public string To { get; set; }
    public string Subject { get; set; }
    // ... etc

    // each Email has a minimum 1 EmailTag
    public virtual List<EmailTag> ListTags { get; set; }      
}

public class EmailTag
{
    [Key]
    public int Id { get; set; }
    public int IdEmail { get; set; }
    public virtual Email Email { get; set; }
    public int IdTag { get; set; }
    public virtual Tag Tag { get; set; }
}

public class Tag
{
    [Key]
    public int IdTag { get; set; }
    public string TagName { get; set; }

    // Tag can have 0 users (public), or 1 or more users (private)
    public virtual List<TagUser> ListUsers { get; set; }
}

public class TagUser
{
    [Key]
    public int Id { get; set; }
    public int IdTag { get; set; }
    public virtual Tag Tag { get; set; }
    public int IdUser { get; set; }
    public virtual User User { get; set; }
}

In the repository we currently have the code bellow. I was thinking of first putting together a whitelist of all emails (just IDs) to which user has access to via tags permissons(?) and then join on this list:

public async Task<IQueryable<Email>> List(int idUser, List<int> tagsFilter, string searchString, int pageIndex, int pageSize)
{
    searchString = searchString.ToLower();

    // white list
    var emailWhiteList = EmailWhiteList(idUser, tagsFilter);

    // get IDs list
    var idList = await _dbContext.Emails
            .Where(x => 
                ((x.From + x.To + x.Subject).ToLower().Contains(searchString) 
                || searchString == "") && emailWhiteList.Contains(x.Id))
            .OrderByDescending(x => x.Received)
            .Select(t => t.Id)
            .Skip((pageIndex - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync();

    return _dbContext.Emails
            .Where(x => idList.Contains(x.Id))
            .OrderByDescending(x => x.Received);
}

and I got stuck on the logic of emailWhiteList. Any ideas on how to properly include tags' permissions into email query?

EDIT (emaiWhiteList) method:

// get allowed mails for the user
private HashSet<int> EmailWhiteList(int idUser, List<int> tagsFilter)
{
    // *this works OK
    // get all tags that are restricted to the user
    var blacklist = _dbContext.TagDBSet
        .Where(x => x.ListUsers.Where(u => !u.Deleted).Count() > 0
            && !x.ListUsers.Where(l => !l.Deleted).Any(l => l.IdUser == idUser)
            && x.InEmails)
        .Select(x => x.IdTag)
        .Distinct()
        .ToList();

    // * this works OK
    // get all tags that are allowed to the user
    var whitelist = _dbContext.TagDBSet
        .Where(x => !blacklist.Contains(x.IdTag))
        .Select(x => x.IdTag)
        .Distinct()
        .ToList();

    // tag whitelist with tagsFilter intersection
    HashSet<int> tagList = new HashSet<int>(whitelist.Select(t => t));
    if(tagsFilter.Count > 0) tagList = new HashSet<int>(whitelist.Intersect(tagsFilter));

    // * here is the problem I think, because I also get emails that
    // have a tag that is not allowed to idUser, because this email
    // also has a tag that is allowed to the idUser
    //get emailtag whitelist
    var mails = _dbContext.EmailTags
        .Where(x => !x.Deleted
            && tagList.Contains(x.IdTag))
        .ToList()
        .GroupBy(x => x.IdEmail)
        .Select(x => new { 
            IdEmail = x.First().IdEmail,
            Count = x.Count()
            })
        .Where(x => x.Count == tagsFilter.Count() || tagsFilter.Count == 0)
        .Select(x => x.IdEmail)
        .ToList();

    return new HashSet<int>(mails );
}

Answer 1

I updated your emailWhiteList function to retrieve the data using the GroupJoin/SelectMany (left outer join) approach. The logic here is:

1. Left join the whitelisted tags
2. Left join the blacklisted tags
3. Group by id and count both whitelisted and blacklisted tags that were joined on separately
4. Add condition - count of whitelisted is equal to filter list count (all filter tags included)
5. Add condition - coubnt of blacklisted is equal to 0 (no blacklisted tags allowed)

    private HashSet<int> EmailWhiteList(int idUser, List<int> tagsFilter)
    {
        // get all tags that are restricted to the user
        var blacklist = _dbContext.TagDBSet
                .Where(x => x.ListUsers.Count() > 0
                        && !x.ListUsers.Any(l => l.IdUser == idUser)
                        && x.InEmails
                        )
                .Select(x => x.IdTag)
                .Distinct()
                //.ToList() // needed to be commented out for group join to work
                ;

        // get all tags that are allowed to the user
        var whitelist = _dbContext.TagDBSet
                    .Where(x => !blacklist.Contains(x.IdTag)
                            && tagsFilter.Contains(x.IdTag)) // replaces the tag whitelist with tagsFilter intersection
                    .Select(x => x.IdTag)
                    .Distinct()
                    //.ToList() // needed to be commented out for group join to work
                    ;

        var emails = _dbContext.EmailTags
            // left join the whitelist tags on the email tags 
            // (the condition will be that count of joined whitelist records equals the length of filter - has all tags requested by filter)
            .GroupJoin(
                whitelist,
                emailTag => emailTag.IdTag,
                tag => tag,
                (emailTag, tag) => new { EmailTag = emailTag, WhitelistTag = tag }
            )
            .SelectMany(
                x => x.WhitelistTag.DefaultIfEmpty(),
                (x, y) => new { EmailTag = x.EmailTag, WhitelistTag = y }
            )
            // left join the blacklist tags on the email tags 
            // (the condition will be that count of joined blacklist records equals 0 - has no blacklisted tags)
            .GroupJoin(
                blacklist,
                joinedEmailTag => joinedEmailTag.EmailTag.IdTag,
                tag => tag,
                (joinedEmailTag, tag) => new { EmailTag = joinedEmailTag.EmailTag, WhitelistTag = joinedEmailTag.WhitelistTag, BlacklistTag = tag }
            )
            .SelectMany(
                x => x.BlacklistTag.DefaultIfEmpty(),
                (x, y) => new { EmailTag = x.EmailTag, WhiteListTag = x.WhitelistTag, BlacklistTag = y }
            )
            .ToList()
            .GroupBy(x => x.EmailTag.IdEmail)
            .Select(x => new {
                IdEmail = x.Key,
                WhiteListCount = x.Count(x => x.WhiteListTag > 0),
                BlackListCount = x.Count(x => x.BlacklistTag > 0),
            })
            .Where(x => x.WhiteListCount == tagsFilter.Count()
                && x.BlackListCount == 0)
            .Select(x => x.IdEmail)
            .ToList();

        return new HashSet<int>(emails);
    }