Entity Framework Core with SQLite: How to include records from multiple related tables without hyperinflating temporary files?

Question

My application works with the historical stock data. When new data needs to be added for a stock or some records need to be updated, I load stock-related data using the following function:

public Stock GetStockWithData(string symbol)
{
    return
        Stocks.
        Include(stock => stock.TradingHistory).
        Include(stock => stock.Dividends).
        Include(stock => stock.Splits).
        Single(stock => stock.Symbol == symbol);
}

It works well for most of the stocks, but for some, it takes forever and a temporary file created during this operation grows to a very large size comparing to the amount of data the operation has to return or even the size of the whole database. For example, the operation is now executing for 17 minutes and the size of the temporary file (called etilqs_c3j0v0Nx4sifGZr) is 175 GB and growing, whereas the current size of the database is 2.32 GB. (After executing the query for 191.59 minutes there was an exception with the message

SQLite Error 13: 'database or disk is full'.

which means the temporary file occupied all 1.63 TB left on my drive.)

An interesting detail is that if I leave any one of three include statements in the query, it executes almost instantaneously. For example, the following code retrieving only the TradingHistory (which by far is the biggest portion of the stock data) executes in 0.3550237 seconds (i.e. less than a half of a second):

public Stock GetStockWithData(string symbol)
{
    return
        Stocks.
        Include(stock => stock.TradingHistory).
        // Include(stock => stock.Dividends).
        // Include(stock => stock.Splits).
        Single(stock => stock.Symbol == symbol);
}

So, how to retrieve the data from multiple related tables without causing temporary files to hyperinflate?

My app is using the following packages:

• Microsoft.EntityFrameworkCore (5.0.2)
• Microsoft.EntityFrameworkCore.Sqlite (5.0.2)
• Microsoft.EntityFrameworkCore.Tools (5.0.2)

Here is how the data model is defined:

using Price = Double;
using Volume = Double;

public class Context : DbContext
{
    public DbSet<Stock> Stocks { get; set; }
}

public class Stock
{
    public int StockId { get; set; }
    [StringLength(5)]
    public string Symbol { get; set; }

    public ICollection<StockTradingDay> TradingHistory { get; set; } = new StockTradingHistory();
    public ICollection<StockDividend> Dividends { get; set; } = new StockDividends();
    public ICollection<StockSplit> Splits { get; set; } = new StockSplits();
}

public class StockTradingDay
{
    public int StockTradingDayId { get; set; }
    public int StockId { get; set; }
    public DateTime Date { get; set; }
    public StockDayPriceFluctuation Price { get; set; }
    public Volume Volume { get; set; }
}

[Owned]
public class StockDayPriceFluctuation
{
    public Price Open { get; set; }
    public Price High { get; set; }
    public Price Low { get; set; }
    public Price Close { get; set; }
}

public class StockDividend : StockEvent
{
    public int StockDividendId { get; set; }
    public int StockId { get; set; }
}

public class StockSplit : StockEvent
{
    public int StockSplitId { get; set; }
    public int StockId { get; set; }
}

public class StockEvent
{
    public DateTime? Date { get; set; }
    public double? Value { get; set; }
}

Answer 1

IMHO, maybe you should consider using MS SQL Server Express, Oracle Express or MySQL, which are all free to use and work with EF Core. Also Access works with EF Core too.