David Shifflet's Snippets

< Back to Index

.NET Core, Oracle, Linq2Db, Sqlite and In Memory Database Tests

March 17, 2018 by David Shifflet -

Introduction

Example Code Here

So in the last example we created a .NET Core project that connected to an Oracle database and displayed a list of products from Northwind. For this example we are going to write some tests to make sure our Linq2Db is working

Our goal here is to:

• Use an In Memory Database that mirrors our Oracle database so it's fast. We are going to use Sqlite. The queries are agnostic in regards to the database engine, so in theory the ones for Oracle should work with Sqlite and vice versa.
• Use xUnit
• Test that the linq queries from Linq2Db actually work

Let's get started!

Getting Ready

We are going to use Sqlite as our database engine. The database will be stored in memory. That means we are going to need a build or DDL script to create the database objects for us.

We could create this from scratch, but using SqlWrangler is a lot faster.

First connect to your database using SQL Wrangler. Execute the following sql:

select * from northwind.products

Some rows will return. Now click the button button.

A window will appear.



Type a schema name and a table name, and check both boxes. We want to create tables and insert data into them. Finally click OK and save the file somewhere. Open that file, it will look like:

/* CREATE TABLE northwind_products */
create table northwind_products (
PRODUCT_ID INT NOT NULL,
PRODUCT_NAME VARCHAR(40) NOT NULL,
SUPPLIER_ID INT NOT NULL,
CATEGORY_ID INT NOT NULL,
QUANTITY_PER_UNIT VARCHAR(20),
UNIT_PRICE FLOAT NOT NULL,
UNITS_IN_STOCK INT NOT NULL,
UNITS_ON_ORDER INT NOT NULL,
REORDER_LEVEL INT NOT NULL,
DISCONTINUED VARCHAR(1) NOT NULL
);

/* INSERT TABLE northwind_products */
insert into northwind_products (PRODUCT_ID, PRODUCT_NAME, SUPPLIER_ID, CATEGORY_ID, QUANTITY_PER_UNIT, UNIT_PRICE, UNITS_IN_STOCK, UNITS_ON_ORDER, REORDER_LEVEL, DISCONTINUED) values (1, 'Chai', 1, 1, '10 boxes x 20 bags', 18, 39, 0, 10, 'N');
insert into northwind_products (PRODUCT_ID, PRODUCT_NAME, SUPPLIER_ID, CATEGORY_ID, QUANTITY_PER_UNIT, UNIT_PRICE, UNITS_IN_STOCK, UNITS_ON_ORDER, REORDER_LEVEL, DISCONTINUED) values (2, 'Chang', 1, 1, '24 - 12 oz bottles', 19, 17, 40, 25, 'N');
insert into northwind_products (PRODUCT_ID, PRODUCT_NAME, SUPPLIER_ID, CATEGORY_ID, QUANTITY_PER_UNIT, UNIT_PRICE, UNITS_IN_STOCK, UNITS_ON_ORDER, REORDER_LEVEL, DISCONTINUED) values (76, 'Lakkalik??ri', 23, 1, '500 ml', 18, 57, 0, 20, 'N');
insert into northwind_products (PRODUCT_ID, PRODUCT_NAME, SUPPLIER_ID, CATEGORY_ID, QUANTITY_PER_UNIT, UNIT_PRICE, UNITS_IN_STOCK, UNITS_ON_ORDER, REORDER_LEVEL, DISCONTINUED) values (77, 'Original Frankfurter gr?ne So?e', 12, 2, '12 boxes', 13, 32, 0, 15, 'N');

Keep in mind SQLite doesn't have the concept of schemas. So to mirror that functionality we are using [SCHEMANAME]_[TABLENAME]. So NORTHWIND.PRODUCTS becomes NORTHWIND_PRODUCTS. This will cause some problems later on, but nothing major.

To the Code

Armed with our build script it's time to write some code!

Open up the previous project and add a new Tests project

I renamed the default UnitTests1.cs to BasicTests.cs. We are going to need to install a package to talk to Sqlite, so using the PackageManagerConsole run:

install-package Microsoft.Data.Sqlite

Be sure to add it to the Tests project (The drop down in the PackageManagerConsole)! The other projects won't need it.

The code we are going to add is

• Our Database Build Script (SQL)
• A Fixture to share our database across our tests using xUnit
• The Tests
• A DbSettings for Sqlite and Linq2Db
• A Setup that populates our database with tables and data

Our Database Build Script (SQL)

Now let's add our build script by creating a new text document call it "build_northwind.sql" and copy and paste the output from the SqlWrangler output into it. Be sure to set the "Copy To Output Directory" to "Copy Always".

A Fixture to share our database across our tests using xUnit

Our tests are going to need to share the database so we will need a fixture for the xUnit tests. So let's create one.

using System;

namespace Northwind.Tests
{
    public class NorthwindDbFixture : IDisposable
    {
        public NorthwindSetup Setup { get; }

        public NorthwindDbFixture()
        {
            //DataSource=:memory: makes Sqlite use in memory
            DbNorthwindStartup.Init(new SqliteDbSettings("Data Source=:memory:;"));
            //Let's run this which will create our database and change our mapping names
            Setup = new NorthwindSetup("build_northwind.sql", "Northwind.Models");
        }

        public void Dispose()
        {
        }
    }
}

This may seem strange. I explain why we need this and how it works later on.

The Tests

Now in our BasicTests.cs let's write some tests!

using System.Linq;
using Xunit;
using Xunit.Abstractions;

namespace Northwind.Tests
{
    public class BasicTests : IClassFixture
    {
        private readonly NorthwindSetup _setup;

        //this is to display some output about the counts
        private readonly ITestOutputHelper _output;

        public BasicTests(ITestOutputHelper output, NorthwindDbFixture fixture)
        {
            _setup = fixture.Setup;
            _output = output;
        }

        [Fact]
        public void CanGetProductsViaLinq()
        {
            using (var db = _setup.GetDbNorthwind())
            {
                var query = db.Product.Where(o => o.Id > 25).OrderByDescending(o => o.ProductName)
                    .ToList();
                _output.WriteLine("Products: {0}", query.Count);
                Assert.True(query.Any());
            }
        }

        [Fact]
        public void CanGetCheapProductsViaLinq()
        {
            using (var db = _setup.GetDbNorthwind())
            {
                var query = db.Product.Where(o => o.UnitPrice < 20).OrderByDescending(o => o.ProductName)
                    .ToList();
                Assert.True(query.Any());
            }
        }
    }
}

Why the Fixture? Why ITestOutputHelper?

Using the fixture will maintain the value of the _setup variable across the tests. Basically every time a test runs in xUnit it will instantiate a new class (call the constructor) then call the individual test. By implementing IClassFixture and having a parameter in the constructor, public BasicTests(NorthwindDbFixture fixture), xUnit is smart enough to call the constructor with that fixture. The constructor then passes it to a local variable so the tests can access it.

The ITestOutputHelper let's us display some output to test results by doing _output.WriteLine("Products: {0}", query.Count);

A DbSettings for Sqlite and Linq2Db

Now let's create a DbSettings for our Sqlite database so Linq2Db can talk to it.

using System.Collections.Generic;
using LinqToDB.Configuration;

namespace Northwind.Tests
{
    public class ConnectionStringSettings : IConnectionStringSettings
    {
        public string ConnectionString { get; set; }
        public string Name { get; set; }
        public string ProviderName { get; set; }
        public bool IsGlobal => false;
    }

    public class SqliteDbSettings : ILinqToDBSettings
    {
        public IEnumerable DataProviders
        {
            get { yield break; }
        }

        public string DefaultConfiguration => "Microsoft.Data.Sqlite";
        public string DefaultDataProvider => "Microsoft.Data.Sqlite";

        public string ConnectionString { get; set; }

        public SqliteDbSettings(string connectionString)
        {
            ConnectionString = connectionString;
        }

        public IEnumerable ConnectionStrings
        {
            get
            {
                yield return
                    new ConnectionStringSettings
                    {
                        Name = "Northwind",
                        ProviderName = "Microsoft.Data.Sqlite",
                        ConnectionString = ConnectionString
                    };
            }
        }
    }
}

This looks a lot like our DbSettings in the Northwind project for Oracle. The only real change is the provider is now Microsoft.Data.Sqlite.

Our code is using this DbSettings code in the NorthwindDbFixture.

DbNorthwindStartup.Init(new SqliteDbSettings("Data Source=:memory:;"));

A Setup that populates our database with tables and data

Next we are going to need something that builds up our database with tables and data so let's create a Setup for that!

using System;
using System.IO;
using System.Linq;
using LinqToDB.Data;
using LinqToDB.Mapping;

namespace Northwind.Tests
{
    public class NorthwindSetup
    {
        private readonly string[] _nameSpaces;
        private bool _namesChanged;
        private readonly string[] _buildCommands;
        
        public NorthwindSetup(string buildFile, params string[] nameSpaces)
        {
            if (buildFile == null) throw new ArgumentNullException(nameof(buildFile));
            if (!File.Exists(buildFile)) throw new FileNotFoundException(
                string.Format("Unable to find '{0}'", buildFile));
            
            _nameSpaces = nameSpaces ?? throw new ArgumentNullException(nameof(nameSpaces));

            using (var sr = new StreamReader(buildFile))
            {
                var s = sr.ReadToEnd();
                _buildCommands = s.Split(';');
            }
        }
        
        public DbNorthwind GetDbNorthwind()
        {
            var db = new DbNorthwind();
            
            //Have to change our table names because Linq2Db for Sqlite doesn't use the schema name
            ChangeTableNames(db, _nameSpaces);

            //build up the database from our script
            foreach (var cmd in _buildCommands)
            {
                db.Execute(cmd);
            }
            return db;
        }

        private void ChangeTableNames(DataConnection db, string[] nameSpaces)
        {
            //What this does?
            //Given a namespace look for classes we have registered with Linq2Db
            //and change the table name to schema_tablename.
            
            //Only do it once
            if (_namesChanged) return;
            _namesChanged = true;

            //get the classes in the namespaces
            foreach (var item in AppDomain.CurrentDomain.GetAssemblies()
                .SelectMany(t => t.GetTypes())
                .Where(t => t.IsClass && nameSpaces.Contains(t.Namespace)))
            {
                var descriptor = db.MappingSchema.GetEntityDescriptor(item);
                if (descriptor.SchemaName != null)
                {
                    //The reflection is here to deal with the generic methods.
                    var builder = db.MappingSchema.GetFluentMappingBuilder();
                    var method = typeof(FluentMappingBuilder).GetMethod("Entity");
                    var genericMethod = method.MakeGenericMethod(item);
                    var entityBuilder = genericMethod.Invoke(builder, new object[] { null });
                    var tblMethod = entityBuilder.GetType().GetMethod("HasTableName");                   
                    tblMethod.Invoke(entityBuilder, new object[] {
                        string.Format("{0}_{1}", descriptor.SchemaName, descriptor.TableName)});
                }
            }
        }
    }
}

See that ChangeTableNames() method at the end. We need that because Linq2Db basically ignores the schema name when using Sqlite.

Linq2Db with SqlLite when executing db.Product.Where(o => o.UnitPrice < 20) is going to execute select * from products where.... Remember our table is called NORTHWIND_PRODUCTS to accomodate the idea of schemas.

So we are changing the table names in the mappings to follow our format of [SCHEMANAME]_[TABLENAME]. The lines of reflection are needed because of the generic functions. This way we execute select * from northwind_products where... when we execute our queries.

We want it to work for all the db mapped classes so we don't have to specify them. So any class in a namespace we specified that is mapped with Linq2Db, it will change the table names for us automatically. It discovers them and changes them for us.

If you wanted to you could specify them by hand by doing something like:

db.MappingSchema.GetFluentMappingBuilder().Entity().HasTableName("NORTHWIND_PRODUCTS");

Also it is important not to call the ChangeNames more than once. It's kind of expensive that is why this is there.

            //Only do it once
            if (_namesChanged) return;
            _namesChanged = true;

Although we are changing the names once. The database is being recreated for every test! So keep this in mind. The database goes away when the connection is closed or disposed. See the end of that using statement... It goes away at that point.

		using (var db = _setup.GetDbNorthwind())
		{
			var query = db.Product.Where(o => o.UnitPrice < 20).OrderByDescending(o => o.ProductName)
				.ToList();
			Assert.True(query.Any());			
		}
		//No more database because connection is disposed!

Finally this setup is called in the NorthwindDbFixture.

Setup = new NorthwindSetup("build_northwind.sql", "Northwind.Models");

Summary

And that is basically it. Run the tests and they should pass and run fast! The console application should also work.

• We didn't change the Northwind project to test it at all
• Using Linq2Db means our queries are database agnostic. The same ones will work with Oracle or Sqlite.
• Keep in mind, the Sqlite DB is in memory, so when the connection is disposed it is disposed!

Example Code Here