ASP.Net Core Copying Data from SQL Database to SQLite

Question

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

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

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

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.

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?

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.

I need to do something like this but am considering your original approach exactly for performance reasons. Did you find that using EF save changes was much faster than doing Sqlite inserts within a transaction? — Jon, May 16 '20 at 10:52
@Jon We never had any perormance issues using EF to save changes, but we also never really felt the need to try direct sqlite inserts. So I dont really have a comparrison. — James Hogle, May 18 '20 at 20:02
Ok thanks, good to know. I was concerned that >10K records through EF would be slow. — Jon, May 19 '20 at 07:34

score 0 · Answer 1 · answered Apr 20 '18 at 15:59

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.

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

ASP.Net Core Copying Data from SQL Database to SQLite

1 Answers1

Linked