Translate Guid.NewGuid()

[eshangin]

Hi. Today tried to apply .OrderBy(item => Guid.NewGuid()) to get items in random order but as I see Npgsql unable to translate it to Postgres query. In case of MsSQL it works and translates to order by newid() query.
I think it will be good to have the same kind feature in Npgsql. Maybe it could be in EF.Functions, I'm not sure...

[roji]

Can you please post full details on what happens (exception stack trace, etc.)? Npgsql is definitely supposed to translate Guid.NewGuid()...

[austindrenski]

Some version info would be useful here too (e.g. PostgreSQL, Npgsql provider).

I could see this getting caught up in an evaluatable filter and the ordering being done on the client (i.e. no exception, just log warnings about local eval). But that's just speculation without version info.

[eshangin]

"PostgreSQL 9.6.10, compiled by Visual C++ build 1800, 64-bit"
We use package https://www.nuget.org/packages/Npgsql.EntityFrameworkCore.PostgreSQL/2.1.2

The code looks like this:

var result = query.OrderBy(item => Guid.NewGuid()).Skip(offset_value).Take(limit_value).ToArray();

There is no exception. As I see it just calls query like select * from "TableName". Then seems it makes order by new guid, skip, take in memory.
But it will be good if random ordering will be done in database using query like select * from "TableName" order by random() limit {limit_value} offset {offset_value}

[austindrenski]

@eshangin Thanks for supplying the additional information.

@roji It looks like we don't actually have a method translator for Guid.NewGuid(), and it doesn't look like there's a default implementation from EF Core (e.g. SqlServer has its own implementation in SqlServerNewGuidTranslator).

Am I overlooking something, or should we add the translator?

Design notes

• Presumably this would map to uuid_generate_v4() from the uuid-ossp extension.
• Since it's an extension, we wouldn't want it enabled by default, so possible options include:
  • Handle as a plugin package for uuid-ossp.
  • Handle by checking for a model annotation.

[YohDeadfall]

The problem here is in the uuid type which doesn't have random generators out of the box. Therefore, the user should use one of two extensions. We can't dictate which one must be used. It's a documented limitation.

To workaround the issue create an extension you want and a database function. For example:

// Requires pgcrypto
[DbFunction]
public static Guid GenRandomUuid() => throw new NotImplementedException();

[eshangin]

The goal is to make random ordering. We could use .OrderBy(item => Guid.NewGuid()) with MsSQL and it is translated to order by newid(). For postgress I think we should get order by random() in the result. So maybe is should be one new EF.Functions which will be used like .OrderBy(item => EF.Functions.RandomOrder()) ? I'm not sure at the moment if new function like this could be implemented, just an idea...

[roji]

@austindrenski you're right - seems like we're missing a translator for Guid.NewGuid(). However, I think we can add this as a regular translator, on by default - the fact that something is a PostgreSQL extension doesn't necessarily mean that on the Npgsql side it needs to be supported as a plugin... A good example is the hstore type, which is an extension but is supported out of the box. I think this is OK for extensions which are pretty "standard". One reason I think that a plugin isn't very useful here, is that the user would still have to manually add the uuid-ossp extension on the model (unless I'm mistaken we can't do that for them from a plugin).

If we do add support out-of-the-box, and the user uses Guid.NewGuid() without adding the extension, they'd simply get a PostgreSQL exception about the function not being found, which seems OK to me. If we want to go further, I think that would require the translator to have access to the model, where it could check whether the uuid-ossp was specified or not, and raise a more explicit exception. IMHO it's not really worth it...

As @YohDeadfall says, the user can choose between the two extensions - although from my PostgreSQL experience uuid-ossp is by far more widely-used. Even if we make that supported out-of-the-box, a plugin could still be used to override the translation and use another one.

What do you guys think? I'm not totally against the idea of a plugin, but it seems easier and more natural to just have it in.

[roji]

On an unrelated note, does it make sense to think about translating Math.Random() System.Random() to PostgreSQL random() (instead of a custom EF.Functions.RandomOrder() as @YohDeadfall proposed)? That could be used for random ordering without any complications...

[austindrenski]

Ah that's right, I forgot that we do something similar with hstore. Agreed that a plugin would be overkill for one item here. I can start putting together a PR.

Do you mean System.Random()? I think that's currently excluded by an evaluatable filter, but I do think that would be preferred to adding something on EF.Functions. (Unless there's a compelling reason to keep it excluded, of course.)

[roji]

@austindrenski great, will look forward to your PR.

Of course you're right - I meant System.Random(), and you're right that it's excluded in the filter. The explanation for that is given in dotnet/efcore#12672: if it weren't in the filter, EF Core would evaluate it exactly once and use the same value across all rows. This is because at the moment the concept of "evaluatable" is very coarse, and more nuances are needed (evaluate on server but cache across rows, evaluate on server but don't cache across rows...).

But to go back to the original question: the fact that Math.Random() is excluded by the filter means that it's always server-evaluated, so we can translate it, can't we? After all it's logically identical to generating a random new UUID...

[austindrenski]

the fact that Math.Random() is excluded by the filter means that it's always server-evaluated, so we can translate it, can't we?

Ope, you're right. I had that backwards (again). We should be good to translate it.