dotnet / dotnet/efcore

OrderBy is ignored when used with Distinct and then Concatenated

Open
#28,399 18 comments 0 reactions 0 assignees View on GitHub
area-query area-set-operations customer-reported needs-design
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

I'm selecting from few different tables, projecting to a common model object and concatenating them (translated as UNION ALL) to one single result collection. I also apply OrderBy to each select.
Example:
```cs
var result = dbSetA.Where(...).AsModel().OrderBy(x => x.Name)
.Concat(dbSetB.Where(...).AsModel().OrderBy(x => x.Name))
.ToList();
```

This example works as expected. All subqueries are order by name and then UNION ALL is applied. the result is
[Sorted result of dbSetA] + [Sorted result of dbSetB]

However when I apply Distinct (doesn't matter on what position in the subquery), the OrderBy is ignored (removed from translated query)
example:
```cs
var result = dbSetA.Where(...).AsModel().Dictinct().OrderBy(x => x.Name)
.Concat(dbSetB.Where(...).AsModel().Dictinct().OrderBy(x => x.Name))
.ToList();

```
In this case the translated subqueries contain Distinct but no Order By, so the result is
[Unsorted distinct result of dbSetA] + [Unsorted distinct result of dbSetB]

I believe this is a bug.

The only workaround I was able to find was to execute subqueries separately and merge using List methods:
example:
```cs
var result = dbSetA.Where(...).AsModel().Dictinct().OrderBy(x => x.Name).ToList();
result.AddRange(dbSetB.Where(...).AsModel().Dictinct().OrderBy(x => x.Name).ToList());
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.