Add EF.Functions.Random()

[max619]

Is it possible to retrive random sequence of entities using mysql rnd() function and how to do it?

Obviously OrderBy(x=>Guid.NewGuid()) is not working as referenced here

[divega]

@max619, According to the message @yukozh wrote when that issue was closed, Pomelo provider would need to support translating some method to the rand() function on the server. I am not if this is supported but it would be better to ask in Pomelo.EntityFrameworkCore.MySql.

[divega]

@maumar I had a note in triage to ask you to find out if we had a spec test for this.

However, based on PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#265, it seems that OrderBy(x=>Guid.NewGuid()) not working to randomize the order on MySQL is by design, and you need to write a different query. Not sure if there is a way we could write a spec test that accommodates this. Thoughts?

[divega]

Talked to @maumar offline. We don't have spec test for this. in principle, it is probably not a good candidate for a spec test given that:

1. Obtaining randomized order is apparently going to require different queries for different databases
2. There is no good way to verify that the order obtained is random

That said, if we think of spec tests as a check list of high level functionality a provider should supply, then it seems ok to have an abstract test and let providers override it. Let's decide what to do with this on next triage.

[timia2109]

Why not add the Method DbContext.Functions.Random()?

In my mind this should be included there are many use-cases you need this.

[bricelam]

@timia2109 Agreed. I had the same thought in bricelam/EFCore.SqliteEx#10

[bricelam]

Re-opening to track adding adding a standard Random function for providers to implement. Nearly all of them return a floating-point value between 0 and 1, so I think we should use that as the spec.

Provider	SQL
SQL Server	RAND()
SQLite	abs(random() / 9223372036854775807.0)
Cosmos	RAND()
PostgreSQL	random()
MySQL	RAND()
Oracle	DBMS_RANDOM.VALUE
DB2	RAND()
Firebird	RAND()

[roji]

Opened npgsql/efcore.pg#1457 to track on the Npgsql side.

[dbrownems]

Note that mapping to RAND for SQL Server will not work for ordering, as RAND() is evaluated only once per query, not on each row (unless RAND() appears in the SELECT clause). There are only a few TSQL functions are evaluated for each row if they don't take an argument from the row. You could use something rand(checksum(newid())) generate a random(ish?) value that can be used for sorting.