c# - EntityFramework Core 过滤父记录以包含基于子用户权限的所有子记录

Question

我们将电子邮件（.msg 文件）保存到我们的 Web 应用程序（一种联合电子邮件存档）。在传输时，每封电子邮件都会被分配一个或多个标签。有些标签是公开的，有些则仅限于某些用户（私人）。我在将 EF 查询表达式（最好是 lambda）放在一起以获取满足可能搜索条件的所有电子邮件时遇到问题。

课程：

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; }
}

在存储库中，我们目前有以下代码。我正在考虑首先将用户可以通过标签权限（？）访问的所有电子邮件（仅 ID）的白名单放在一起，然后加入此列表：

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);
}

我被困在emailWhiteList. 关于如何正确地将标签的权限包含到电子邮件查询中的任何想法？

编辑（emaiWhiteList）方法：

// 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 );
}

标签： c#entity-framework-core

Answer 1

我更新了您的 emailWhiteList 函数以使用 GroupJoin/SelectMany（左外连接）方法检索数据。这里的逻辑是：

1. 离开加入白名单标签
2. 离开加入黑名单标签
3. 按 id 分组并计算分别加入的白名单和黑名单标签
4. 添加条件 - 白名单的计数等于过滤器列表计数（包括所有过滤器标签）
5. 添加条件 - 黑名单的数量等于 0（不允许列入黑名单的标签）

    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);
    }