What is Guid.NewGuid() doing in LINQ to Entities?

Question

I have a LINQ to Entities written, which is making use of the following OrderBy:

.OrderBy(i => Guid.NewGuid())

The above randomises the ordering of the dataset, so each run of this code produces a different ordering, everytime.

However, if I do any of the following, the dataset is NOT randomised and is the exact SAME for all 3 OrderBy, see below:

.OrderBy(i => new Guid("5fd3e5e7-b172-42f5-a4dd-da4212201a31"))
.OrderBy(i => new Guid("beb7345c-1421-48e0-b177-51b2bb065214"))

Guid g = Guid.NewGuid();
.OrderBy(i => g)

You can see the 3 OrderBy above are using different Guids, so why do they produce the same order? Why is it that Guid.NewGuid() randomises the list everytime I run this piece of code, but the other 3 OrderBy statements produce the same result everytime?

My issue is, I need to pass in a value into a function, which is used within the LINQ to Entities dataset to randomise the results depending on the value, however I also need to be able to pass the same value and retrieve the same order of randomness.

Thanks.

Answer 1

This behavior when you have Guid.NewGuid() is by design in Entity Framework, it mimics SQL's ORDER BY NEWID(), to allow random ordering. When you specify a constant Guid, it merely orders by this constant value.

Answer 2

The code .OrderBy(i => Guid.NewGuid()) passes an expression which is evaluated for each row to find something to order it by. Written like this, for each value it will call Guid.NewGuid() again, returning a different GUID and giving each row a random(ish) value to sort by.

This is NOT the same as calling Guid.NewGuid() and passing the result to .OrderBy(i => g), because this will return the same GUID over and over, meaning that the GUID has no effect on sorting, and the collection is sorted in whatever way is the default - likely just to leave them in the order they were in before.

Answer 3

why do they produce the same order?

Because they return the same value for every row. The expression you pass to OrderBy() will be executed for every row, to yield a value that denotes the order for that row. In fact, using Guid.NewGuid() as expression for OrderBy() can cause different problems, because it isn't stable (returns different values upon successive calls for the same row). On the other hand, Guid.NewGuid() should be translated to NEWID(), negating that problem.

A fixed GUID will thus return the same value for every row.

I need to pass in a value into a function, which is used within the LINQ to Entities dataset to randomise the results depending on the value

Then redesign that function.

Answer 4

UPDATE: my original answer is true for LINQ and lambdas in general, however not for LINQ to Entities (I wish they called it LINQ for Entity Framework...). I just missed that part of the question, thanks to @Servy for pointing that out. I added a LINQ to Entities paragraph.

---

1. For LINQ to Objects (where the source data consists of .NET object collections such as List<T>, arrays, etc), this is what happens:

   A lambda statement in a clause such as .Where(x => IsAllowed(x)) or .OrderBy(x => SomeValue()) is executed once for every item that is processed. Instead of being executed once right away, the code you wrote becomes a parameter of the LINQ method, and LINQ executes it if & when it needs it.

   This means that in your case i => Guid.NewGuid() gets executed for every item, creating a new value every time.

   This is not a special feature for LINQ only, it can be applied everywhere, but LINQ certainly uses it a lot.

   If you use a method of your own, e.g. .OrderBy(i => MyMethod()) and then put a breakpoint inside MyMethod, then you will see that it gets called 0, 1 or many times, depending on the items being processed. And from the Debug Call Stack you can then see that you are inside a LINQ execution path.

2. For LINQ to Entities, things are even a bit more complicated. It still takes the code you wrote as a parameter, but this time it will only accept code that it can transform directly into SQL statements for the underlying database.

   As we can see, a lambda that has a body of Guid.NewGuid() is recognized by LINQ to EF and it becomes NEWID() in the resulting SQL statement. So although the end result and behaviour is very similar, this time it is the database is where the 'random' guids are generated, once for every row.

   Because everything must be compatible/translatable to SQL, a lot of code is now illegal. The example above with using MyMethod (so your own code) will not work now because only very specific .NET Framework calls can be translated to SQL.