Entity Framework Core连接SQLite提示Users表不存在求助

问题背景 #

开发一个从SQLite取数的微服务项目，分为Microservice.Database和Microservice.UserService两个独立项目。已创建含Users表并填充数据的SQLite数据库，但用EF Core查询时持续报错：

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: Users'

已核对路径、通过SQLite浏览器确认表存在，反复检查配置仍无法解决。

项目结构 #

Microservice
|
Microservice.Database
|   |UserDbContext.cs
|   |users.db
|
|Microservice.UserService
|   |appsettings.json
|   |Program.cs

异常堆栈信息 #

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: Users'
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)
at Microservice.UserService.UserService.GetAll() in C:\Users\ljhha\Documents\RnD\ProjectSetup\Microservice\Microservice.UserService\UserService.cs:line 11
at Microservice.UserService.Controllers.UsersController.GetAsync() in C:\Users\ljhha\Documents\RnD\ProjectSetup\Microservice\Microservice.UserService\Controllers\UsersController.cs:line 27
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
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.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

相关代码文件 #

UserDbContext.cs #

using Microsoft.EntityFrameworkCore;
using Microservice.Database.Entities;

namespace Microservice.Database
{
    public class UsersDbContext : DbContext
    {
        public DbSet<User>? Users { get; set; }

        public UsersDbContext() 
        { 
            // generate migrations, you need this default constructor          
        }

        public UsersDbContext(DbContextOptions<UsersDbContext> options) : base(options)
        {
            // Database.EnsureCreated();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=users.db");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            SeedData.Seed(modelBuilder);
        }
    }
}

Microservice.Database.csproj #

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <TargetFramework>net7.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="7.0.0" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.20">
            <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
            <PrivateAssets>all</PrivateAssets>
        </PackageReference>
        <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.0" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="7.0.0">
            <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
            <PrivateAssets>all</PrivateAssets>
        </PackageReference>
    </ItemGroup>
</Project>

appsettings.json #

{
    "ConnectionStrings": 
    {
        "DefaultConnection": "Data Source=..\Microservice.Database\users.db"
    },
    "Logging": 
    {
        "LogLevel": 
        {
            "Default": "Information",
            "Microsoft": "Warning",
            "Microsoft.Hosting.Lifetime": "Information"
        }
    },
    "AllowedHosts": "*"
}

Microservice.UserService\Program.cs #

using Microsoft.EntityFrameworkCore;
using Microservice.Database;
using Microservice.UserService;
using Microservice.UserService.Mappers;
using AutoMapper;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.services
                .AddEntityFrameworkSqlite()
                .AddDbContext<UsersDbContext>(options =>options.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddScoped<IUserService, UserService>();
builder.Services.AddAutoMapper(AppDomain.CurrentDomain.GetAssemblies());

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseDeveloperExceptionPage();
}

app.UseHttpsRedirection();
app.UseAuthorization();

app.MapControllers();

app.Run();

Microservice.UserService\Microservice.UserService.csproj #

<Project Sdk="Microsoft.NET.Sdk.Web">
    <PropertyGroup>
        <TargetFramework>net7.0</TargetFramework>
        <Nullable>enable</Nullable>
        <ImplicitUsings>enable</ImplicitUsings>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="12.0.1" />
        <PackageReference Include="Microsoft.AspNetCore.Mvc.NewtonsoftJson" Version="7.0.0" />
        <PackageReference Include="Microsoft.AspNetCore.Mvc.Versioning" Version="5.0.0" />
        <PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="7.0.0" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.20">
            <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
            <PrivateAssets>all</PrivateAssets>
        </PackageReference>
        <PackageReference Include="Swashbuckle.AspNetCore" Version="6.5.0" />
    </ItemGroup>

    <ItemGroup>
        <ProjectReference Include="..\Microservice.Database\Microservice.Database.csproj" />
    </ItemGroup>
</Project>

已尝试的排查方向 #

• 修改连接字符串
• 启用Database.EnsureCreated();
• 检查Entity Framework Core版本差异
• 确认数据库中是否存在该表

排查与解决方案 #

1. 修复OnConfiguring方法的覆盖问题 #

UsersDbContext中的OnConfiguring方法硬编码了连接字符串，会覆盖Program.cs中通过依赖注入传入的配置。当使用依赖注入配置DbContext时，需要让OnConfiguring仅在未配置选项时生效：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    // 仅当外部未配置选项时才使用默认连接字符串
    if (!optionsBuilder.IsConfigured)
    {
        optionsBuilder.UseSqlite("Data Source=users.db");
    }
}

2. 修复Program.cs中的代码错误 #

Program.cs中有一处语法错误：builder.Services.services应为builder.Services，修正后：

builder.Services
        .AddEntityFrameworkSqlite()
        .AddDbContext<UsersDbContext>(options => 
            options.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection")));

3. 修正连接字符串的路径转义 #

JSON文件中路径的反斜杠需要转义，否则会被解析为转义字符。修改appsettings.json中的连接字符串：

"DefaultConnection": "Data Source=..\\Microservice.Database\\users.db"

或者使用正斜杠：

"DefaultConnection": "Data Source=../Microservice.Database/users.db"

4. 确保数据库文件被复制到输出目录 #

右键Microservice.Database项目中的users.db文件，设置复制到输出目录为如果较新则复制或始终复制，否则服务项目运行时无法找到数据库文件，会自动创建一个空的新数据库，导致表不存在。

5. 检查表名大小写与映射 #

SQLite默认大小写敏感（取决于操作系统），确认User实体的表名映射是否正确：

• 若实体未显式配置表名，EF Core会将DbSet<User> Users映射为Users表，但需确保数据库中的表名确实是Users（而非users或其他大小写变体）。
• 可在OnModelCreating中显式指定表名：

modelBuilder.Entity<User>().ToTable("Users");

6. 排查SeedData的影响 #

SeedData.Seed(modelBuilder)可能会修改表结构或导致迁移冲突，暂时注释掉该代码，测试是否能正常查询，排除种子数据的干扰。

7. 验证实际连接的数据库路径 #

在UsersDbContext的构造函数中添加日志，输出实际连接的数据库路径，确认是否连接到了正确的文件：

public UsersDbContext(DbContextOptions<UsersDbContext> options) : base(options)
{
    var connection = Database.GetDbConnection();
    Console.WriteLine($"Connected to: {connection.ConnectionString}");
}

运行服务后查看控制台输出，确认路径指向的是存在Users表的数据库文件。

内容的提问来源于stack exchange，提问作者IT SSL