如何用ASP.NET Core和EF Core运行时创建设备专属SQLite数据库？

1. 可复用的DbContext实现 #

创建支持多数据库类型的DbContext，通过传入的连接字符串自动适配SQL Server或SQLite：

public class AppDbContext : DbContext
{
    private readonly string _connectionString;

    public AppDbContext(string connectionString) => _connectionString = connectionString;

    // 替换为你的实际实体集合
    public DbSet<DeviceData> DeviceData { get; set; }
    public DbSet<DeviceConfig> DeviceConfig { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (_connectionString.EndsWith(".db"))
        {
            optionsBuilder.UseSqlite(_connectionString);
        }
        else
        {
            optionsBuilder.UseSqlServer(_connectionString);
        }
    }
}

2. 核心接口实现 #

在Controller中完成「生成SQLite库→导入主库数据→返回文件→清理临时文件」的完整流程：

[ApiController]
[Route("api/devices")]
public class DeviceOfflineDbController : ControllerBase
{
    private readonly IConfiguration _config;

    public DeviceOfflineDbController(IConfiguration config) => _config = config;

    [HttpGet("offline-db/{deviceId}")]
    public async Task<IActionResult> GenerateOfflineDb(string deviceId)
    {
        var tempDbPath = Path.Combine(Path.GetTempPath(), "deneme.db");
        
        // 预清理残留文件
        if (File.Exists(tempDbPath)) File.Delete(tempDbPath);

        try
        {
            // 初始化SQLite数据库结构
            var sqliteConn = $"Data Source={tempDbPath}";
            using (var sqliteCtx = new AppDbContext(sqliteConn))
            {
                await sqliteCtx.Database.EnsureCreatedAsync();
            }

            // 从SQL Server主库拉取设备专属数据
            var mainConn = _config.GetConnectionString("MainSqlServer");
            using (var mainCtx = new AppDbContext(mainConn))
            {
                var deviceData = await mainCtx.DeviceData
                    .Where(d => d.DeviceId == deviceId)
                    .AsNoTracking() // 优化查询性能，避免跟踪实体
                    .ToListAsync();
                
                var deviceConfigs = await mainCtx.DeviceConfig
                    .Where(c => c.DeviceId == deviceId)
                    .AsNoTracking()
                    .ToListAsync();

                // 批量导入SQLite（事务保障数据完整性）
                using (var sqliteCtx = new AppDbContext(sqliteConn))
                {
                    using (var transaction = await sqliteCtx.Database.BeginTransactionAsync())
                    {
                        sqliteCtx.DeviceData.AddRange(deviceData);
                        sqliteCtx.DeviceConfig.AddRange(deviceConfigs);
                        await sqliteCtx.SaveChangesAsync();
                        await transaction.CommitAsync();
                    }
                }
            }

            // 返回生成的数据库文件
            var fileContent = await File.ReadAllBytesAsync(tempDbPath);
            return File(fileContent, "application/octet-stream", $"{deviceId}_offline.db");
        }
        finally
        {
            // 确保临时文件被清理，无论请求成功与否
            if (File.Exists(tempDbPath)) File.Delete(tempDbPath);
        }
    }
}

3. 配置主数据库连接 #

在appsettings.json中添加SQL Server连接字符串：

{
  "ConnectionStrings": {
    "MainSqlServer": "Server=你的SQL服务器地址;Database=主库名称;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

4. 关键优化与注意事项 #

• 并发安全：如果存在多请求并发场景，建议将临时文件名改为$"{deviceId}_{Guid.NewGuid()}.db"，避免文件冲突。
• 大数据量处理：若设备数据量较大，需分批导入数据，避免内存溢出；可使用Skip()和Take()实现分批查询。
• NuGet依赖：确保项目已安装Microsoft.EntityFrameworkCore、Microsoft.EntityFrameworkCore.SqlServer、Microsoft.EntityFrameworkCore.Sqlite三个包。
• 权限问题：系统临时目录（Path.GetTempPath()）通常有足够读写权限，避免使用项目目录存储临时文件引发权限问题。

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