1

I have a linq query which gave me the warning but it still works. I want to get rid of the warning.

uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.

The linq query is

var list = (from u in _db.user
        join r in _db.resource on u.userId equals r.userId
        join t in _db.team on u.bossId equals t.bossId
        where r.pid == pid
        select new MyDto
        {
            pid = pid,
            userId = u.userId,
            teamId = t.teamId,
            name = t.name
        }).GroupBy(d => d.userId).Select(x => x.First()).OrderBy(y => y.userId).ToList();

I use EntityFramework Core 2.1

UPDATE:

I changed the code by the comments.

var list = (from u in _db.user
        join r in _db.resource on u.userId equals r.userId
        join t in _db.team on u.bossId equals t.bossId
        where r.pid == pid
        select new MyDto
        {
            pid = pid,
            userId = u.userId,
            teamId = t.teamId,
            name = t.name
        })
        .GroupBy(d => d.userId)
        .Select(x => x.OrderBy(y => y.userId)
        .First())
        .ToList();

Then there is a different warning.

The LINQ expression 'GroupBy([user].userId, new MyDto() {pid = Convert(_8_locals1_pid_2, Int16), userId = [user].UserId, .....) could not be translated and will be evaluated locally.

Hello
  • 796
  • 8
  • 30
  • It's not clear where your warning is coming from: it isn't one of the built-in ones. Can you please share the warning's code? Typically it has the form "XX1234" – canton7 Nov 06 '20 at 14:09
  • Try :`.Select(x => x.OrderBy(r=> r.Key).First()).` – Eldar Nov 06 '20 at 14:10
  • Why would eliminating OrderBy give unpredictable results? The OrderBy is not needed so you can remove. Without a OrderBy the results is more random since code is removing the first items and depend on the order that data was written. Suppose you put all your bill every month and put them into a pile. Then at end of month you just pick the top 5 bills and pay them. and leave the rest for the next month. So what happens if you do not pay your rent? Do you get evicted? – jdweng Nov 06 '20 at 14:14
  • 2
    The warning makes sense, if you have no order defined, then what "first" means? That will be first in some database-defined order, which is not even guaranteed to be the same every time. – Evk Nov 06 '20 at 14:14
  • @jdweng, remove `OrderBy` it is same warning... – Hello Nov 06 '20 at 14:15
  • @Eldar, I tried your method. Then the warning is a different one. `The LINQ expression 'orderby [r].userId asc' could not be translated and will be evaluated locally`. – Hello Nov 06 '20 at 14:27
  • 2
    @Hello With or without warning, your query is evaluated locally. – Ivan Stoev Nov 06 '20 at 14:31
  • The group by can't be translated because it happens after you `select` the new objects. – Joel Coehoorn Nov 06 '20 at 15:04

1 Answers1

3

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 First().

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.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794