Chapter 6. Simplify data access with object-relational mappers

This chapter covers

• Implementing a data-access layer with an object-relational mapper (ORM)
• Comparing a micro-ORM (Dapper) with a full-featured ORM (Entity Framework Core)
• Using Microsoft.Extensions libraries to build a data-access layer

Your implementation of supply chain management software was a success, and now Widget Corporation would like to expand its capabilities. They want touchscreens for their assembly-line workers; daily, weekly, and monthly reports; and a bunch of other new features. The head of your consulting company decides that this should be a product that you can sell to other customers, so she starts hiring new team members to help you build it.

The low-level data-access layer you built worked great when there was only one client and one database. Now you’ve got to think about appealing to customers with all kinds of databases. Your team members also find that the code for interacting with the relational database is tedious and subject to human error. They suggest using an ORM, but there are so many choices, and you’re not sure which one to use.

This chapter intends to arm you with experience of .NET Core ORMs so you’re better able to make decisions about what works best in your application. We’ll look at two different kinds of ORMs: full-featured ORMs and micro-ORMs. Both have advantages and disadvantages, but both automate some of the boilerplate code needed to convert between objects and relational databases. The full-featured ORM we’ll look at is called Entity Framework Core, which is a part of .NET Core. But first, let’s explore a library called Dapper, which has been ported to .NET Standard, to see what a micro-ORM is capable of in .NET Core.

6.1. Dapper

Stack Overflow and the rest of Stack Exchange are powered by a custom ORM library called Dapper. Dapper is considered a micro-ORM because it doesn’t write SQL queries for you like many full-featured ORMs. It only tries to simplify the process of converting between objects and relational data.

In the previous chapter, you created a data-access layer for your supply chain management application. You’ll continue with that example and convert your existing code to use Dapper. I suggest copying the code to a new folder called DapperTest.

Go to the DapperTestWidgetScmDataAccess folder and edit the project file so it looks like the following.

Listing 6.1. Modify WidgetScmDataAccess.csproj to add Dapper dependency

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netstandard1.3</TargetFramework>         1
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.Common"
                      Version="4.3.0" />
    <PackageReference Include="Dapper"                      2
                      Version="1.50.2" />
  </ItemGroup>

</Project>

• 1 Dapper uses .NET Standard 1.3.
• 2 Dapper started supporting .NET Standard as of version 1.50.

In chapter 5 you used a DbCommand to execute SQL commands and a DbDataReader to read the rows of output from that command into objects. Recall the ReadParts method from that chapter, and compare it to how the same can be achieved with Dapper. Both are shown in the following listing.

Listing 6.2. ReadParts from chapter 5 compared with the Dapper version

private void ReadParts()                                   1
{
  var command = connection.CreateCommand();
  command.CommandText = @"SELECT Id, Name                  2
    FROM PartType";
  var reader = command.ExecuteReader();
  var parts = new List<PartType>();
  Parts = parts;
  while (reader.Read())                                    3
  {
    parts.Add(new PartType() {                             4
      Id = reader.GetInt32(0),                             5
      Name = reader.GetString(1)
    });
  }
}

private void ReadPartsDapper()                             6
{
  Parts = connection.Query<PartType>(                      7
    "SELECT * FROM PartType");                             8
}

• 1 Old method from chapter 5
• 2 Specifies columns
• 3 Reads one row at a time
• 4 Creates a new PartType object per row
• 5 Manually converts the database types to C# types
• 6 Dapper version of ReadParts
• 7 Query is an extension method from the Dapper library.
• 8 No need to order columns

A single line of code in Dapper replaces the whole ReadParts method. Let’s unpack listing 6.2 a bit to understand how.

The connection field from ScmContext is a standard DbConnection object. Dapper defines a set of extension methods that apply to DbConnection, one of which is Query. Query is a generic method, meaning you use C# generics to specify that the rows returned from the SQL query will fit into PartType objects. Dapper uses reflection to determine the properties of the PartType class as well as their names and types, and maps them to the columns returned by the SQL query. Dapper only tries to match columns and properties by name, so anything that doesn’t match is skipped. That’s why you don’t have to specify columns in the SQL query. Query returns an IEnumerable of the class you specified in the generic type parameter (the type specified in angle brackets).

You can use Query to get the inventory and suppliers. Let’s look at how that changes the constructor of ScmContext in the next listing.

Listing 6.3. ScmContext class rewritten to use Dapper

using System;                                                              1
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;                                                         2
using Dapper;                                                              3

namespace WidgetScmDataAccess
{
  public class ScmContext
  {
    private DbConnection connection;
    public IEnumerable<PartType> Parts { get; private set; }
    public IEnumerable<InventoryItem> Inventory { get; private set; }
    public IEnumerable<Supplier> Suppliers { get; private set; }

    public ScmContext(DbConnection conn)
    {
      connection = conn;
      Parts = conn.Query<PartType>("SELECT * FROM PartType");
      Inventory = conn.Query<InventoryItem>("SELECT * FROM InventoryItem");
      foreach (var item in Inventory)                                      4
        item.Part = Parts.Single(p => p.Id == item.PartTypeId);
      Suppliers = conn.Query<Supplier>("SELECT * FROM Supplier");
      foreach (var supplier in Suppliers)
        supplier.Part = Parts.Single(p => p.Id == supplier.PartTypeId);
    }
  }
}

• 1 Some of these usings are only needed later.
• 2 Used for Single extension method
• 3 Adds Dapper library
• 4 Dapper won’t auto-populate class properties like Part.

Both the Supplier and InventoryItem classes reference PartType objects. The Dapper Query method will populate the value of PartTypeId for Supplier and InventoryItem, but it has no way of magically locating the PartType objects you read earlier. That’s why you need to set the Part property explicitly on these objects. Although Dapper can’t do everything, it saves a lot of the boilerplate of executing the command, reading the rows, and mapping columns to properties.

6.1.1. Inserting rows with Dapper

Dapper can also map an object’s properties to the parameters in an INSERT SQL statement. Try creating an order with both System.Data and Dapper. You’ll simplify it to just adding the row to the Order table and leave out the SendEmailCommand and transactions for this example. The following listing shows both approaches.

Listing 6.4. Creating a row in the Order table with System.Data and Dapper

public void CreateOrder(Order order)
{
  var command = connection.CreateCommand();
  command.CommandText = @"INSERT INTO [Order]
    (SupplierId, PartTypeId, PartCount,
    PlacedDate) VALUES (@supplierId,
    @partTypeId, @partCount, @placedDate);
    SELECT last_insert_rowid();";                             1
  AddParameter(command, "@supplierId",                        2
    order.SupplierId);
  AddParameter(command, "@partTypeId", order.PartTypeId);
  AddParameter(command, "@partCount", order.PartCount);
  AddParameter(command, "@placedDate", order.PlacedDate);
  long orderId = (long)command.ExecuteScalar();               3
  order.Id = (int)orderId;                                    4
}

public void CreateOrderDapper(Order order)                    5
{
  order.Id =                                                  6
    connection.Query<int>(
    @"INSERT INTO [Order]
      (SupplierId, PartTypeId, PartCount,
      PlacedDate) VALUES (@SupplierId,                        7
      @PartTypeId, @PartCount, @PlacedDate);
    SELECT last_insert_rowid();",
    order).First();                                           8
}

• 1 Recall that this gets the unique ID for the just-inserted row.
• 2 AddParameter is a helper method to create a DbParameter.
• 3 ExecuteScalar returns one value, which is the ID.
• 4 Applies the ID to the Order object
• 5 Dapper version of CreateOrder
• 6 Dapper won’t automatically set the Id.
• 7 Note that the parameter names are case-sensitive.
• 8 Return value for Query is IEnumerable<int>, hence the First()

Both methods use the same SQL statement, with the exception of the slight alteration to the parameter names, because Dapper uses a case-sensitive comparison on the property names of the Order object. Both methods assign the integer return value to the Id property. But where the System.Data approach needs eight C# statements (counting the semicolons), the Dapper approach only needs one.

6.1.2. Applying transactions to Dapper commands

The CreateOrder method from chapter 5 inserted a row into two different tables as part of a transaction: the row in the Orders table marks that an order was placed, and the row in the SendEmailCommand table signals another system to send an email to the supplier. You need to create either both records or neither of them (in case of a failure), so you created a DbTransaction object on the DbConnection and applied that transaction object to each DbCommand. In Dapper, you do basically the same thing.

The following listing shows how you can rewrite the CreateOrder method to use Dapper.

Listing 6.5. CreateOrder method rewritten to use Dapper

public void CreateOrder(Order order)
{
  var transaction = connection.BeginTransaction();             1
  try {
    order.Id = connection.Query<int>(
      @"INSERT INTO [Order]
        (SupplierId, PartTypeId, PartCount,
        PlacedDate) VALUES (@SupplierId,
        @PartTypeId, @PartCount, @PlacedDate);
      SELECT last_insert_rowid();", order,
      transaction).First();                                    2

    connection.Execute(@"INSERT INTO SendEmailCommand          3
      ([To], Subject, Body) VALUES
      (@To, @Subject, @Body)", new {                           4
        To = order.Supplier.Email,
        Subject = $"Order #{order.Id} for {order.Part.Name}",
        Body = $"Please send {order.PartCount}" +
          $" items of {order.Part.Name} to Widget Corp"
      }, transaction);                                         5

    transaction.Commit();                                      6
  }
  catch {
    transaction.Rollback();                                    7
    throw;
  }
}

• 1 Creates DbTransaction object
• 2 Adds the transaction to the command
• 3 Uses Execute instead of Query
• 4 Creates an anonymous type to hold the parameters
• 5 Adds the transaction to this command too
• 6 Commits on success
• 7 Rolls back on failure

The syntax of an anonymous type is new { Property1 = value1, Property2 = value2 }. This overloads the new operator to create an instance of a new anonymous type.

You don’t need to return a SendEmailCommand, so you don’t need to get the last_insert_rowid() from the insert command, which means you can use Execute instead of Query. Think of Execute as equivalent to the ExecuteNonQuery method from the DbCommand class. It executes the SQL statements and returns the number of rows affected.

6.1.3. The drawback of a micro-ORM

You’ve seen how easy it is to use Dapper. It saves you from a lot of boilerplate code and maintains great performance. Depending on your application, however, there’s a drawback to using Dapper—and micro-ORMs in general. The problem is that you’re writing SQL in your application code, and SQL isn’t standardized.

SQL isn’t the same for every database. SQLite will have different syntax and capabilities than SQL Server and PostgreSQL. For example, in SQLite you used last_insert_rowid() to get the ID for the row inserted by the previous statement. In SQL Server, you’d use SCOPE_IDENTITY(), and in PostgreSQL you’d use INSERT INTO ... RETURNING id.

In order to support different databases, you could turn ScmContext into an interface. To add support for a new database, you’d then create a new implementation of that interface. Figure 6.1 illustrates such a design.

Figure 6.1. Data-access layer (DAL) design for SCM context

In figure 6.1, there are three implementations of the IScmContext interface, indicated by the dotted lines. When you call the GetScmContext method, you’ll get back an IScmContext object, and the GetScmContext method is responsible for choosing which implementation class to instantiate. As a user of DataAccessFactory, you don’t care which implementation was chosen or how that implementation works. Because all your code uses IScmContext, DataAccessFactory can create a SqliteScmContext object for unit testing and a SqlServerScmContext object for production.

The following listing shows the contents of the DataAccessFactory class.

Listing 6.6. Implementation of DataAccessFactory

using Scm.Dal.SQLite;                                    1
using Scm.Dal.SqlServer;
using Scm.Dal.PostgreSql;

enum DatabaseType {                                      2
  Sqlite,
  SqlServer,
  PostgreSql
}

static class DataAccessFactory
{
  internal static string ConnectionString {get; set;}    3
  internal static IScmContext GetScmContext(             4
    DatabaseType dbType)
  {
    switch (dbType)
    {
      case DatabaseType.Sqlite:
        return new SqliteScmContext(ConnectionString);
      case DatabaseType.SqlServer:
        return new SqlServerScmContext(ConnectionString);
      case DatabaseType.PostgreSql:
        return new PostgreSqlScmContext(ConnectionString);
      default:
        throw new ArgumentException(
          $"Unrecognized Database type {dbType}", "dbType");
    }
  }
}

• 1 Tightly coupled to all implementations
• 2 Enumerates all supported databases
• 3 Caller must set connection string before calling GetScmContext
• 4 Caller must know what database they want

DataAccessFactory uses the factory design pattern, which allows you to create a new object that implements an interface without knowing the specific implementation type. The factory design pattern solves the problem of determining which implementation object to create, but it introduces an interesting set of other problems. One is that the factory class must know all the implementations of a given interface. Also, adding new interfaces to the factory means adding new methods. Developers using the DataAccessFactory NuGet package will find it odd that they have to download the Oracle implementation even though they’re using SQL Server. Also, in order to add support for a new database, you’d have to publish a new version of the package.

If you want to overcome these limitations and separate the IScmContext implementations into different packages, then the factory pattern won’t be suitable for your needs. A better way to handle this situation is to use dependency injection.

6.1.4. A brief introduction to dependency injection

Dependency injection (DI) is a design principle for creating objects. When you create a new object, A, it may rely on another object, B, to perform its functions. Instead of A controlling the creation of B, it allows the host or creator of A to supply an object, B. Thus, the inversion of control.

The factory pattern is also a design principle for creating objects. To understand why the DI pattern is superior to the factory pattern, let’s explore the factory pattern in more depth. A factory has a clearly defined set of methods that return objects that implement a given interface. The implementation can be chosen in many ways, including with configuration and method parameters—allowing the host to indicate which implementation to use. DataAccessFactory from listing 6.6 is a typical implementation of a factory.

The advantage of the factory pattern is that you can let the host choose which implementation of IScmContext to use. An xUnit test can choose SQLite, and your production website can choose PostgreSQL. The disadvantage is that you have to include all implementations in your list of dependencies. Adding a new implementation, like MySql, would mean another dependency and a modification to the factory class.

The new operator creates the tight coupling. If you could remove that, you could pass in the Type object for the implementation. The following listing shows how you could modify the factory class to remove the new operator.

Listing 6.7. Factory modified to use Type object

using System;
using System.Linq;
using System.Reflection;

static class DataAccessFactory {
  internal static Type scmContextType = null;
  internal static Type ScmContextType {                              1
    get { return scmContextType; }
    set {
      if (!value.GetTypeInfo().ImplementedInterfaces.
          Contains(typeof(IScmContext))) {                           2
        throw new ArgumentException(
          $"{value.GetTypeInfo().FullName} doesn't implement IScmContext");
      }
      scmContextType = value;
    }
  }
  internal static IScmContext GetScmContext() {
    if (scmContextType == null) {
      throw new ArgumentNullException("ScmContextType not set");
    }
    return Activator.CreateInstance(scmContextType)                  3
      as IScmContext;                                                4
  }
}

• 1 Host must set the implementation type before calling GetScmContext
• 2 Checks that the type actually implements IScmContext
• 3 An alternative to new that creates an object of the passed-in Type
• 4 as is similar to a typecast.

In listing 6.7, you’ve removed the tight coupling by requiring that the host pass in the implementation type. This code uses the Type object, but there are many other ways of using reflection to get the type. Instead of holding the dependencies in the factory, the host is injecting the dependency—hence the “dependency injection” name of this pattern.

One problem with listing 6.7 is that you don’t pass in the connection string for types that need it. This code assumes there’s a default constructor, which isn’t a safe assumption. The way to solve this problem is to require the host to create the implementation object, as follows.

Listing 6.8. Factory modified to make the host supply the implementation object

using System.Collections.Generics;

static class DataAccessFactory
{
  internal static Dictionary<Type, object> implementations =
    new Dictionary<Type, object>();

  internal static void AddImplementation<T>(T t) where T : class {
    implementations.Add(typeof(T), t);
  }
  internal static T GetImplementation<T>() where T : class {
    return implementations[typeof(T)] as T;
  }
}

Let’s unpack listing 6.8 by first looking at the Dictionary. It maps types to objects—this factory class is really only a wrapper around the Dictionary. The AddImplementation method uses C# generics to check that the object passed in the parameter implements or is a child type of the generic parameter, T. There’s also a constraint (where T : class) to make sure nobody tries to use a value type or struct.

Make a general-purpose factory

You don’t add the generic constraint where T : IScmContext. That would mean that you could only use this class for IScmContext types, and only one implementation is needed for the application. Removing the constraint makes this more general-purpose, which means it should probably be called something other than DataAccessFactory.

To use this factory, a host, H, that uses a class, A, will create the implementation object, B, for IScmContext and add it to the DataAccessFactory. When A needs to use object B, it gets it from DataAccessFactory.

You’ve now modified your factory to the point where it’s no longer a factory. It instead fits the DI (or inversion of control) pattern. The host is responsible for creating the implementation object, so it will provide the connection string if needed.

Removing the tight coupling is the biggest advantage of DI. The pattern also makes the inversion of control easy enough that you can use it in other places and thereby make your code easier to unit test.

6.1.5. Dependency injection in .NET Core

There are many DI libraries to choose from when writing .NET Framework applications, and some of those libraries will certainly port to .NET Standard to expand their platform reach. The ASP.NET Core team couldn’t wait that long and built their own DI library. You’ll use that DI library to build your data-access layer.

Figure 6.2 shows how the classes and packages lay out if you move from the factory pattern to the DI pattern. Each implementation of IScmContext is separated into its own package that’s included only if the host decides to use it.

Figure 6.2. Data-access layer for SCM context using DI instead of the factory pattern

You’re going to implement your data-access layer using DI. Use the following commands to set up a new set of projects:

cd ..
mkdir DapperDi
cd DapperDi
dotnet new classlib -o ScmDataAccess
dotnet new classlib -o SqliteDal
dotnet new xunit -o SqliteScmTest

The ScmDataAccess and SqliteDal folders will have Class1.cs files in them. They won’t cause any harm, but you don’t need them, so feel free to remove them. In ScmDataAccess, copy the InventoryItem.cs, Order.cs, PartCommand.cs, PartType.cs, SendEmailCommand.cs, and Supplier.cs files from the previous examples. You’ll need to change the namespace from WidgetScmDataAccess to ScmDataAccess because you’re turning this into a product rather than a custom solution for Widget Corp.

Add a new file to ScmDataAccess called IScmContext.cs with the contents from the following listing.

Listing 6.9. Contents of the IScmContext interface

using System.Collections.Generic;
using System.Data.Common;

namespace ScmDataAccess
{
  public interface IScmContext
  {
    IEnumerable<PartType> Parts { get; }
    IEnumerable<InventoryItem> Inventory { get; }
    IEnumerable<Supplier> Suppliers { get; }
    PartCommand[] GetPartCommands();                             1
    void DeletePartCommand(int id, DbTransaction transaction);
    void UpdateInventoryItem(int partTypeId, int count,
      DbTransaction transaction);
    void CreateOrder(Order order);
    DbTransaction BeginTransaction();
    IEnumerable<Order> GetOrders();
  }
}

• 1 Array instead of IEnumerable

Be mindful that enumerables provide data as soon as it’s available

You used GetPartCommands in chapter 5, but I didn’t point out that it returns an array of PartCommand objects. By contrast, GetOrders returns an IEnumerable<Order>. The reason for this difference is that you’re deleting the rows from the PartCommand table as you iterate through the list. You want the full list before you do that so that deleting the rows doesn’t interfere with any locks taken during the read of the PartCommand table. IEnumerable collections can start providing objects before all of them are read. It should be clear from the API signature that all PartCommand objects are read into memory, and you do that by specifying the array.

Modify the ScmDataAccess.csproj file as follows.

Listing 6.10. ScmDataAccess.csproj contents

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netstandard1.2</TargetFramework>          1
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.Common"
                      Version="4.3.0" />
  </ItemGroup>

</Project>

• 1 .NET Standard 1.2 is required for System.Data.Common.

Now build the SqliteDal project. Change SqliteDal.csproj as follows.

Listing 6.11. SqliteDal.csproj contents

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netstandard1.3</TargetFramework>          1
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.Sqlite"
                      Version="1.1.0" />
    <PackageReference Include="Dapper"
                      Version="1.50.2" />
    <ProjectReference Include="../ScmDataAccess/ScmDataAccess.csproj" />
  </ItemGroup>

</Project>

• 1 Uses .NET Standard 1.3 because you’re using Dapper

Create a file called SqliteScmContext.cs. This class will be the implementation of the IScmContext interface for the Sqlite database. Implement it as follows.

Listing 6.12. SqliteScmContext class—a SQLite implementation of IScmContext

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using Dapper;
using Microsoft.Data.Sqlite;
using ScmDataAccess;

namespace SqliteDal {
  public class SqliteScmContext : IScmContext {
    private SqliteConnection connection;
    public IEnumerable<PartType> Parts { get; private set; }
    public IEnumerable<InventoryItem> Inventory { get; private set; }
    public IEnumerable<Supplier> Suppliers { get; private set; }

    public SqliteScmContext(SqliteConnection conn) {                     1
      connection = conn;
      conn.Open();                                                       2
      Parts = conn.Query<PartType>("SELECT * FROM PartType");
      Inventory = conn.Query<InventoryItem>("SELECT * FROM InventoryItem");
      foreach (var item in Inventory)
        item.Part = Parts.Single(p => p.Id == item.PartTypeId);
      Suppliers = conn.Query<Supplier>("SELECT * FROM Supplier");
      foreach (var supplier in Suppliers)
        supplier.Part = Parts.Single(p => p.Id == supplier.PartTypeId);
    }

    public PartCommand[] GetPartCommands() {
      return connection.Query<PartCommand>("SELECT * FROM PartCommand")
        .ToArray();                                                      3
    }

    public void DeletePartCommand(int id, DbTransaction transaction) {
      connection.Execute(@"DELETE FROM PartCommands
        WHERE Id=@Id", new { Id = id }, transaction);                    4
    }

    public void UpdateInventoryItem(int partTypeId, int count,
      DbTransaction transaction) {
      connection.Execute(@"UPDATE InventoryItem
        SET Count=@Count
        WHERE PartTypeId=@PartTypeId",
        new { Count = count, PartTypeId = partTypeId},
        transaction);
    }

    public void CreateOrder(Order order) {                               5
      var transaction = connection.BeginTransaction();
      try {
        order.Id = connection.Query<int>(
          @"INSERT INTO [Order]
          (SupplierId, PartTypeId, PartCount,
          PlacedDate) VALUES (@SupplierId,
          @PartTypeId, @PartCount, @PlacedDate);
          SELECT last_insert_rowid();", order,
          transaction).First();

        connection.Execute(@"INSERT INTO SendEmailCommand
          ([To], Subject, Body) VALUES
          (@To, @Subject, @Body)", new {
            To = order.Supplier.Email,
            Subject = $"Order #{order.Id} for {order.Part.Name}",
            Body = $"Please send {order.PartCount}" +
              $" items of {order.Part.Name} to Widget Corp"
          }, transaction);

        transaction.Commit();
      }
      catch {
        transaction.Rollback();
        throw;
      }
    }

    public DbTransaction BeginTransaction() {
      return connection.BeginTransaction();
    }

    public IEnumerable<Order> GetOrders() {
      var orders = connection.Query<Order>("SELECT * FROM [Order]");
      foreach (var order in orders) {                                    6
        order.Part = Parts.Single(p => p.Id == order.PartTypeId);
        order.Supplier = Suppliers.Single(s => s.Id == order.SupplierId);
      }

      return orders;
    }
  }
}

• 1 There can be multiple instances of SQLite in memory.
• 2 Open can safely be called multiple times.
• 3 ToArray reads all the values from the IEnumerable into an array.
• 4 Uses an anonymous type for the Id parameter
• 5 Same CreateOrder method from earlier
• 6 foreach will get all Order objects in the IEnumerable, just like ToArray.

Use yield return to start processing data sooner

The GetOrders method uses foreach to supply the values for the Part and Supplier properties. Because foreach enumerates through all the Order objects, the entire result set from the SQL query has to be read. This can be a performance issue if there are a lot of orders. Instead of return orders at the end, you could put a yield return order inside the foreach loop to return the Order objects to the caller one at a time.

Now test this code out. The test project, SqliteScmTest, is going to act as the host. You’ll use DI to mark SqliteScmContext as the implementation for IScmContext. The following listing shows how to add the DI libraries to SqliteScmTest.csproj.

Listing 6.13. Adding DI to SqliteScmTest.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="15.0.0" />
    <PackageReference Include="xunit" Version="2.2.0" />
    <PackageReference Include="xunit.runner.visualstudio" Version="2.2.0" />
    <PackageReference Include="Microsoft.Data.Sqlite"
                      Version="1.1.0" />
    <PackageReference
      Include="Microsoft.Extensions.DependencyInjection.Abstractions"
      Version="2.0.0" />                                              1
    <PackageReference
      Include="Microsoft.Extensions.DependencyInjection"
      Version="2.0.0" />                                              2
    <ProjectReference Include="../SqliteDal/SqliteDal.csproj" />
  </ItemGroup>

</Project>

• 1 Abstract types for DI
• 2 Implementation of DI abstractions

As in the previous examples, you’ll create a class fixture for your xUnit tests. The fixture is also responsible for initializing the DI settings. The full code is available online, but the important bits are shown in the next listing.

Listing 6.14. SampleScmDataFixture class using DI

using System;                                                     1
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.DependencyInjection;                   2
using ScmDataAccess;
using SqliteDal;

namespace SqliteScmTest
{
  public class SampleScmDataFixture
  {
    private const string PartTypeTable =
        @"CREATE TABLE PartType(
            Id INTEGER PRIMARY KEY,
            Name VARCHAR(255) NOT NULL
          );";
                                                                  3
    public IServiceProvider Services                              4
      { get; private set; }
    public SampleScmDataFixture()
    {
      var conn = new SqliteConnection(
        "Data Source=:memory:");                                  5
      conn.Open();
      (new SqliteCommand(PartTypeTable, conn)).ExecuteNonQuery();
                                                                  6
      var serviceCollection = new ServiceCollection();
      IScmContext context = new SqliteScmContext(conn);
      serviceCollection.AddSingleton<IScmContext>(                7
        context);
      Services = serviceCollection.BuildServiceProvider();
    }
  }
}

• 1 IServiceProvider is in the System namespace and not specific to DI.
• 2 Include both namespaces.
• 3 The rest of the table-creation statements are available online.
• 4 This is how you’ll expose DI to the tests.
• 5 Connection string specifies an in-memory database
• 6 The rest of the table-creation code is available online.
• 7 Creates a singleton instance of SqliteScmContext to implement IScmContext

In listing 6.14 you’re using a singleton SqliteScmContext object. Singleton means that every time you use the Services property to get an implementation of IScmContext, you’ll get the same instance.

Each SqliteConnection creates its own in-memory database, so if you want to share that database between tests, you’ll use a singleton. But what if you don’t want one test’s changes to the database to interfere with another test? xUnit runs the tests in parallel and in random order, so it could cause problems if you’re expecting a test to be anything other than atomic. In this case, you can have a new SqliteScmContext object created every time. The following listing shows how to rewrite the constructor of SampleScmDataFixture to use transient objects instead of a singleton.

Listing 6.15. Creating transient objects instead of using a singleton

public SampleScmDataFixture()
{
  var serviceCollection = new ServiceCollection();
  serviceCollection.AddTransient<IScmContext>(
    provider => {                                                1
    var conn = new SqliteConnection("Data Source=:memory:");
    conn.Open();
    (new SqliteCommand(PartTypeTable, conn)).ExecuteNonQuery();
    return new SqliteScmContext(conn);
  });
  Services = serviceCollection.BuildServiceProvider();
}

• 1 Transient objects need to be created when asked for, hence the delegate.

In listing 6.14 you created a new SqliteScmContext object and called the AddSingleton method on the ServiceCollection. When you retrieved the IScmContext implementation later, you got that object back.

With the AddTransient method in listing 6.15, you need to create a new SqliteScmContext object every time it’s retrieved. The only way to do that is to invoke a method you provide, and to do that you use an anonymous delegate.

You’re now all set to create a test and try it out. Modify the UnitTest1.cs file as follows.

Listing 6.16. Test SCM data-access layer using Dapper, SQLite, and DI

using System;
using System.Linq;
using ScmDataAccess;
using Microsoft.Extensions.DependencyInjection;                1
using Xunit;

namespace SqliteScmTest
{
  public class UnitTest1 : IClassFixture<SampleScmDataFixture>
  {
    private SampleScmDataFixture fixture;
    private IScmContext context;

    public UnitTest1(SampleScmDataFixture fixture)
    {
      this.fixture = fixture;
      this.context = fixture.Services.
        GetRequiredService<IScmContext>();                     2
    }

    [Fact]
    public void Test1()
    {
      var orders = context.GetOrders();
      Assert.Equal(0, orders.Count());                         3
      var supplier = context.Suppliers.First();
      var part = context.Parts.First();
      var order = new Order() {
          SupplierId = supplier.Id,
          Supplier = supplier,
          PartTypeId = part.Id,
          Part = part,
          PartCount = 10,
          PlacedDate = DateTime.Now
      };
      context.CreateOrder(order);                              4
      Assert.NotEqual(0, order.Id);
      orders = context.GetOrders();
      Assert.Equal(1, orders.Count());                         5
    }
  }
}

• 1 This namespace is part of the Abstractions dependency.
• 2 Extension of IServiceProvider in the Abstractions dependency
• 3 Verifies that there are no orders
• 4 Creates an order
• 5 Verifies that the order was created

Be mindful of using singletons in unit tests

The constructor doesn’t change, regardless of whether the host has set a singleton or a transient for the implementation of IScmContext. You simply ask for the implementation, and it uses whatever was specified by the host to get the object. However, Test1 would need to change to support singletons, because it expects the order count to be 0 initially, and 1 after the order is created. If another test created an order, and you were using a singleton SqliteScmContext, this test would fail. Because xUnit randomizes the test order, this might not happen all the time.

Using the Microsoft DI library without adding a dependency on it

Your business-logic code depends on some implementation of IScmContext, but it doesn’t take a dependency on any particular implementation because it’s using DI. This is nice, because you don’t need to add a project or package reference to the SQLite or SQL Server libraries. Instead, you added a reference to the Microsoft.Extensions.DependencyInjection.Abstractions package. But it turns out you don’t have to do that either.

You only add the dependency to the Abstractions library because you’re using the GetRequiredService extension method. If you rewrite the code as follows, you can remove the reference to the Abstractions library.

Listing 6.17. Changing the GetRequiredService extension method call to an equivalent

public void InitWithDi(IServiceProvider services)
{
  this.context = services.
    GetRequiredService<IScmContext>();                 1
}

public void InitWithoutDi(IServiceProvider services)
{
  this.context = services.
    GetService(typeof(IScmContext)) as IScmContext;    2
}

• 1 Uses extension method from DI abstractions package
• 2 Uses built-in methods on IServiceProvider

IServiceProvider is used in .NET for all kinds of things, not just DI, and it’s included in .NET Core. You only want to use one DI library in your application’s code, so if you’re publishing a NuGet package, other developers will appreciate that your library doesn’t depend on any particular DI implementation.

Note that there are some advanced features of the Microsoft Extensions DI library that may not work with IServiceProvider. But for this simple example, it will work for both singleton and transient.

6.1.6. Configuring the application

Until now, you’ve only used in-memory databases with SQLite. That has allowed you to get away with hard-coding the connection string to Data Source=:memory:. But real applications have different connection strings based on the environment in which they’re deployed, so hard-coding isn’t an option. You’ll need a way to configure the connection string.

XML configuration in .NET

.NET Framework developers are familiar with XML configuration via the app.config file. When built, it has the same name as the executable, but appended with .config. App.config has a special section for connection strings, which are retrieved in code using the System.Configuration library. .NET Core doesn’t have XML configuration built in, as of the writing of this book (but it may be added in later versions). This means .NET Core applications need to provide their own configuration.

To handle configuration in .NET Core, you’ll again turn to the Microsoft.Extensions libraries. Modify the SqliteScmTest.csproj file as shown in the following listing to add references to the Microsoft.Extensions.Configuration packages.

Listing 6.18. Add Microsoft.Extensions.Configuration reference to test project

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="15.3.0" />
    <PackageReference Include="xunit" Version="2.2.0" />
    <PackageReference Include="xunit.runner.visualstudio" Version="2.2.0" />
    <PackageReference Include="Microsoft.Data.Sqlite" Version="2.0.0" />
    <PackageReference
      Include="Microsoft.Extensions.DependencyInjection.Abstractions"
      Version="2.0.0" />
    <PackageReference Include="Microsoft.Extensions.DependencyInjection"
                      Version="2.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration"
                      Version="2.0.0" />                                 1
    <PackageReference Include="Microsoft.Extensions.Configuration.Json"
                      Version="2.0.0" />                                 2
    <ProjectReference Include="../SqliteDal/SqliteDal.csproj" />
    <None Include="config.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>

</Project>

• 1 Configuration package
• 2 There are lots of ways to configure; use JSON.

The SampleScmDataFixture class will read the configuration, because it needs the connection string in order to create the IScmContext object. To do this, modify the test class fixture, as follows.

Listing 6.19. SampleScmDataFixture using configuration to get the connection string

using System;
using System.Collections.Generic;
using ScmDataAccess;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.DependencyInjection.Abstractions;
using SqliteDal;

namespace SqliteScmTest
{
  public class SampleScmDataFixture
  {
    const string PostsTable = ...;                                   1

    const string ConnStrKey = "ConnectionString";
    const string DefConnStr = "Data Source=:memory:";                2

    static Dictionary<string, string> Config {get;} =                3
      new Dictionary<string, string>()
      {
        [ConnStrKey] = DefConnStr                                    4
      };

    public IServiceProvider Services { get; private set; }

    public SampleScmDataFixture()
    {
      var configBuilder = new ConfigurationBuilder();
      configBuilder                                                  5
        .AddInMemoryCollection(Config)                               6
        .AddJsonFile("config.json", true);                           7
      var configRoot = configBuilder.Build();
      var connStr = configRoot[ConnStrKey];                          8
      var serviceCollection = new ServiceCollection();
      serviceCollection.AddTransient<IScmContext>(provider => {
        var conn = new SqliteConnection(connStr);
        conn.Open();
        (new SqliteCommand(PartTypeTable, conn)).ExecuteNonQuery();
                                                                     9
        return new SqliteScmContext(conn);
      });
      Services = serviceCollection.BuildServiceProvider();
    }
  }
}

• 1 Copy from the previous chapter or from companion code on GitHub
• 2 This will be your fallback in case the config file isn’t there.
• 3 Dictionary serves as a key/value pair collection
• 4 Adds a key with the name ConnectionString
• 5 This coding style is called method chaining or fluent interface.
• 6 Serves as the default; last one with a value wins
• 7 true indicates the file is optional.
• 8 Retrieves the connection string, given the key
• 9 The rest of the code is available online.

To get the configuration, you start with the ConfigurationBuilder. The order in which you add configuration sources to the builder matters. The first source added is the last source tapped for configuration data. In this case, if a configuration value doesn’t exist in the config.json file, it’ll check the in-memory collection. If you have default values for configuration, it makes sense to put them in an in-memory collection and add it to the builder as the first source.

The AddInMemoryCollection method is built into the regular configuration package, but the AddJsonFile method is an extension method from the JSON package. There are several configuration packages, such as XML, INI, command line, and Azure Key Vault. They all implement an extension method with the method-chaining pattern. Method chaining is a nice way to make code cleaner. Each Add method returns the ConfigurationBuilder object so another Add method can be applied. It prevents you from having to write the configBuilder. on each line.

Once all the configuration sources are applied to the builder, you call the Build method to get an object that implements the IConfigurationRoot interface. IConfigurationRoot inherits from IConfiguration, which has an indexer for getting configuration values. In this case, the indexer takes the name of the configuration property and returns the first value it finds when searching through the configuration sources in the reverse order of how they were added.

You put a value for the connection string in the in-memory collection, which serves as your default value. If the configuration library can’t find the config.json file, or if the file doesn’t contain a definition for ConnectionString, you’ll still have the default value. That means you can execute this code without creating the config.json file.

To test how the JSON configuration works, create a config.json file with a connection string, as shown in the next listing.

Listing 6.20. config.json file with the SQLite connection string

{
  "ConnectionString": "Data Source=scm.db"         1
}

• 1 File-based data source

SQLite can use a file-based database instead of an in-memory database. You wouldn’t commonly use file-based databases with unit tests because the tests modify the database, which would make testing results inconsistent from run to run. But it’s a great way to detect whether the configuration system is finding the config.json file instead of using the default connection string.

You’ll need to copy this file to the build output so that the configuration library can find it. Modify the SqliteScmTest.csproj file by adding the item group shown in the following listing.

Listing 6.21. Copy the configuration file to the output folder

<ItemGroup>
  <None Include="config.json">
    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
  </None>
</ItemGroup>

Execute the tests and check the bin/Debug/netcoreapp2.0 folder to make sure that a file called scm.db was created. The presence of this file proves that the connection string from the config.json file was used.

Configuring the connection string through a separate file is necessary for deploying to different environments. You could also use configuration to specify which implementation of IScmContext to use in your DI library. Some DI libraries allow you to configure them through configuration files, but because there isn’t a standard way of doing that in .NET Core, there’s no built-in support for configuration in the Microsoft DI extensions library.

6.1.7. When to build your own data-access layer

We’ve looked at two approaches for building a custom data-access layer.

The first was to use the barebones approach of executing queries by supplying parameters and reading the results into objects manually. This may be a useful technique in certain circumstances, such as for object-mapping rules that are too complicated for an ORM library, or for high-performance applications.

The second approach was to use a micro-ORM to do the tedious work. This allows you to be more productive while not sacrificing much performance. You still need to know SQL, though, and the potential for SQL injection attacks is still there, because you’re writing SQL statements to a string. But you spend less time writing boilerplate code to transfer data between objects and database entities.

Both of these methods require significant design work. Different databases use different flavors of SQL, so it helps to have DI. You also need to be able to unit test your code. The code should also support configuration so that it can be easily and securely configured in all environments.

Many of the applications I’ve worked on haven’t needed custom data-access layers. The SQL queries themselves always took more time than the ORM code. In those cases, it was more important to make developers more productive than to squeeze performance out of the code. But the more code you write, the more you need to maintain. This is why I often turn to full ORM libraries like Entity Framework.

6.2. Entity Framework Core

Alongside ASP.NET Core, Microsoft is building Entity Framework (EF) Core. EF Core is a .NET Core version of Entity Framework, which is a full ORM built by Microsoft for the .NET Framework. Instead of porting EF from the .NET Framework, the team decided to rewrite most of EF—much like how ASP.NET Core is a rewrite of ASP.NET. You’ll explore EF briefly by using it to rewrite your supply-chain management data-access layer.

You can start by creating the projects using the following commands. You’ll create the ScmDataAccess project as before, but you won’t need a project for implementing each type of database. You’ll include an xUnit test project called ScmDalTest to make sure everything’s working:

cd ..
mkdir EfTest
cd EfTest
dotnet new classlib -o EfScmDataAccess
dotnet new xunit -o EfScmDalTest

The PartType class remains the same, but the InventoryItem and Supplier classes will be slightly different for EF. The changes are shown in listings 6.22 and 6.23.

Listing 6.22. InventoryItem.cs modified for use with EF

namespace EfScmDataAccess
{
  public class InventoryItem
  {
    public int Id { get;set; }                        1
    public PartType Part { get; set; }                2
    public int Count { get; set; }
    public int OrderThreshold { get; set; }
  }
}

• 1 InventoryItem needs its own identity column.
• 2 No PartTypeId; EF will get the Part for you.

Listing 6.23. Supplier.cs modified for use with EF

namespace EfScmDataAccess
{
  public class Supplier
  {
    public int Id { get; set; }
    public string Name { get;set; }
    public string Email { get; set; }
    public PartType Part { get; set; }             1
  }
}

• 1 No PartTypeId; EF will get the Part for you.

Just like with Dapper, EF uses conventions so your code can look cleaner. Both can also work with custom attributes if you need to specify certain behaviors. As a micro-ORM, Dapper doesn’t know about the relationships between objects. This is where a full ORM, like EF, differentiates itself.

In listings 6.22 and 6.23, you add a PartType object. EF interprets this as a one-to-one relationship between an InventoryItem or Supplier object and a PartType object. It also understands that there are foreign keys between the associated tables. Two of the conventions EF uses are <otherclassname><idproperty>, indicating a relationship, and the Id or <classname>Id property, indicating an identity.

Now, let’s take a look at the EfScmContext class in the next listing.

Listing 6.24. EfScmContext—ScmContext modified for use with EF

using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace EfScmDataAccess
{
  public class EfScmContext : DbContext                            1
  {
    public DbSet<PartType> Parts { get; set; }                     2
    public DbSet<InventoryItem> Inventory { get; set; }
    public DbSet<Supplier> Suppliers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder
     optionsBuilder)
    {
      optionsBuilder.UseSqlite("Filename=efscm.db");               3
    }
  }
}

• 1 Must inherit from DbContext
• 2 DbSet is a collection provided by EF.
• 3 Using SQLite with a file-based database

That’s basically all you need to do for the data-access code. EF generates the SQL commands necessary to perform the create, retrieve, update, and delete operations that you want. These operations are all available on the DbSet class, which we’ll look into later.

Now you have to create the database schema, and EF can generate it for you. It doesn’t do this when your application starts up, though. Instead, you need to use EF’s .NET CLI migration tool to apply the schema to the database. This also means that if you want EF to create the database schema, you can’t use an in-memory Sqlite database. .NET CLI tools are pulled in as dependencies.

To use the EF migration tool, the first step is to modify the EfScmDataAccess.csproj file.

Listing 6.25. EfScmDataAccess.csproj modified for EF and EF migration tool

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>                       1
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite"
                      Version="2.0.0" />                                   2
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design"
                      Version="2.0.0" />                                   3
    <DotNetCliToolReference
                      Include="Microsoft.EntityFrameworkCore.Tools.DotNet"
                      Version="2.0.0" />                                   4
  </ItemGroup>
</Project>

• 1 Custom tools require netcoreapp instead of netstandard.
• 2 EF’s library includes everything for using SQLite.
• 3 Needed for the EF CLI tool
• 4 The .NET CLI tool reference

6.2.1. Using EF migrations to create the database

EF has a custom tool to generate something called a migration, which is essentially a means of migrating your database from one version to the next. In our example, the database has no tables to start out with. The EF migration will create tables to match your model. If you were to build a new version of the code where you made changes to the model, another migration step would be added to migrate an existing database to the new schema. EF keeps track of all the migrations, so it can migrate a database from any previous version (or from scratch) to the latest version. In some cases, there are data changes as well as schema changes. EF cleverly keeps track of all that.

Update migrations don’t work on SQLite

Migrations for SQLite only work to create the database. They won’t update an existing database to a new version.

CLI tools only work on console applications

One of the issues with custom tools in the .NET CLI is that they can only be used on console applications. You don’t want your data-access layer to be a console application, so you’re using a workaround where EfScmDalTest stands in as the console application.

In order to get your migration tool to work, you’ll need to modify the EfScmDalTest unit test project. The following listing shows the modified project file.

Listing 6.26. EfScmDalTest.csproj file modified to support the EF migration tool

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="15.0.0" />
    <PackageReference Include="xunit" Version="2.2.0" />
    <PackageReference Include="xunit.runner.visualstudio" Version="2.2.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design"
                      Version="2.0.0" />                                 1
    <PackageReference Include="System.Runtime.Serialization.Primitives"
                      Version="4.3.0" />
    <ProjectReference Include="../EfScmDataAccess/EfScmDataAccess.csproj" />
  </ItemGroup>

  <ItemGroup>
    <None Include="efscm.db"                                             2
          Condition="Exists('efscm.db')">                                3
      <CopyToOutputDirectory>Always</CopyToOutputDirectory>
    </None>
  </ItemGroup>
</Project>

• 1 Needed to ensure the correct version of EF is referenced
• 2 Copies database file to output folder for testing
• 3 Only copies if the file exists

After you’ve run the dotnet build command on both projects, you can use the EF migration tool. Change to the EfScmDataAccess folder and execute the following command:

dotnet ef --startup-project ../EfScmDalTest migrations add EfScmMigration

This creates a Migrations folder in the project with a set of C# files in it. This is the code generated by EF to perform the migration.

Now all you need to do is test this code out.

6.2.2. Running the tests using EF

Before you can execute the tests, you’ll need to create the database. This is done using the same EF migration tool you used earlier. From the EfScmDataAccess folder, execute the following command to create the SQLite file-based database:

dotnet ef --startup-project ../EfScmDalTest database update

The tool creates the efscm.db file in the EfScmDalTest folder with the schema generated from the classes in the EfScmDataAccess project. When you build the EfScmDalTest project, this database file is copied to the build output.

Now test out this database by going back to the EfScmDalTest project and editing UnitTest1.cs as follows.

Listing 6.27. Test exercising some EF functionality

using System;
using System.Linq;
using Xunit;
using EfScmDataAccess;

namespace EfScmDalTest
{
  public class UnitTest1
  {
    [Fact]
    public void Test1()
    {
      using (var ctxt = new EfScmContext())            1
      {
        var partName = "Sample" +
          DateTime.Now.ToString("HHmmss");             2
        var part = new PartType() {                    3
            Name = partName
        };
        ctxt.Parts.Add(part);                          4
        ctxt.SaveChanges();                            5

        var getPart = ctxt.Parts.Single(               6
            p => p.Name == partName);
        Assert.Equal(getPart.Name, part.Name);

        ctxt.Parts.Remove(getPart);                    7
        ctxt.SaveChanges();

        getPart = ctxt.Parts.FirstOrDefault(           8
            p => p.Name == partName);
        Assert.Null(getPart);
      }
    }
  }
}

• 1 Context maintains database connections
• 2 Makes a unique part name so the unit test can be run multiple times
• 3 Creates a new PartType object
• 4 Adds the part to the DbSet
• 5 Commits the new PartType to the database
• 6 EF translates LINQ queries to SQL queries for you.
• 7 Deleting data is as simple as creating data.
• 8 Checks that the part is no longer there

In our example, the EfScmContext object has the connection to the SQLite database written in its constructor, but EF has plenty of other ways to construct the context and connect it to a database. When using EF, you don’t have to build a custom data-access layer with DI. The configuration extension library may be helpful, though.

DbSet operates much like a collection. When getting values from DbSet, your LINQ queries will be interpreted into SQL statements by EF. So just because you have thousands of parts in your inventory, doesn’t mean thousands of PartType objects will be held in memory. EF keeps track of the objects you retrieve in the context. If you make a change to an object and save it to DbSet, EF detects the change you made and generates the SQL UPDATE statement.

EF handles a lot of the work of communicating with a database—it has many providers to work with all kinds of databases. But regardless of the database you connect to, the code remains the same. Developers write queries using LINQ instead of SQL. For many applications, this can significantly boost productivity over writing a custom data-access layer.

Additional resources

To learn more about what we covered in this chapter, try the following resources:

• Design Patterns: Elements of Reusable Object-Oriented Software by Erich Gamma, Richard Helm, Ralph Johnson, and John Vlissides (Addison-Wesley Professional, 1994)
• Dependency Injection in .NET, second edition, by Steven van Deursen and Mark Seemann (Manning, 2018)—http://mng.bz/xSnW
• Entity Framework Core in Action by Jon P Smith (Manning, 2018)—http://mng.bz/cOH4
• Microsoft’s Entity Framework documentation—http://docs.efproject.net
• Configuration in ASP.NET Core—http://mng.bz/30T8

EF resources for .NET Framework apply in most cases

Although there are differences between the Framework and Core versions of Entity Framework, documentation on EF for the .NET Framework should have plenty of useful information.

Summary

In this chapter you learned about the Dapper and Entity Framework object-relational mapping libraries available in .NET Core. We covered these key concepts:

• Eliminating most boilerplate object-table mapping code with an ORM
• Using dependency injection to separate different data-access implementations
• Applying many kinds of configuration to .NET Core applications with extensions libraries for .NET Standard
• Executing custom tools with the .NET CLI

Here are some important techniques to remember from this chapter:

• The Microsoft.Extensions family has lots of useful libraries built on the .NET Standard.
• Micro-ORMs like Dapper can increase productivity without sacrificing performance.
• There are many options for configuration in .NET Core applications, including fallbacks.
• Some libraries, like Entity Framework, come with custom tools that can be used from the .NET CLI.
• In order to use custom tools on a library, specify another project with an entry point (like an xUnit test project) when running the tool.

ORM libraries increase developer productivity in many cases. Choosing an ORM depends on many factors, and we covered two different types in this chapter. There are a lot of data-access libraries out there, and many of them should make their way to .NET Standard in the future. You also learned about some useful stuff in the Microsoft.Extensions family of libraries, such as configuration and dependency injection. Other extensions libraries will be used later in the book.

Although relational data is important for many applications, not all data is in relational stores. In the next chapter we’ll cover how to get data from other services over a network.