We have this expression
.Select(x => x.First())
Which record will be first for that expression? There’s no way to know, because at this point the
OrderBy() clause which follows hasn’t processed yet. You could get different results each time you run the same query on the same data, depending on what order the records were returned from the database. The results are not predictable, exactly as the error message said.
But surely the database will return them in the same order each time? No, you can’t assume that. The order of results in an SQL query is not defined unless there is an ORDER BY clause with the query. Most of the time you’ll get primary key ordering (which does not have to match insert order!), but there are lots of things that can change this: matching a different index, JOIN to a table with a different order or different index, parallel execution with another query on the same table + round robin index walking, and much more.
To fix this, you must call
OrderBy() before you can call
Looking a little deeper, this is not even part of the SQL. This work is happening on your client. That’s not good, because any indexes on the table are no longer available. It should be possible to do all this work on the database server, but selecting the first record of a group may mean you need a lateral join/APPLY or row_number() windowing function, which are hard to reproduce with EF. To completely remove all warnings, you may have to write a raw SQL statement:
select userId, teamId, name, pid from ( select u.userId, t.teamId, t.name, r.pid, row_number() over (order by u.userId) rn from User u inner join resource r on r.userId = u.userId inner join team t on t.bossId = u.bossId where r.pid = @pid ) d where d.rn = 1
Looking around, it is possible to use row_number() in EF, but at this point I personally find the SQL much easier to work with. My view is ORMs don’t help for these more complicated queries, because you still have to know the SQL you want, and you also have to know the intricacies of the ORM in order to build it. In other words, the tool that was supposed to make your job easier made it harder instead.
CLICK HERE to find out more related problems solutions.