Help with SQLite issues

[flarocca]

Hi Kevin!!! I am pleased for reaching out you! First of all, your job is amazing, I've become a very big fan of you!! Indeed I've watched almost all your Pluralsight courses. I am here to ask for help, you might have come across similar situations.

I've been recently watching one of your Pluralsight courses about Testing with EF Core. One of its modules talks about using SQLite for testing, which is amazing!!! The course is so cool that I am planning on moving from regular Integration Tests using SQL Server Express to using SQLite. However, I've got with some issues due to some particularities our current implementation has.

The first issue is ComputedColumns, let me show you what the configuration looks like:

public class BookingTransactionConfiguration : IEntityTypeConfiguration<BookingTransaction>
{
    public void Configure(EntityTypeBuilder<BookingTransaction> builder)
    {
        builder.Property(bt => bt.Id).ValueGeneratedNever();
        builder.Property(bt => bt.EffectiveStatusId)
            .HasComputedColumnSql("CASE WHEN [StatusId] = 5 AND DATEADD(MINUTE, [DurationInMinutes], [StartDateTime]) < GETUTCDATE() THEN 7 ELSE [StatusId] END");
    }
}

public class BookingTransaction
{
    public Guid Id { get; set; }
    public int EffectiveStatusId { get; set; }
}

The issue is that when I try to save a new BookingTransaction, I always get a NOT NULL exception on EffectiveStatusId. This is the test code:

var connectionStringBuilder =
    new SqliteConnectionStringBuilder { DataSource = ":memory:" };
var connection = new SqliteConnection(connectionStringBuilder.ToString());
var options = new DbContextOptionsBuilder<BookingDbContext>()
    .UseSqlite(connection)
    .Options;
var dbContext = new BookingDbContext(options);

var booking = new BookingTransaction
{
    Id = _bookingId1,
    EffectiveStatusId = 1
};

dbContext.BookingTransaction.Add(booking);
dbContext.SaveChanges();

And this is the error:

SQLite Error 19: 'NOT NULL constraint failed: BookingTransaction.EffectiveStatusId'

I don't really know how to solve this.

The second issue I am actually facing is with Spatial data and NetTopologySuite. Some of the entities use Lat and Lon fields (IPoint). I am actually configuring the DBContext to use NetTopologySuit which works fine for SQL Server, but it is not actually working with SQLite. This is the context configuration:

var connectionStringBuilder =
    new SqliteConnectionStringBuilder { DataSource = ":memory:" };
var connection = new SqliteConnection(connectionStringBuilder.ToString());
var options = new DbContextOptionsBuilder<BookingDbContext>()
    .UseSqlite(connection, sqliteOptions =>
    {
        sqliteOptions.UseNetTopologySuite();
    })
    .Options;
var dbContext = new BookingDbContext(options);

To avoid repetition, this is exactly issue in StackOverflow

These are the packages I am actually using:

• Net Core 2.2
• Microsoft.EntityFrameworkCore 2.2.6
• Microsoft.EntityFrameworkCore.Sqlite 2.2.6
• Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite 2.2.6

Thanks a lot not only for wasting your time reading all this stuff but also for all the content you create for us!!! It is invaluable!!! I really enjoy watching your courses! Sorry for any inconveniences I might have generated!

I am almost ready to be awesome!!!

Have a good day!