Efcore: Scaffolding issue with Oracle function-based index

[kdrapel]

When performing a scaffolding from an existing Oracle 11g database (using Devart Dotconnect) with the following command:

Scaffold-DbContext "DATA SOURCE=(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=XXXXXX) (PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=YYYY))); PASSWORD=aaaa;USER ID=bbbb" Devart.Data.Oracle.Entity.EFCore

I encounter an exception about a "Value cannot be null". After narrowing down the culprit table, it is caused by a function-based index on a table

System.ArgumentNullException: Value cannot be null.
Parameter name: column
   at Microsoft.EntityFrameworkCore.Utilities.Check.NotNull[T](T value, String parameterName)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.GetPropertyName(DatabaseColumn column)
   at System.Linq.Enumerable.SelectListIterator`2.ToArray()
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndex(EntityTypeBuilder builder, DatabaseIndex index)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndexes(EntityTypeBuilder builder, ICollection`1 indexes)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTables(ModelBuilder modelBuilder, ICollection`1 tables)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseModel)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.CreateFromDatabaseModel(DatabaseModel databaseModel, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ModelScaffolder.Generate(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, String projectPath, String outputPath, String rootNamespace, String contextName, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

Steps to reproduce

The bug will happen as soon as the visitor arrives on a table containing a function-based index like this one below, where USERNAME_TXT is a VARCHAR2 (30 CHAR).

CREATE INDEX "FAULTYINDEX" ON "TABLE" (UPPER("USERNAME_TXT"))

Oracle will create this index whose COLUMN_NAME is in fact something like "SYS_NC00019$" in my case. This column does not exist as such on the table and I think this is what is leading to the problem with the null exception. I had the same issue occuring on another table of the schema that had the same kind of index construction.

From http://www.dba-oracle.com/oracle11g/oracle_11g_function_based_columns.htm:

_Oracle 11g has introduced a new feature that allows you to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary)._

Further technical details

EF Core version: 2.0.1
Database Provider: Devart.Data.Oracle.Entity.EFCore 9.5.399
Operating system: W7
IDE: VS 2017 Community

[ralmsdeveloper]

Hello @kdrapel,
Probably in Scaffolding, your component is setting some value to the Default field, when there are virtual columns we set the default value to null, because this information is calculated on the server.

there is an example here that you can understand, maybe it's good to get in touch with your component vendor, at EF Core you are only working with information fed by the scaffolding collaborator.

[kdrapel]

Thanks @ralmsdeveloper for your feedback, I contacted the vendor regarding this bug.

[ralmsdeveloper]

@ajcvickers you can closed?

[kdrapel]

@ralmsdeveloper Just one last question, I somehow expected the error message to at least indicate the faulty table or/and index, is this lack of information related to way the vendor is providing you context or is there something to do on your side. Is there a way to make this more verbose?

[ralmsdeveloper]

Hello @kdrapel,
Records are sent by methods, in scaffolding generation, of each provider.
For you to visualize what is happening, configuring something like this in your DbContext:

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var loggerFactory = new LoggerFactory();
        loggerFactory.AddProvider(new TraceLoggerProvider());
        optionsBuilder.UseLoggerFactory(loggerFactory); 
    }
}

class:

public class TraceLogger : ILogger
{
    private readonly string LogsSample;
    public TraceLogger(string LogsSample) => this.LogsSample = LogsSample;
    public bool IsEnabled(LogLevel logLevel) => true;

    public void Log<TState>(
        LogLevel logLevel,
        EventId eventId,
        TState state,
        Exception exception,
        Func<TState, Exception, string> formatter)
    {
        var Log = formatter(state, exception);
        if (!string.IsNullOrWhiteSpace(Log))
        {
            Trace.WriteLine(Log);
        }
    }

    public IDisposable BeginScope<TState>(TState state) => null;
}

public class TraceLoggerProvider : ILoggerProvider
{
    public ILogger CreateLogger(string LogsSample) => new TraceLogger(LogsSample);

    public void Dispose() { }
}

[ajcvickers]

Note from triage: we will investigate if there is some assumption in our code that needs to change to avoid this.

[bricelam]

This is just a bug in the Oracle provider. The DatabaseIndex objects returned by the provider should only be for indexes over columns--not expressions/functions.

The could possibly be fixed by querying additional metadata about the index from the ALL_INDEXES view.