Entity Framework Core reports a column that does not exist in any model nor the database and seems as TableNavigation property

Question

When I try to list all my parkings, I'm getting this error

Invalid column name 'ParkingUserUserId'

I came across this EF Core issue: https://github.com/dotnet/efcore/issues/15021

I have checked my model and scripts without finding the reason.

The table was created like this

-- Create the ParkingSpot table
CREATE TABLE ParkingSpot 
(
    ParkingSpotId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Address NVARCHAR(255) NOT NULL,
    CityId INT NOT NULL,
    Latitude DECIMAL(10, 6) NOT NULL,
    Longitude DECIMAL(10, 6) NOT NULL,
    Status NVARCHAR(20) NOT NULL,
    UserId INT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (CityId) REFERENCES City(CityId),
    FOREIGN KEY (UserId) REFERENCES ParkingUser(UserId)
);

And the model is defined like this

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WebApi.Models
{
    [Table("ParkingSpot")]
    public class ParkingSpot
    {
        [Key]
        public int ParkingSpotId { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public int CityId { get; set; }
        public decimal Latitude { get; set; }
        public decimal Longitude { get; set; }
        public string Status { get; set; }
        public int UserId { get; set; }

        // Navigation property for the user who created this parking spot
        [ForeignKey("UserId")]
        public ParkingUser ParkingUser { get; set; }
        public DateTime CreatedAt { get; set; }

        // Navigation property for CurrentCapacity
        public ICollection<CurrentCapacity> CurrentCapacities { get; set; }

        // Navigation property for MaxCapacity
        public ICollection<MaxCapacity> MaxCapacities { get; set; }

        // Navigation property for City
        [ForeignKey("CityId")]
        public City City { get; set; }
    }
}

As you can see there is a foreign key called ParkingUser(UserId) but there is no column called ParkingUserUserId and if EF Core is unable to use that navigation property, where is it showing me details? How can I fix this?

Here the full error log

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'ParkingUserUserId'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ParkingService.GetAllParkingSpots() in /app/services/ParkingService.cs:line 16
   at WebApi.controllers.WebApi.Controllers.ParkingController.ListParkings() in /app/controllers/ParkingController.cs:line 34
   at lambda_method2(Closure, Object, Object[])
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|7_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
ClientConnectionId:a83ccbda-8f8e-4873-afa2-42b2134f8460
Error Number:207,State:1,Class:16

Here is the ParkingUser model

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WebApi.Models
{
    [Table("ParkingUser")]
    public class ParkingUser
    {
        [Key]
        public int UserId { get; set; }
        public string Username { get; set; }
        public string Email { get; set; }
        public string Password { get; set; }
        public string Role { get; set; }

        // Navigation properties
        public ICollection<Rating> Ratings { get; set; }
        public ICollection<ParkingSpot> CreatedParkingSpots { get; set; }
    }
}

and here ParkingDbContext

using Microsoft.EntityFrameworkCore;
using WebApi.Models;

namespace WebApi.Data
{
    public class ParkingDbContext : DbContext
    {
        public ParkingDbContext(DbContextOptions<ParkingDbContext> options) : base(options) { }

        // DbSet properties for all entities
        public DbSet<ParkingUser> ParkingUsers { get; set; }
        public DbSet<Country> Countries { get; set; }
        public DbSet<City> Cities { get; set; }
        public DbSet<ParkingSpotType> ParkingSpotTypes { get; set; }
        public DbSet<ParkingSpot> ParkingSpots { get; set; }
        public DbSet<MaxCapacity> MaxCapacities { get; set; }
        public DbSet<CurrentCapacity> CurrentCapacities { get; set; }
        public DbSet<Rating> Ratings { get; set; }
        public DbSet<SpotType> SpotTypes { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure relationships using Fluent API

            // Relationship between ParkingSpot and City
            modelBuilder.Entity<ParkingSpot>()
                .HasOne(p => p.City)
                .WithMany(c => c.ParkingSpots)
                .HasForeignKey(p => p.CityId);

            // Relationship between ParkingSpot and ParkingUser (CreatedBy)
            modelBuilder.Entity<ParkingSpot>()
                .HasOne(p => p.ParkingUser)
                .WithMany()
                .HasForeignKey(p => p.UserId);

            // Relationship between MaxCapacity and ParkingSpot
            modelBuilder.Entity<MaxCapacity>()
                .HasOne(mc => mc.ParkingSpot)
                .WithMany(ps => ps.MaxCapacities)
                .HasForeignKey(mc => mc.ParkingSpotId);

            // Relationship between CurrentCapacity and ParkingSpot
            modelBuilder.Entity<CurrentCapacity>()
                .HasOne(cc => cc.ParkingSpot)
                .WithMany(ps => ps.CurrentCapacities)
                .HasForeignKey(cc => cc.ParkingSpotId);

            // Add other relationships as needed

            base.OnModelCreating(modelBuilder);
        }
    }
}

Answer 1

Here

// Relationship between ParkingSpot and ParkingUser (CreatedBy)
modelBuilder.Entity<ParkingSpot>()
    .HasOne(p => p.ParkingUser)
    .WithMany() // <---
    .HasForeignKey(p => p.UserId);

you left out ParkingUser.CreatedParkingSpots collection navigation property unmapped. What happens in such case is that EF assumes it is defining a new separate unidirectional one-to-many relationship with FK conventionally named ParkingUserUserId ({Principal Entity Name}{Principal Entity PK Name}). If you were using EF migrations for maintaining the db schema, you would have seen that in the generated migration.

Of course having second relationship wasn't your intention, so simply associate the navigation property with the existing relationship:

// Relationship between ParkingSpot and ParkingUser (CreatedBy)
modelBuilder.Entity<ParkingSpot>()
    .HasOne(p => p.ParkingUser)
    .WithMany(u => u.CreatedParkingSpots) // <---
    .HasForeignKey(p => p.UserId);

and in general, make sure optional parameters of {Has|With}{One|Many} fluent calls always match the presence or absence of the corresponding navigation property, since skipping the parameter means no property rather than use default property.