ASP.Net Core将数据从SQL数据库复制到SQLite [英] ASP.Net Core Copying Data from SQL Database to SQLite

问题描述

我正在将应用程序转换为ASP.Net Core，要求用户在某些情况下能够脱机工作.以前，我们通过将数据复制到SQLite数据库中并将该数据下载到客户端上来进行处理.我在ASP.NET Core/Entity Framework的上下文中遇到了一些问题.

I am converting an application to ASP.Net Core that requires users to be able to work offline in certain instances. Previously, we handled this by copying data into a SQLite database, and downloading that data onto the client. I am running into some issues doing this within the context of ASP.NET Core/Entity Framework.

对于上下文，我有一个看起来像这样的模型:

For context, I have a model that looks like this:

public class Facility
{
    public Guid Id {get; set;}
    public string Code {get; set;}
    public string Description {get; set;}
}

用于构建SQLite数据库的sql脚本如下:

The sql script for building the SQLite database looks like this:

CREATE TABLE [Facility] (
[Id] uniqueidentifier NOT NULL
, [Code] nvarchar(8)  NOT NULL
, [Description] nvarchar(30)  NULL
, CONSTRAINT [PK_Facility] PRIMARY KEY ([Id])
);

我正在Sql数据库和SQLite数据库之间复制数据，如下所示:

I am copying the data between the Sql database, and the SQLite database like so:

var optionsBuilder = new DbContextOptionsBuilder<MyContext>();

optionsBuilder.UseSqlite(MyConnectionString);

using (var sqliteContext = new MyContext(optionsBuilder.Options))
{
    await sqliteContext.Fac.AddRangeAsync(CollectionOfFacilityObjects);

    await sqliteContext.SaveChangesAsync();
}

这很好用，除了将 GUID Id值插入SQLite数据库时遇到一些麻烦.在SQLite Studio中查询SQLite数据库会显示值问题.

This works well except for there being some trouble with the GUID Id values being inserted into the SQLite database. Querying the SQLite database in SQLite Studio shows the problem with the values.

我猜想这与SQLite实际上不支持官方 GUID 数据类型有关，

Im guessing this has to do with SQLite not actually supporting an official GUID datatype, as seen here. Thus the value of the GUID Id in my model classes is not translated correctly. Is there anyway that I can get the GUID value to copy over correctly?

注意:我对这个问题的最初处理方法是为需要复制的每一行生成插入语句，并在一个事务中全部运行它们.这项工作起初很有效，但在尝试插入约1万条左右的记录时很快变得不可行.

Note: My original approach to this problem was to generate insert statements for each row that needed to by copied over, and run them all in one transaction. This worked initially, but quickly became unfeasable when trying to insert >10k records or so.

推荐答案

我认为问题是您正试图重新使用相同的DbContext，并且应该为Sqlite创建单独的DbContext并为sqlite生成单独的迁移.每个提供程序生成的迁移代码都不同，当您使用sqlite提供程序生成迁移时，它知道如何处理Guid属性，并将在其自身的迁移中使用字符串.

I think the problem is you are trying to re-use the same DbContext and you should make a separate DbContext for Sqlite and generate separate migrations for sqlite. Migration code gets generated differently per provider, and when you generate migrations with sqlite provider it knows how to handle Guid properties and will use strings in its own migrations.

您可能已经进行了MSSQL迁移，并且正在尝试使用uniqueidentifier和其他特定于MSSQL的语法.

You probably already have MSSQL migrations and those are trying to use uniqueidentifier and other MSSQL specific syntax.

这篇关于ASP.Net Core将数据从SQL数据库复制到SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！