0

I wrote following query which produces an OUTER APPLY when translated to SQL

Users.Where(u => !u.IsDeleted).Select(u => new
{
    User = u, 
    Requests = OfficeRequests.Where(x => x.BuildingId == 1 && x.RequestedDate >= DateTime.UtcNow.Date).ToList()
})

But when the condition User.UserId == OfficeRequest.UserId is included the resulting sql turns into LEFT JOIN instead of OUTER APPLY

Users.Where(u => !u.IsDeleted).Select(u => new
{
    User = u, 
    Requests = OfficeRequests.Where(x => x.BuildingId == 1 && x.UserId == u.UserId && x.RequestedDate >= DateTime.UtcNow.Date).ToList()
})

Is there a way to force outer apply in translated sql query?

Ali Khan
  • 138
  • 9
  • 2
    If the query returns the correct result I am not sure it matters - In many cases the SQL Optimiser will [translate an outer apply into a left join anyway](https://stackoverflow.com/a/26487087/1048425), so even if you manage to force an outer apply to be composed, what is executed may end up as a left join anyway. My initial answer to this question would be the same as the conclusion in the linked answer - unless you are actually having performance issues then don't worry about it. If you are having performance issues, then detail them. – GarethD Dec 07 '22 at 13:17
  • The only reason for preferring an outer apply is performance. The results are same though. – Ali Khan Dec 07 '22 at 13:45
  • 1
    This query has nothing with OUTER APPLY or LEFT JOIN. It is Eager Loading query and EF decides how to load related data. If you have performance issues, try to use `AsSplitQuery()` – Svyatoslav Danyliv Dec 07 '22 at 13:51
  • 1
    Are you actually having performance issues though? If so, it might be worth running the query you want to be generated along side the query that is being generated, and seeing if there is still a measurable difference. If there is it may be worth adding the two execution plans to [Paste The Plan](https://www.brentozar.com/pastetheplan/) and adding the link to your question. This could be an xy scenario, and your actual issue may be in the database and not in your link. And if the performance of the two is the same then changing your linq isn't going to help anyway. – GarethD Dec 07 '22 at 13:54
  • What is `Users`, what is `OfficeRequests`? It's vital to know such things for answering. Also, is this really LINQ-to-SQL, not Entity Framework? – Gert Arnold Dec 09 '22 at 16:18

0 Answers0