主页 / 问题 / 566779
Accepted
Bald
Asked:2020-09-16 12:59:10 +0000 UTC2020-09-16 12:59:10 +0000 UTC

Linq Left Join：是否可以优化 linq 查询？

  • 772

我需要向用户展示一个视图，要显示的信息在不同的表中。

为了简单起见，我在多个请求中获取数据，之后我使用左连接将所有数据粘合在一起。

linq请求看起来像这样：

var operations = _context.Set<Operation>()
                    .Include(x=>x.Product)
                    .ToList();

var timeWorks = _context.Set<OperationItem>()
                    .Where()
                    .ToList();

var attachments = _context.Set<OperationItem>()
                    .Where()
                    .ToList();

var result = operations
                 .GroupJoin(attachments, o=>o.Id, i=>i.OperationId, (l,r)=> new {Operation = l, Attachment = r})
                 .SelectMany(x=>x.Attachment.DefaultIfEmpty(), (l,r)=>new {Operation = l.Operation, Attachment = r})
                 .GroupJoin(timeWorks, o=>o.Operation.OperationId, i=>i.OperationId, (l,r)=> new {Operation = l.Operation, Attachment = l.Attaqchment, TimeWork = r.Sum(_=>_.TimeWork)})
                 .Select(x=> new OperationList
                 {
                     //Собираем окончательное представление
                 })
                 .ToList();

一切正常，但我不喜欢每个左连接我都必须遍历前一个连接的结果：

new {Operation = l.Operation, Attachment = l.Attaqchment, TimeWork = r.Sum(_=>_.TimeWork)}

你能告诉我我做错了什么或者有更简单的方法吗？

c#

1 个回答

  1. Best Answer

    您可以使用 linq 的内置形式来避免“翻转”——在这种情况下，编译器将自行处理翻转：

    var result = from operation in operations
             join attachment in attachments on operation.Id equals attachment.OperationId into attachmentsgroup
             from attachment in attachmentsgroup.DefaultIfEmpty()
             join timeWork in timeWorks on operation.Id equals timeWork.OperationId into timeWorksgroup
             let timeWork = timeWorksgroup.Sum(_ => _.TimeWork)
             select new OperationList {
               // ...
             };

    但一般来说，left join via group join 是一种技巧，只适用于以 left join 为原生运算符的数据库查询。

    对于对象的查询，事情就容易多了！

    var operations = _context.Set<Operation>()
                    .Include(x=>x.Product)
                    .AsEnumerable();

var timeWorks = _context.Set<OperationItem>()
                    .Where()
                    .ToLookup(x => x.OperationId);

var attachments = _context.Set<OperationItem>()
                    .Where()
                    .ToLookup(x => x.OperationId);

var result = from operation in operations
             from attachment in attachments[operation.Id].DefaultIfEmpty()
             let timeWork = timeWorks[operation.Id].Sum(_ => _.TimeWork)
             select new OperationList {
               // ...
             };
    • 3

相关问题