Thomas
August
Ryan

Load a CSV into SQLite on Startup in ASP.NET Core 6

When I deploy my ASP.NET Core 6 app I want it to gather CSV formatted data and ingest it into SQLite as part of its startup process. To that end I keep a list of the URLs and file names of the CSVs I want it to ingest in the appsettings.json file. These are then downloaded as required by the data ingest process, parsed using the CsvHelper library, and then inserted into SQLite.

After the startup process is complete, EF Core is used as the ORM to query SQLite just like it would be in any other app.

A big CSV file opened in VS Code.

To accomplish this I create an instance of my EF Core database context before I call CreateHostBuilder(args).Build().Run(); to start up the app. Using that context, I can check to see if there’s any data in the app’s database. If there is, then we do nothing. If there isn’t, then we ingest the data we want into the database.

Program.cs
using eRealProperty.Models;

using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;

using Serilog;
using Serilog.Events;

using System;
using System.Threading.Tasks;

namespace ServerSide
{
    public class Program
    {
        public async static Task Main(string[] args)
        {
            Log.Logger = new LoggerConfiguration()
                .MinimumLevel.Override("Microsoft", LogEventLevel.Information)
                .Enrich.FromLogContext()
                .WriteTo.Console()
                .CreateLogger();

            // Get the configuration keys
            var config = new ConfigurationBuilder()
                    .AddJsonFile("appsettings.json", optional: false)
                    .Build();

            // Ready the ORM configuration
            var contextOptions = new DbContextOptionsBuilder<eRealPropertyContext>()
                    .UseSqlite(config.GetConnectionString("eRealPropertyContext"))
                    .Options;

            // Put the ORM to work and make sure we have a database
            using var db = new eRealPropertyContext(contextOptions);
            await db.Database.MigrateAsync();

            // Load the data if there's nothing in the database.
            if (!await db.RealPropertyAccounts.AnyAsync())
            {
                Log.Information("Ingesting Real Accounts");
                await db.Database.ExecuteSqlRawAsync("DELETE FROM RealPropertyAccounts;");
                var accounts = await RealPropertyAccount.IngestAsync(db, 
                                            config["DataSources:RealPropertyAccount"], 
                                            config["DataSources:RealPropertyAccountFileName"]);
                
                if (accounts)
                {
                    Log.Information($"Ingested {await db.RealPropertyAccounts.CountAsync()} Real Accounts.");
                }
                else
                {
                    Log.Fatal("Failed to ingest Real Accounts.");
                }
            }

            try
            {
                Log.Information("Starting web host");
                CreateHostBuilder(args).Build().Run();
            }
            catch (Exception ex)
            {
                Log.Fatal(ex, "Host terminated unexpectedly");
            }
            finally
            {
                Log.CloseAndFlush();
            }
        }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
            .UseSerilog()
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                });
    }
}

Performant CSV Parsing

A text file containing comma separated values or a CSV is a common way to store data exported mainframes or other complex and often obscure systems. CSV’s are neat because they are so simple, just a line of values separated into columns by commas, where the first row of values are the names of the columns. CSVs can be quite small with just a couple hundred rows and a file size measured in kilobytes. But they can also scale way up into multi-gigabyte files with millions of rows.

At smaller sizes how you handle them in C#, parse data from them, and then load them into your database doesn’t matter. But when this process starts taking minutes and tens of gigabytes of memory to complete; it’s now worth it to invest some time in improving the performance of your ingest process.

I’m using the CsvHelper library to parse each row of the CSV into an object. CsvHelper is cool because minimizes the memory footprint of CSV parsing by only parsing one row at a time and then forgetting the prior row when you move to the next one. It also rids us of doing the busy work of manually serializing the fields in our csv. Rather we can use the column names to create a class that models our csv’s columns and then use csv.GetRecords() to return to us all the rows in our csv as instances of that model.

EF Core isn’t for Bulk Inserts

My app uses EF Core 6 to handle database interactions and SQLite to store its data. Unfortunately, this is not a good combination if your goal is to quickly and efficiently ingest data from a CSV file. Using EF Core to insert rows in bulk requires you to pay a penalty both in terms of memory consumption from entity tracking and in the amount of time required to ingest all the data. To avoid paying these penalties we can skip over our ORM and interact directly with SQLite using the underlying database connection.

We can use the database context EF Core provides as a starting place. The ingest function I run when the app starts up takes 3 parameters, the database context from EF Core, the URL of the csv to download, and the name of the specific file we want the app to parse and load into our database.

From the database context we can create a transaction by calling var transaction = await context.Database.BeginTransactionAsync(). Then we can create a command to execute in this transaction by calling Database.GetDbConnection().CreateCommand(); Finally when we're done with our command we can submit it to the database by calling await command.ExecuteNonQueryAsync(); and then when we are done with all the commands in our transaction we can close it out by calling await transaction.CommitAsync();

Old-school SQL Commands

query.cs
            var command = context.Database.GetDbConnection().CreateCommand();
            command.CommandText =
                $"insert into LevyCodes (Id, DistrictAbbrev, LevyCode, DistrictName, IngestedOn) " +
                $"values ($Id, $DistrictAbbrev, $LevyCode, $DistrictName, $IngestedOn);";

            var Id = command.CreateParameter();
            Id.ParameterName = "$Id";
            command.Parameters.Add(Id);

Inside of the SQL command we can set a template using command.CommandText to set a string. The string you use will be specific to the version of SQL that the underlying database supports. SQLite uses the format discussed here for insert statements. For the values statement in our SQL command we will prefix each column name in the command text with a ‘$’ to mark it as the text to be swapped with the value of a matchingparameter.

We can create an SQL parameter by calling var parameter = command.CreateParameter(); Then we will need to assign it a name so it knows what text to match against in the CommandText. I've combined the name of the column with the prefix character ‘$’ to form “$Id” as the name of the parameter. Finally we can add this parameter to our SQL command by calling command.Parameters.Add(parameter); We repeat this process for all of the values that we want to submit to SQLite in our insert command.

Read rows asynchronously and only once

Now that our database transaction and command are prepared, we need to grab data from the csv. To do this we’ll use the csv.GetRecordsAsync(); method to get an IAsyncEnumerable of our class that models each row in the csv. This return type is both weird and cool because it allows us to asynchronously iterate over every row in our csv using an await foreach (var records in records) loop.

Inside of this foreach loop we can use the values from the current row in the csv as the values of the parameters in the SQL command. We can also do validation on the values parsed by CsvHelper and set out own values like a table specific Id for the row and a timestamp to record when it was ingested. Then to submit this row to SQLite we’ll call await command.ExecuteNonQueryAsync(); because this is not a query and it will not return results.

No more time or memory wasted

Once we’ve submitted all of the rows in the csv to SQLite we can close out the transaction by calling await transaction.CommitAsync(); You can see an example of this whole process in the Gist below. Prior to using these techniques, it took multiple hours and tens of gigabytes of memory to ingest all the data my app needs to run. Now it takes half an hour and less than a gigabyte of memory to accomplish the same work.

LevyCode.cs
using CsvHelper;
using CsvHelper.Configuration;
using CsvHelper.Configuration.Attributes;

using Flurl.Http;

using Microsoft.EntityFrameworkCore;

using System;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Threading.Tasks;

namespace eRealProperty.Models
{
    public class TaxLevy
    {
        [Key]
        [Ignore]
        public Guid Id { get; set; }
        public string DistrictAbbrev { get; set; }
        public string LevyCode { get; set; }
        public string DistrictName { get; set; }
        [Ignore]
        public DateTime IngestedOn { get; set; }

        public static async Task<bool> IngestAsync(eRealPropertyContext context, string zipUrl, string fileName)
        {
            if (string.IsNullOrWhiteSpace(zipUrl) || string.IsNullOrWhiteSpace(fileName))
            {
                return false;
            }

            var pathtoFile = await zipUrl.DownloadFileAsync(AppContext.BaseDirectory);
            var pathToCSV = Path.Combine(AppContext.BaseDirectory, fileName);

            var fileTypes = new string[] { ".txt", ".csv" };
            // If a file with the same name already exists it will break the downloading process, so we need to make sure they are deleted.
            foreach (var type in fileTypes)
            {
                var filePath = Path.Combine(AppContext.BaseDirectory, Path.GetFileNameWithoutExtension(pathtoFile) + type);
                if (File.Exists(filePath))
                {
                    File.Delete(filePath);
                }
            }

            if (!File.Exists(pathToCSV))
            {
                ZipFile.ExtractToDirectory(pathtoFile, AppContext.BaseDirectory);
            }

            var config = new CsvConfiguration(CultureInfo.InvariantCulture)
            {
                NewLine = Environment.NewLine,
                MissingFieldFound = null,
                CacheFields = true
            };

            using var transaction = await context.Database.BeginTransactionAsync();
            using var reader = new StreamReader(pathToCSV);
            using var csv = new CsvReader(reader, config);

            var command = context.Database.GetDbConnection().CreateCommand();
            command.CommandText =
                $"insert into LevyCodes (Id, DistrictAbbrev, LevyCode, DistrictName, IngestedOn) " +
                $"values ($Id, $DistrictAbbrev, $LevyCode, $DistrictName, $IngestedOn);";

            var Id = command.CreateParameter();
            Id.ParameterName = "$Id";
            command.Parameters.Add(Id);

            var DistrictAbbrev = command.CreateParameter();
            DistrictAbbrev.ParameterName = "$DistrictAbbrev";
            command.Parameters.Add(DistrictAbbrev);

            var LevyCode = command.CreateParameter();
            LevyCode.ParameterName = "$LevyCode";
            command.Parameters.Add(LevyCode);

            var DistrictName = command.CreateParameter();
            DistrictName.ParameterName = "$DistrictName";
            command.Parameters.Add(DistrictName);

            var IngestedOn = command.CreateParameter();
            IngestedOn.ParameterName = "$IngestedOn";
            command.Parameters.Add(IngestedOn);

            var records = csv.GetRecordsAsync<TaxLevy>();

            await foreach (var record in records)
            {
                record.Id = Guid.NewGuid();
                record.IngestedOn = DateTime.Now;

                Id.Value = record.Id;
                DistrictAbbrev.Value = record.DistrictAbbrev;
                LevyCode.Value = record.LevyCode;
                DistrictName.Value = record.DistrictName;
                IngestedOn.Value = record.IngestedOn;

                await command.ExecuteNonQueryAsync();
            }

            await transaction.CommitAsync();
            return true;
        }
    }
}

This site uses Microsoft Clarity for analytics. Learn about Clarity's data collection.
This site is licensed under the GNU Affero General Public License v3.0.
View this site's source on GitHub.