c# - 消除 LINQ to Entities 中的循环

Question

我如何修改下面的查询以消除 foreach 循环并对数据库进行一次调用？

List<AuditLog> auditLogs = new List<AuditLog>();

foreach (TableRecordGuidPair pair in allTablesRecords)
{
    var auditLogsFromDatabase = databaseContext.AuditLog.OrderBy(x => x.EventDateUtc)
      .Where
        (x => x.TableName == pair.TableName && 
         x.RecordId == pair.RecordID)
        .Select(auditLog => new AuditLog
        {
            AuditLogId = auditLog.AuditLogId,
        }).ToList();
    auditLogs.AddRange(auditLogsFromDatabase);
}

return auditLogs;

Answer 1

您可以像这样使用 SelectMany 选择所有列表:

return allTablesRecords
    .SelectMany(pair => 
        databaseContext.AuditLog.OrderBy(x => x.EventDateUtc)
            .Where(x => x.TableName == pair.TableName && x.RecordId == pair.RecordID)
            .Select(auditLog => new AuditLog
            {
                AuditLogId = auditLog.AuditLogId,
            })
    )
    .ToList();

或者，你可以做一个连接

return (from allTableRecords pair
    join databaseContext.AuditLog auditLog
        on new { pair.TableName, pair.RecordId } equals new { auditLog.TableName, auditLog.RecordId }
    select new {
        AutitLogId = auditLog.AuditLogId
    })
    .ToList();