In theory, any query that can be expressed using LINQ will be simpler than its SQL equivalent. This simplification has a price - underlying implementation complexity. As a result some scenarios might be not supported or have issues. ELINQ does not perform any logical transformations, - it's plain SQL developed in .NET. So it's more verbose in some cases, yet C#! Compared to an alternative - writing SQL as an embedded string, it's a clear winner (run to see the SQL):
If you have good eyes and the original issue opened, you could notice that the SQL generated by EF is much more complex. It's not a mistake. EF seems to always generate an OUTER APPLY
construct, while it's not needed when there is a single floor. So the custom query is much simpler.
But if required the full variant can be coded. Here it is, for a reference: