Open roji opened 5 years ago
positional works in order by clause? So if your concern is the unspeakable name for the union-ed expression, you can always reference it by position.
@mburbea we actually don't really need positional - the union-ed expression does have a name. The difficulty is an EF Core implementation detail rather than an SQL issue, we simply don't have the infrastructure to make the ordering clause refer to the union-ed expression alias (or position for that matter).
This shouldn't be a critical matter, probably mostly a matter of generating cleaner SQL, so we'll probably defer this for after 3.0.
See https://github.com/aspnet/EntityFrameworkCore/pull/17340#issuecomment-523490838 for an implementation proposal.
We currently pushdown to subquery whenever an OrderBy, Take or Offset is applied to it - but these can be applied directly on the set operation, without a subquery (but see #16238 for Take on SqlServer).
For example, for test Select_Union_different_fields_in_anonymous_with_subquery, we currently translate to:
We should translate to:
The reason we don't currently do this, is that we have no good way for the ORDER BY clause to reference a column alias that has no table alias (
[Foo]
).