Home
Options

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

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

Comments

  • Options

    Thanks for the detailed info. The fact that it's a view is a bit of a red herring: views can be updateable, and the scenario you describe (or similar scenarios) can also occur with tables. The underlying problem is that EF Core doesn't allow multiple columns for which it infers an identity property.

    I've got a fix drafted, but won't be able to release for a couple of weeks or so, until the start of the next beta cycle (after .NET 7 RTM is released). The fix is potentially disruptive because LINQPad must make assumptions about how EF Core applies rules that are complex and not fully documented.

  • Options
    edited October 2022

    Thanks Joe, for the speedy response as always. Amazing a fix is on the way soon.

    Yes that's true, this issue is for tables and updatable views alike.

    In my case the Oracle view is marked as with read only, perhaps that was not picked up in the db metadata extraction/scaffolding by LinqPad?

  • Options

    Joe,
    Is there an update on this fix? It's becoming a real blocker for me now, as my clients have migrated to Oracle!
    Thanks

  • Options

    Thanks for the reminder - I've now merged this feature into the latest beta:
    https://www.linqpad.net/linqpad7.aspx#beta

    Let me know how you get on.

  • Options
    edited May 2023

    Hey Joe, this issue had been fixed and was working. Unfortunately it's broken again on the latest Linqpad release 7.6.6 and beta 7.7.10. Any ideas what's gone wrong?

  • Options

    Nothing's changed here; can you provide a step-by-step repro?

Sign In or Register to comment.