Testing ASP Net Core Application with SQLite throws unknown function: newid()

Question

I am testing my service layer which contains some calls to repositories by using the SQLite database provider. My test class has been written according to the following introduction: https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/sqlite

For the entity which is written to the database in this test case, the Guid is generated on database:

entity.Property(e => e.Guid).HasDefaultValueSql("newid()");

Now when trying to add a new entity (without explicitely setting the Guid), I get following exception:

SQLite Error 1: 'unknown function: newid()'.

Is there a way around this issue? It seems like this function is simply not supported. As the database I am working with is quite old I am afraid to find more places like this which may not work.

My hope was to get better unit tests with SQLite than using the InMemoryDatabase provider which does not really suits for testing "relational database functionalities". But if this problem cannot be solved, I am stuck and probably need to stick to integration tests (at least for the data access part of my services)

Answer 1

You can create a custom function to do this in c# if you reference Microsoft.Data.SqLite

For example:

var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = ":memory:" };
var connection = new SqliteConnection(connectionStringBuilder.ToString());

connection.CreateFunction("newid", () => Guid.NewGuid());

See here for a comparison between System.Data.SQLite and Microsoft.Data.Sqlite

Answer 2

I guess you would have this issue for MySql uuid() or any other DB because there is a direct dependency to MSSQL with newid(). My first suggestion would be to generate the GUID on the application side and pass it to the DB but you have probably already thought of that and cannot. As a workaround you can create a extension method like the following:

public static class Extensions
{
    private static readonly Dictionary<Type, string> NewIdDictionary = new Dictionary<Type, string>
    {
        { typeof(SqlServerOptionsExtension), "newid()" }
    };

    public static PropertyBuilder<TProperty> HasDefaultValueForSql<TProperty>(this PropertyBuilder<TProperty> propertyBuilder, 
        DbContextOptions contextOptions)
    {
        var result = contextOptions.Extensions.Select(extension =>
        {
            if (!(extension is RelationalOptionsExtension item)) return string.Empty;
            return NewIdDictionary.TryGetValue(item.GetType(), out var sql) ? sql : string.Empty;
        }).SingleOrDefault(s => !string.IsNullOrEmpty(s));

        return propertyBuilder.HasDefaultValueSql(result);
    }
}

Although there isn't an equvalient for SQLite it seems to work when passing null to .HasDefaultValueSql. And then you can add other extension types if needed.