EF Core查询停车场时提示无效列'ParkingUserUserId'的问题求助

查询所有停车场数据时触发以下错误：

Invalid column name 'ParkingUserUserId'

排查数据库表结构、实体模型及上下文配置后仍未找到问题根源，相关信息如下：

数据库表创建脚本（ParkingSpot） #

-- 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)
);

ParkingSpot 实体模型 #

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; }
    }
}

ParkingUser 实体模型 #

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; }
    }
}

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);
        }
    }
}

完整错误日志 #

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

问题原因与修复方案 #

原因分析 #

EF Core 生成 ParkingUserUserId 列的核心原因是双向导航关系未正确匹配：

• ParkingSpot 定义了指向 ParkingUser 的导航属性 ParkingUser
• ParkingUser 定义了反向导航属性 CreatedParkingSpots
• 但在 OnModelCreating 的 Fluent API 配置中，仅使用了 .WithMany() 而未关联到 CreatedParkingSpots，导致 EF Core 误判为两个独立关系，自动生成了额外的外键列。

修复步骤 #

1. 修正Fluent API关系配置
   修改 ParkingDbContext 中的 ParkingSpot 与 ParkingUser 关系配置，显式关联反向导航属性：

// Relationship between ParkingSpot and ParkingUser (CreatedBy)
modelBuilder.Entity<ParkingSpot>()
    .HasOne(p => p.ParkingUser)
    .WithMany(u => u.CreatedParkingSpots) // 关联到ParkingUser的CreatedParkingSpots属性
    .HasForeignKey(p => p.UserId)
    .OnDelete(DeleteBehavior.SetNull); // 匹配数据库中UserId可空的设置

2. 修正实体模型的可空性
   数据库中 UserId 是可空列，但实体模型中 public int UserId { get; set; } 为不可空值类型，需改为可空类型：

public int? UserId { get; set; }

3. 可选：统一配置方式
   由于已使用Fluent API配置关系，可移除 ParkingSpot 中 [ForeignKey("UserId")] 特性，避免配置冲突。

内容的提问来源于stack exchange，提问作者Mauricio Gracia Gutierrez