Inefficient (?) SQL generated for Guid queries on Oracle

[ghost]

When using Guid as keys on Oracle DBMS, EF.Core 2.2 (or possibly Oracle.EntityFrameworkCore 2.19.60, not sure who is responsible here) the generated SQL causes the DBMS to always skip the index and instead do a full table scan. As you can imagine, this causes massive performance issues.

Steps to reproduce

1. Define an Entity with an indexed Guid field (e.g. the PK)

public class MyEntity
{
     public Guid Id { get; set; }
}

The Id-Field will correctly be converted into a RAW(16) column in the database.

2. Try to query a specific entity based on its ID.

   var me = dbContext.MyEntities.Where<MyEntity>(e => e.Id == someGuid).ToListAsync();

This leads to SQL of the form

SELECT Id FROM MyEntities WHERE Id = N'[someGuid as a string]'

3. Copy the respective query into your database tool of choice and look at the execution plan. It will (hopefully, or else the error is on my side, which is of course always possible) show a FULL TABLE ACCESS instead of using the primary key index.

4. Modify the SQL-Query e.g. to have HEXTORAW('[someGuid as a string]') and look at the execution plan again. This time you should see the index being hit (and, for large tables, see a massively improved performance).

Further technical details

EF Core version: 2.2.6
Database provider: Oracle.EntityFrameworkCore 2.19.60
Target framework: (e.g. .NET Core 2.1)
Operating system: Windows
IDE: (Visual Studio 2019 16.5.3)

[ajcvickers]

@markusbrueckner Thanks for reaching out. Unfortunately, this is an issue with literal generation in the Oracle provider. It will need to be reported to them.

[ghost]

Thank you for your quick reply. I'll see, whether I can take it to Oracle then.