LocalDb

Provides a wrapper around SqlLocalDB to simplify running tests against Entity Framework or a raw SQL Database.

SqlLocalDB is only supported on Windows

Contents

• Why
  • Goals:
  • Why not SQLite
  • Why not SQL Express or full SQL Server
  • Why not EntityFramework InMemory
• References:
• Usage
  • Raw SqlConnection
  • EntityFramework Classic
  • EntityFramework Core
• Debugging
• SqlLocalDb
• ReSharper Test Runner
• Credits
• Design
• Raw Connection Usage
• EntityFramework Classic Usage
• EntityFramework Core Usage
• EntityFramework Core Migrations
• Directory and name resolution
• Sql Management Studio
• Logging
• Template database size
• Template Re-generation

NuGet packages

• https://www.nuget.org/packages/LocalDb/
• https://www.nuget.org/packages/EfLocalDb/
• https://www.nuget.org/packages/EfClassicLocalDb/

Why

Goals:

• Have a isolated SQL Server Database for each unit test method.
• Does not overly impact performance.
• Results in a running SQL Server Database that can be accessed via SQL Server Management Studio (or other tooling) to diagnose issues when a test fails.

Why not SQLite

• SQLite and SQL Server do not have compatible feature sets and there are incompatibilities between their query languages.

Why not SQL Express or full SQL Server

• Control over file location. SqlLocalDB connections support AttachDbFileName property, which allows developers to specify a database file location. SqlLocalDB will attach the specified database file and the connection will be made to it. This allows database files to be stored in a temporary location, and cleaned up, as required by tests.
• No installed service is required. Processes are started and stopped automatically when needed.
• Automatic cleanup. A few minutes after the last connection to this process is closed the process shuts down.
• Full control of instances using the Command-Line Management Tool: SqlLocalDB.exe.

Why not EntityFramework InMemory

• Difficult to debug the state. When debugging a test, or looking at the resultant state, it is helpful to be able to interrogate the Database using tooling
• InMemory is implemented with shared mutable state between instance. This results in strange behaviors when running tests in parallel, for example when creating keys.
• InMemory is not intended to be an alternative to SqlServer, and as such it does not support the full suite of SqlServer features. For example:
  • Does not support Timestamp/row version.
  • Does not validate constraints.

See the official guidance: InMemory is not a relational database.

References:

• Which Edition of SQL Server is Best for Development Work?
• Introducing SqlLocalDB, an improved SQL Express
• SQL LocalDB 2019 Download

Usage

This project supports several approaches.

Raw SqlConnection

Interactions with SqlLocalDB via a SqlConnection.

Full Usage

EntityFramework Classic

Interactions with SqlLocalDB via Entity Framework Classic.

Full Usage

EntityFramework Core

Interactions with SqlLocalDB via Entity Framework Core.

Full Usage

Debugging

To connect to a SqlLocalDB instance using SQL Server Management Studio use a server name with the following convention (LocalDb)\INSTANCENAME.

So for a instance named MyDb the server name would be (LocalDb)\MyDb. Note that the name will be different if a name or instanceSuffix have been defined for SqlInstance.

The server name will be written to Trace.WriteLine when a SqlInstance is constructed. It can be accessed programmatically from SqlInstance.ServerName. See Logging.

SqlLocalDb

The SqlLocalDb Utility (SqlLocalDB.exe) is a command line tool to enable users and developers to create and manage an instance of SqlLocalDB.

Useful commands:

• sqllocaldb info: list all instances
• sqllocaldb create InstanceName: create a new instance
• sqllocaldb start InstanceName: start an instance
• sqllocaldb stop InstanceName: stop an instance
• sqllocaldb delete InstanceName: delete an instance (this does not delete the file system data for the instance)

ReSharper Test Runner

The ReSharper Test Runner has a feature that detects spawned processes, and prompts if they do not shut down when a test ends. This is problematic when using SqlLocalDB since the Sql Server process continues to run:

To avoid this error spawned processes can be ignored:

Credits

SqlLocalDB API code sourced from https://github.com/skyguy94/Simple.LocalDb

Icon

Robot designed by Creaticca Creative Agency from The Noun Project.