Home

Error for Oracle views using EF Core provider, when view returns multiple identity colums

raxuser100raxuser100
edited October 2022

When connecting to an Oracle database, using the EF Core provider. The auto generated DbContext configuration throws an error, for read only views that include multiple columns with auto identity. For example, a simple view that joins 3 tables and includes the PK id (id is auto identity) of those tables, will throw an error.

Environment:

  1. Oracle Database 21c Enterprise Edition Release 21.0.0.0.0
  2. Db running on OCI Cloud
  3. LinqPad 7
  4. Entity Framework Core Connection, using v6.21.61

Linqpad query script

Just reference the database as connection, no need to include any code. Hit run.

void Main()
{
   "hello world".Dump();
}

Error:

Message The properties 'VW_MEMBIN_MEMO_NOTE_LINK_NAMESPACE_FULL.NOTE_ID', 'VW_MEMBIN_MEMO_NOTE_LINK_NAMESPACE_FULL.NOTE_LINK_NAMESPACE_ID' are configured to use 'Identity' value generation and are mapped to the same table 'MAIN.VW_MEMBIN_MEMO_NOTE_LINK_NAMESPACE_FULL', but only one column per table can be configured as 'Identity'. Call 'ValueGeneratedNever' in 'OnModelCreating' for properties that should not use 'Identity'.
Data    
(0 items)
InnerException  null
HelpLink    null
Source  Oracle.EntityFrameworkCore
HResult -2146233079

Example Db view

The database views are basic and just join on a few tables. The columns returned include the auto identity Pks of joined tables. For example this view will throw an error

CREATE or REPLACE VIEW vw_invoice_item AS
   SELECT i.id invoice_id, it.id invoice_item_id
   tb_invoice i inner join
   tb_invoice_item it on it.invoice_id = i.id
WITH READ ONLY

DbContext decompile

LinqPad auto generates the model. The auto identity columns are configured using ValueGeneratedOnAdd(), I think this causes the error. The decompilation is show below:

    modelBuilder.Entity(delegate(EntityTypeBuilder<VW_MEMBIN_MEMO_NOTE_LINK_NAMESPACE_FULL> entity)
    {
        entity.ToTable("VW_MEMBIN_MEMO_NOTE_LINK_NAMESPACE_FULL", "MAIN");
        entity.HasNoKey();
        entity.Property((VW_MEMBIN_MEMO_NOTE_LINK_NAMESPACE_FULL e) => e.NOTE_ID).ValueGeneratedOnAdd();
        -- other config code
        entity.Property((VW_MEMBIN_MEMO_NOTE_LINK_NAMESPACE_FULL e) => e.NOTE_LINK_NAMESPACE_ID).ValueGeneratedOnAdd(); });

Here the columns NOTE_LINK_NAMESPACE_ID and NOTE_ID are both configured using ValueGeneratedOnAdd(), causing an error. The view is read only, so no columns should be configured with ValueGeneratedOnAdd().

Possible solutions

  1. If the view is read only, then ensure all columns are configured as such. Remove column configuration calling ValueGeneratedOnAdd().
  2. Ability to somehow customise the DbContext model in the LinqPad query before execution (not sure if possible!)

Full exception trace

   at Oracle.EntityFrameworkCore.Infrastructure.Internal.OracleModelValidator.ValidateSharedColumnsCompatibility(IReadOnlyList`1 mappedTypes, StoreObjectIdentifier& storeObject, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.ValidateSharedTableCompatibility(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Oracle.EntityFrameworkCore.Infrastructure.Internal.OracleModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelRuntimeInitializer.Initialize(IModel model, Boolean designTime, IDiagnosticsLogger`1 validationLogger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, ModelCreationDependencies modelCreationDependencies, Boolean designTime)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel(Boolean designTime)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__8_4(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, 
-- chopped here because LinqPad forum limits size of post
· Share on FacebookShare on Twitter

Comments

Sign In or Register to comment.