Efcore: I am trying to form a table of the limitations/differences in SQLite when using it for unit testing

[JonPSmith]

Using SQLite for unit testing when the production db type isn't SQLite

@bricelam, I'm writing a chapter on unit testing of EF Core applications and I advocate using SQLite in-memory. But of course there are some differences between SQLite and say a SQL Server or Postgresql database and I want to warn developer when to not use SQLite in there unit tests. I have characterised them as:

• Wrong answer, i.e. it might work and give you the wrong answer (which in unit testing is the worse result)
• Might break, i.e. it might work correctly when EnsuredCreated is called or in a query, but there are cases which will throw an exception
• Will break, i.e. it almost certain to fail when EnsuredCreated is called (might work if the SQL is very basic)

Here is my current table.

| SQL feature | SQLite support? | Breaks? |
| --- | --- | --- |
| string compare and collations | Works, but different results | Wrong answer! |
| Different schemas | Not supported – ignores config | Wrong answer! |
| Handling decimal types | Works, but EF queries fail (Note1) | Might BREAK |
| SQL column default value | Works, but only with C# constant | Might BREAK |
| SQL computed columns | SQL is different – likely to fail | WILL BREAK |
| SQL user-defined functions (UDFs) | Adding UDF code is different | WILL BREAK |
| SQL sequences | Not supported exception | WILL BREAK |

Note1: can get around EF Core exception on sort/filter via value converter to double, but might return a wrong answer.

I'm sure I have missed some. Could you let me know. Thanks.

EF Core version: 5
Database provider: Microsoft.EntityFrameworkCore.Sqlite)
Target framework: NET 3.1
Operating system: Windows
IDE: Visual Studio 2019 16.8.3

[ErikEJ]

So there is an almost infinte realm of possible breaks here - I vote for: when doing this kind of testing, use the relevant DBMS enigine.

[JonPSmith]

Hi @ErikEJ,

Many developers are happy to only use LINQ and EF Core methods and never use raw SQL. Even some of my clients that know SQL well are happy to let EF Core do everything. In those cases SQLite in-memory is a good fit (mostly - watch out for decimal).

I wouldn't call you a 'normal user' Erik - you are very happy working with SQL directly and are quite capable handling all it throws at you.

[ajcvickers]

@JonPSmith We have a docs page where we outline some of the limitations: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations

However, this is not intended to be exhaustive, and given the potential space of issues, some of which might be very obscure, I don't think it is realistic to create an exhaustive list.

[JonPSmith]

OK, thanks for the link.