Entity Framework Core Problem with Scaffolding entity with foreign key constraint

Question

Hi I scaffold DbContext from existing database using EF Core. The result context contains 2 related entities in OnModelCreating(..) like this:

modelBuilder.Entity<MauYdc>(entity =>
            {
                entity.HasNoKey();

                entity.ToTable("MauYDC");

                entity.HasIndex(e => e.Idphieu)
                    .HasName("IX_MauYDC")
                    .IsUnique();

                entity.Property(e => e.Idphieu)
                    .HasColumnName("IDphieu")
                    .HasColumnType("numeric(18, 0)")
                    .ValueGeneratedOnAdd();

                entity.Property(e => e.Manv).HasMaxLength(50);

                entity.Property(e => e.Tenphieu)
                    .HasColumnName("tenphieu")
                    .HasMaxLength(100);
            });

modelBuilder.Entity<MauYdcChiTiet>(entity =>
            {
                entity.HasNoKey();

                entity.ToTable("MauYDC_ChiTiet");

                entity.Property(e => e.Id)
                    .HasColumnName("ID")
                    .HasColumnType("numeric(18, 0)")
                    .ValueGeneratedOnAdd();

                entity.Property(e => e.Idphieu)
                    .HasColumnName("IDphieu")
                    .HasColumnType("numeric(18, 0)");

                entity.Property(e => e.Idthuoc).HasColumnName("idthuoc");

                entity.Property(e => e.Soluong).HasColumnName("soluong");

                entity.HasOne(d => d.IdphieuNavigation)
                    .WithMany()
                    .HasPrincipalKey(p => p.Idphieu)
                    .HasForeignKey(d => d.Idphieu)
                    .OnDelete(DeleteBehavior.Cascade)
                    .HasConstraintName("FK_MauYDC_chitiet_MauYDC");
            });

Entity MauYdc

public partial class MauYdc
    {
        public decimal Idphieu { get; set; }
        public string Tenphieu { get; set; }
        public string Manv { get; set; }
    }

Entity MauYdcChiTiet

public partial class MauYdcChiTiet
    {
        public decimal? Idphieu { get; set; }
        public int? Idthuoc { get; set; }
        public double? Soluong { get; set; }
        public decimal Id { get; set; }

        public virtual MauYdc IdphieuNavigation { get; set; }
    }

Those are completely generated by EF Core and I leave them untouched. However, I encounter this error when using other table from the context not related to those two

NullReferenceException: Object reference not set to an instance of an object.
Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.SetOrAddForeignKey(ForeignKey foreignKey, InternalEntityTypeBuilder principalEntityTypeBuilder, IReadOnlyList<Property> dependentProperties, Key principalKey, string navigationToPrincipalName, Nullable<bool> isRequired, Nullable<ConfigurationSource> configurationSource)
Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.CreateForeignKey(InternalEntityTypeBuilder principalEntityTypeBuilder, IReadOnlyList<Property> dependentProperties, Key principalKey, string navigationToPrincipalName, Nullable<bool> isRequired, ConfigurationSource configurationSource)
Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.HasRelationship(EntityType targetEntityType, Nullable<MemberIdentity> navigationToTarget, Nullable<MemberIdentity> inverseNavigation, bool setTargetAsPrincipal, ConfigurationSource configurationSource, Nullable<bool> required)
Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.HasRelationship(EntityType targetEntityType, MemberInfo navigationProperty, ConfigurationSource configurationSource, bool setTargetAsPrincipal)
Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder<TEntity>.HasOne<TRelatedEntity>(Expression<Func<TEntity, TRelatedEntity>> navigationExpression)
Hl7Map.Infrastructure.SqlServer.NgoaiTru.NgoaiTruContext+<>c.<OnModelCreating>b__322_31(EntityTypeBuilder<MauYdcChiTiet> entity) in NgoaiTruContext.cs
+
                entity.HasOne(d => d.IdphieuNavigation)
Microsoft.EntityFrameworkCore.ModelBuilder.Entity<TEntity>(Action<EntityTypeBuilder<TEntity>> buildAction)
Hl7Map.Infrastructure.SqlServer.NgoaiTru.NgoaiTruContext.OnModelCreating(ModelBuilder modelBuilder) in NgoaiTruContext.cs
+
            modelBuilder.Entity<MauYdcChiTiet>(entity =>
Microsoft.EntityFrameworkCore.Infrastructure.ModelCustomizer.Customize(ModelBuilder modelBuilder, DbContext context)
Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder)
Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder)
Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder+<>c.<TryAddCoreServices>b__7_3(IServiceProvider p)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor<TArgument, TResult>.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor<TArgument, TResult>.VisitCallSite(ServiceCallSite callSite, TArgument argument)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor<TArgument, TResult>.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor<TArgument, TResult>.VisitCallSite(ServiceCallSite callSite, TArgument argument)
Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine+<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService<T>(IServiceProvider provider)
Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
Microsoft.EntityFrameworkCore.DbContext.get_Model()
Microsoft.EntityFrameworkCore.Internal.InternalDbSet<TEntity>.get_EntityType()
Microsoft.EntityFrameworkCore.Internal.InternalDbSet<TEntity>.CheckState()
Microsoft.EntityFrameworkCore.Internal.InternalDbSet<TEntity>.get_EntityQueryable()
Microsoft.EntityFrameworkCore.Internal.InternalDbSet<TEntity>.System.Linq.IQueryable.get_Provider()
System.Linq.Queryable.Where<TSource>(IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
Hl7Map.Manager.Administration.PatientManager.Get(string IdBenhNhan) in PatientManager.cs
+
            var tttn= _myContext.MyOtherTable.Where(ttTiepnhan => ttTiepnhan.MaBn == IdBenhNhan)?.FirstOrDefault();
Hl7Map.Areas.FHIR.Controllers.PatientsController.Get(string idBn) in PatientsController.cs
+
            var pt = await _patientManager.Get(idBn);
lambda_method(Closure , object )
Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable+Awaiter.GetResult()
Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
System.Threading.Tasks.ValueTask<TResult>.get_Result()
System.Runtime.CompilerServices.ValueTaskAwaiter<TResult>.GetResult()
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask<IActionResult> actionResultValueTask)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

I already use lazy loading for EF Core:

services.AddDbContext<MyContext>(options =>
                options.UseLazyLoadingProxies().UseSqlServer(config.GetConnectionString("MyConnectionString"))
            );

Debug shows the program stuck while running Context constructor and entity builder ==> so it didn't even reach the Where() expression, so add ? after Where(..) won't fix.

What is the problem with EF Core? I'm stuck for this so long.

Answer 1

After trying to make it work with the existing database => and fail, the only solution is add key to both tables (principal and referencing tables) for EF Core to function properly. EF does not allow for foreign key constraints on no key tables.

Answer 2

Complementing the answer ...

In practice, this error occurs because the entity was defined as "No key"

So, when the context is initialized, it generates error when defining the relationship ...

That HasNoKey was included by the scaffold process, because it did not identify a Primary Key field within my 'Departamento' table