如何简化Entity Framework Core中提交记录查询代码（避免循环）

我编写了一段在Entity Framework Core中查询提交记录的代码，想请教是否有简化方法，比如去掉foreach循环：

public async Task<IEnumerable<Submission>> SelectSubmissionsAsync(string submitterId, IEnumerable<Group> groups)
{
    var submissions = new List<Submission>();

    var apps = context.Apps
                      .Select(a => new
                                   {
                                       Id = a.Id,
                                       Member = a.MemberHistories.OrderByDescending(ash => ash.MemberChangeDate).FirstOrDefault().Member,
                                       Owner = a.OwnerHistories.OrderByDescending(oh => oh.OwnerChangeDate).FirstOrDefault().Owner
                                   }) 
                      .ToDictionary(x => x.Id, x => x.Member + x.Owner);

    var subs = context.Submissions.ToList();

    foreach (var sub in subs)
    {
        if (apps.ContainsKey((Guid)sub.AppId))
        {
            var value = apps[(Guid)sub.AppId];
            var check = value.Contains(submitterId, StringComparison.InvariantCultureIgnoreCase) || groups.Any(g => value.Contains(g.Id, StringComparison.InvariantCultureIgnoreCase));

            if (check) 
                submissions.Add(sub);
        }
    }
}


public class Submission
{
    public Guid Id { get; set; }       
    public Application App { get; set; }
    public Guid? AppId { get; set; }
}

public class App
{
    public Guid Id { get; set; }
    public string Identifier { get; set; }        
    public ICollection<MemberHistory> MemberHistories { get; set;}
    public ICollection<OwnerHistory> OwnerHistories { get; set;}
}

优化方案：用EF Core LINQ直接关联过滤，消除内存遍历 #

你的代码目前需要先加载全量数据再在内存中筛选，不仅冗余还影响性能。可以直接通过LINQ关联查询，把过滤逻辑交给数据库执行，代码更简洁高效：

public async Task<IEnumerable<Submission>> SelectSubmissionsAsync(string submitterId, IEnumerable<Group> groups)
{
    var groupIds = groups.Select(g => g.Id).ToList();
    
    return await context.Submissions
        // 过滤掉没有关联App的提交
        .Where(sub => sub.AppId.HasValue)
        // 关联对应的App
        .Join(context.Apps, 
            submission => submission.AppId.Value, 
            app => app.Id, 
            (submission, app) => new { Submission = submission, App = app })
        // 提取最新的成员和所有者信息
        .Select(joined => new 
        {
            joined.Submission,
            LatestMember = joined.App.MemberHistories
                .OrderByDescending(history => history.MemberChangeDate)
                .FirstOrDefault()?.Member,
            LatestOwner = joined.App.OwnerHistories
                .OrderByDescending(history => history.OwnerChangeDate)
                .FirstOrDefault()?.Owner
        })
        // 匹配提交者ID或组ID
        .Where(data => 
            // 检查提交者是否是最新成员或所有者
            (!string.IsNullOrEmpty(data.LatestMember) && data.LatestMember.IndexOf(submitterId, StringComparison.InvariantCultureIgnoreCase) >= 0) ||
            (!string.IsNullOrEmpty(data.LatestOwner) && data.LatestOwner.IndexOf(submitterId, StringComparison.InvariantCultureIgnoreCase) >= 0) ||
            // 检查是否属于目标组
            groupIds.Any(groupId => 
                (!string.IsNullOrEmpty(data.LatestMember) && data.LatestMember.IndexOf(groupId, StringComparison.InvariantCultureIgnoreCase) >= 0) ||
                (!string.IsNullOrEmpty(data.LatestOwner) && data.LatestOwner.IndexOf(groupId, StringComparison.InvariantCultureIgnoreCase) >= 0)
            )
        )
        // 只返回提交记录
        .Select(data => data.Submission)
        .ToListAsync();
}

核心优化点 #

• 避免全量加载：不再把所有Submissions和Apps加载到内存，数据库直接返回符合条件的结果
• 消除foreach循环：用LINQ的Where和Join替代手动遍历筛选
• 优化匹配逻辑：不再拼接字符串，分别检查成员和所有者字段，避免不必要的字符串操作
• 提升查询效率：提前提取组ID列表，避免在查询中重复枚举groups集合

额外建议 #

如果Member和Owner字段是用分隔符拼接的多ID字符串，建议改成多对多关联表存储，这样可以用精准的ID匹配替代字符串模糊查询，性能和可维护性都会大幅提升。

内容的提问来源于stack exchange，提问作者user989988