Linq Left Join: Is it possible to optimize a linq query?

Question

I need to show the user a presentation , the information for display is in different tables.

For simplicity, I receive the data in several requests, then I glue all this data using left joints .

linq query looks like this:

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

Everything works as it should, but I don’t like the fact that for each left connection I have to pass the result of the previous connection:

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

Tell me, can I do something wrong and can it be made easier?

Pavel Mayorov Pavel Mayorov 48.9k five 50 110 · Accepted Answer · 2016-09-16T13:42:31

To exclude "prokidyvaniya" you can use the built-in form linq - in this case, the compiler will take care of prokidyvaniya himself:

 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 { // ... };

But, generally speaking, left join through group join is a trick that is suitable for use only in queries to the base, where left join is a "native" operator.

For requests to objects, everything is made much easier!

 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 { // ... };

@Bald msdn.microsoft.com/ru-ru/library/bb549073(v=vs.110).aspx I do not know where you were looking for, but you were definitely looking bad.

Linq Left Join: Is it possible to optimize a linq query?

1 answer 1

More articles: