George Kosmidis

Microsoft MVP | Cloud Solutions Architect | .NET Software Engineer | Organizer of Munich .NET Meetup | Speaker

Mocking expected behaviour of SQL functions for Unit Testing with SQLite

by George Kosmidis / Published 5 years ago, modified 3 years and 2 months ago

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:")
var context = new DemoProjectDbContext(options);

//open one and keep it, in-memory databases live untill the last connection closes

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

//Unit test code here...

This page is open source. Noticed a typo? Or something unclear?
Edit Page Create Issue Discuss
Microsoft MVP - George Kosmidis

My next events!

Online event
Online event
Mindspace Salvatorplatz (Salvatorplatz 3, München, BY, Germany 80333)