While unit testing, I came across a method in an SQL repository class that was using a raw SQL query, and along with that, SQL Server’s newid() function. Since the complicated reason behind this decision was out of scope, I had to find a way to unit test that method…

SQLite was -of course- the first approach, but any attempt to actually test the method would nevertheless fail with the exception Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'no such function: newid'.'.

To go around this, I used SqliteConnection.CreateFunction to bind a custom function to the connection and unit test SQL Queries that include that function (e.g. newid() )

You can see an example of this approach, in the following gist

var options = new DbContextOptionsBuilder()
                  .UseSqlite("Data Source=:memory:")
                  .Options;
var context = new DemoProjectDbContext(options);

//open one and keep it, in-memory databases live untill the last connection closes
context.Database.OpenConnection();

var conn = context.Database.GetDbConnection() as SqliteConnection;
conn.CreateFunction("newid", () => { return Guid.NewGuid(); });
context.Database.EnsureCreated();

//Unit test code here...