With .NET 6, Some Navigation scenario doesn't contain column from Left join in Order By caluse

[archanasoni]

Hi,

We are in the process of supporting .NET 6 via IBM EF Core provider.
We've observed that for few Navigation related testcase query has Left join and column of left join is not included in Order by clause, We also observed that with .NET5 query it was there.
For Example: Below EF Linq Query we have:
var q = from c in ctx.Customers
where c.Customerid.StartsWith("A")
orderby c.Customerid
select new { c.Customerid, c.Orders };

It gets translated in NET6:
SELECT c.CUSTOMERID, o.ORDERID, o.CUSTOMERID, o.EMPLOYEEID, o.FREIGHT, o.ORDERDATE, o.REQUIREDDATE, o.SHIPADDRESS, o.SHIPCITY, o.SHIPCOUNTRY, o.SHIPNAME, o.SHIPPEDDATE, o.SHIPPOSTALCODE, o.SHIPREGION FROM NEWTON.CUSTOMERS AS c LEFT JOIN NEWTON.ORDERS AS o ON c.CUSTOMERID = o.CUSTOMERID WHERE c.CUSTOMERID LIKE 'A' || '%' ORDER BY **c.CUSTOMERID**

Where as in .NET5 it gets translated to:
SELECT c.CUSTOMERID, o.ORDERID, o.CUSTOMERID, o.EMPLOYEEID, o.FREIGHT, o.ORDERDATE, o.REQUIREDDATE, o.SHIPADDRESS, o.SHIPCITY, o.SHIPCOUNTRY, o.SHIPNAME, o.SHIPPEDDATE, o.SHIPPOSTALCODE, o.SHIPREGION FROM NEWTON.CUSTOMERS AS c LEFT JOIN NEWTON.ORDERS AS o ON c.CUSTOMERID = o.CUSTOMERID WHERE c.CUSTOMERID LIKE 'A' || '%' ORDER BY **c.CUSTOMERID, o.ORDERID**

Clearly we can see the diff in order by clause. In .NET6 Order by c.CUSTOMERID is being used where as in .NET5 Order by c.CUSTOMERID, o.ORDERID

Similarly one more scenario we have, Linq query for this:
var q1 = from o in ctx.Orders
where o.Customerid == "ALFKI"
select new { o.Orderid, o.Customer.Orders };

.NET6 SQL Query:
SELECT o.OrderID, c.CustomerID, o0.OrderID, o0.CustomerID, o0.EmployeeID, o0.OrderDate FROM Orders AS o LEFT JOIN Customers AS c ON o.CustomerID = c.CustomerID LEFT JOIN Orders AS o0 ON c.CustomerID = o0.CustomerID WHERE o.CustomerID = 'ALFKI' ORDER BY o.OrderID, c.CustomerID

.NET5 SQL Query:
SELECT o.OrderID, c.CustomerID, o0.OrderID, o0.CustomerID, o0.EmployeeID, o0.OrderDate FROM Orders AS o LEFT JOIN Customers AS c ON o.CustomerID == c.CustomerID LEFT JOIN Orders AS o0 ON c.CustomerID == o0.CustomerID WHERE o.CustomerID == 'ALFKI' ORDER BY o.OrderID ASC, c.CustomerID ASC, o0.OrderID ASC

We observed that in .NET6 Order by does not contain column of last Left Join where as with .NET5 it has..
Now the difference in order by cause result to be different in different DB2 flavors like zOS, IBM i etc.

Similar SQL query diff we can see for SQL Server as well, the only thing is SQL Server process result in differnt way and because of that we do not see any issue there..

Not sure what is the reason for it and how can we handle it..

[roji]

This seems like #19828 - this was an intentional change.

Your LINQ query order by the CustomerId, but does not order by OrderId. EF Core 5.0 used to generate SQL that ordered by the OrderId, but this wasn't expressed in the LINQ query and therefore not something that could be depended on. If ordering by the OrderId is important for the program, then that ordering should be explicitly specified.

Where are you seeing the actual issue? Are these tests which are failing in EF Core 6.0, and which may need to be updated to explicitly specify the ordering?

[archanasoni]

Thanks for the quick reply, with IBM EF Core provider these testcases are failing against zOS, as the server need last order by as well. Below query does not include any order by, but still 2 columns are added and column from second Left Join is being left:
var q1 = from o in ctx.Orders
where o.Customerid == "ALFKI"
select new { o.Orderid, o.Customer.Orders };

sql query:
SELECT o.OrderID, c.CustomerID, o0.OrderID, o0.CustomerID, o0.EmployeeID, o0.OrderDate FROM Orders AS o LEFT JOIN Customers AS c ON o.CustomerID = c.CustomerID LEFT JOIN Orders AS o0 ON c.CustomerID = o0.CustomerID WHERE o.CustomerID = 'ALFKI' ORDER BY o.OrderID, c.CustomerID

This change caused regression for us.
Could you recommend the changes for above Linq ?

[roji]

You probably need to add explicit ordering to your LINQ query as indicated above, something like the following:

var q1 = from o in ctx.Orders
where o.Customerid == "ALFKI"
orderby o.OrderId, o.Customer.CustomerId
select new { o.Orderid, o.Customer.Orders };

This should restore the SQL ORDER BY which was present in EF Core 5.0.

[archanasoni]

Thanks for the suggestion but this isn't helping us. o.Orderid and c.Customerid is already part of generated SQL without mentioning it in Linq, if we mention also result is same. We need to make some changes so that o0.Orderid gets added.
For example .NET6 sql:
SELECT o.OrderID, c.CustomerID, o0.OrderID, o0.CustomerID, o0.EmployeeID, o0.OrderDate FROM Orders AS o LEFT JOIN Customers AS c ON o.CustomerID = c.CustomerID LEFT JOIN Orders AS o0 ON c.CustomerID = o0.CustomerID WHERE o.CustomerID = 'ALFKI' ORDER BY o.OrderID, c.CustomerID

And we want to achieve :
SELECT o.OrderID, c.CustomerID, o0.OrderID, o0.CustomerID, o0.EmployeeID, o0.OrderDate FROM Orders AS o LEFT JOIN Customers AS c ON o.CustomerID == c.CustomerID LEFT JOIN Orders AS o0 ON c.CustomerID == o0.CustomerID WHERE o.CustomerID == 'ALFKI' ORDER BY o.OrderID ASC, c.CustomerID ASC, o0.OrderID ASC

We are not sure what changes are needed in Linq query so that o0.Orderid will be taken care as part of order by clause..

[roji]

My suggestion was for your first LINQ query posted above (where 6.0 generated ORDER BY c.CUSTOMERID and you wanted ORDER BY c.CUSTOMERID, o.ORDERID.

For the second query, you must again explicitly specify the order. This can be done as follows:

var q1 = from o in ctx.Orders
where o.Customerid == "ALFKI"
select new { o.Orderid, Orders = o.Customer.Orders.OrderBy(o2 => o2.OrderId).ToList() };

The principle is always the same: the database doesn't contain an inherent ordering for any rows (i.e. a given customer's orders above). If your LINQ query doesn't explicitly specify the ordering with OrderBy, you get back non-determinstic results.

[archanasoni]

Thanks for the suggestions, we will work further internally to update the testcases.