Faithlife.Data

Faithlife.Data provides helpers for querying ADO.NET-compatible databases.

NuGet

Overview

The Faithlife.Data class library provides an enhanced API for interacting with ADO.NET-compatible databases. It is similar to Dapper and other “micro” ORMs for .NET.

To use this library, call DbConnector.Create() to create a DbConnector with a valid IDbConnection from your favorite ADO.NET database provider, e.g. Microsoft.Data.SqlClient for SQL Server or MySqlConnector for MySQL.

With a DbConnector, you can:

Consult the reference documentation for additional details.

What about Dapper?

If you are familiar with Dapper, you will note many similarities between it and this library. So why use Faithlife.Data? Here are a few key differences:

  • DbConnector wraps the connection, whereas Dapper primarly provides extension methods on IDbConnection.
  • With Dapper, you must remember to set the transaction parameter when there is an active transaction. Since Faithlife.Data tracks the current transaction, it attaches it to database commands automatically.
  • Faithlife.Data has direct support for modern C# and .NET, including tuples, IAsyncEnumerable, and the new index/range syntax.
  • The multi-mapping support of Faithlife.Data is simpler and more flexible than the map and splitOn parameters of Dapper.
  • Faithlife.Data avoids type conversion, requiring that the requested type exactly match the provided type, whereas Dapper will try to convert the value with Convert.ChangeType(). This is sometimes aggravating, but we feel it is better to know what the database is returning and avoid the surprises that type conversion can bring.
  • The async methods of Faithlife.Data call the async methods of the database provider more consistently than Dapper.
  • Faithlife.Data makes the choice between buffered and unbuffered queries more explicit by providing separate methods. This makes it more likely that clients will keep the difference in mind, and allows Query() to return an IReadOnlyList<T> instead of an IEnumerable<T>.
  • Dapper will edit the SQL in some scenarios, e.g. when it substitutes a collection parameter for a list of dynamically named parameters for easier IN support. This is convenient, but Faithlife.Data avoids editing the SQL for simplicity and predictability.
  • Faithlife.Data has an easy alternative to using anonymous objects for specifying parameters, which may have better performance for some clients and uses stronger types than Dapper’s param parameter of type object.
  • Faithlife.Data does less caching than Dapper. This may or may not be an advantage, depending on usage.

Creating a connector

Like IDbConnection, DbConnector is not thread-safe, so you will need one instance per connection. Consider defining a method to easily create a connection to your database.

DbConnector CreateConnector() =>
    DbConnector.Create(new SQLiteConnection("Data Source=:memory:"));

Executing a command

Once you have a connector, you can open the connection with OpenConnection(), create a command with Command(), and execute the command with Execute().

using (var connector = CreateConnector())
using (connector.OpenConnection())
{
    connector.Command(@"
create table widgets (
  id integer primary key autoincrement,
  name text not null);").Execute();
}

Accessing the database asynchronously

Every method that has communicates with the database has an asynchronous equivalent, e.g. ExecuteAsync().

using (var connector = CreateConnector())
using (await connector.OpenConnectionAsync())
{
    await connector.Command(@"
create table widgets (
  id integer primary key autoincrement,
  name text not null);").ExecuteAsync();
}

Note that asynchronous methods in this library return ValueTask, not Task, so be sure to follow the relevant guidelines, e.g. don’t await a ValueTask more than once.

Automatically opening connections

Calling OpenConnection every time you use a connector can get tiresome. You could move the OpenConnection call to your connector creation method, or you can use the AutoOpen setting.

DbConnector OpenConnector() => DbConnector.Create(
    new SQLiteConnection("Data Source=:memory:"),
    new DbConnectorSettings { AutoOpen = true });

If you want to wait to actually open the database connection until just before it is first used, also set the LazyOpen setting.

Using transactions

To leverage a database transaction, call BeginTransaction() before executing any commands, and then call CommitTransaction() before disposing the return value of BeginTransaction().

using (var connector = OpenConnector())
using (await connector.BeginTransactionAsync())
{
    await connector.Command(@"
create table widgets (
  id integer primary key autoincrement,
  name text not null,
  height real not null);").ExecuteAsync();

    await connector.Command(@"
insert into widgets (name, height)
  values ('First', 6.875);
insert into widgets (name, height)
  values ('Second', 3.1415);").ExecuteAsync();

    await connector.CommitTransactionAsync();
}

If CommitTransaction() is not called, the transaction will be rolled back when the return value of BeginTransaction() is disposed.

ADO.NET requres that the Transaction property of IDbCommand be set to the current transaction; DbConnector takes care of that automatically when executing commands.

Mapping database records

The querying methods of this class library (e.g. Query()) are generic, allowing the caller to specify the type to which each database record should be mapped.

For full control over the mapping, the client can specify the map parameter, which is of type Func<IDataRecord, T>. That delegate will be called for each IDataRecord instance returned by the query.

IReadOnlyList<string> GetWidgetNames(DbConnector connector) =>
    connector.Command("select name from widgets;")
        .Query(x => x.GetString(0));

The DataRecordExtensions static class provides Get<T>() extension methods on IDataRecord for mapping all or part of a record into the specified type.

IReadOnlyList<double> GetWidgetHeights(DbConnector connector) =>
    connector.Command("select name, height from widgets;")
        .Query(x => x.Get<double>(1));

Fields can also be accessed by name, though that uses IDataRecord.GetOrdinal and is thus slightly less efficient.

IReadOnlyList<double> GetWidgetHeights(DbConnector connector) =>
    connector.Command("select name, height from widgets;")
        .Query(x => x.Get<double>("height"));

Simple types

Simple types must match the type returned from IDataRecord.GetValue exactly. No conversions are performed, except between nullable and non-nullable types. Be sure to use a nullable type if the value could be null, e.g. int?; an exception will be thrown if the field is null but the type is not nullable.

The supported simple types are string, long, int, short, byte, ulong, uint, ushort, sbyte, double, float, decimal, bool, Guid, DateTime, DateTimeOffset, and TimeSpan.

Use byte[] to return the bytes from a “blob” column.

Tuples

Use tuples to map multiple record fields at once. Each tuple item is read from the record in order; the tuple item names, if any, are ignored.

IReadOnlyList<(string Name, double Height)> GetWidgetInfo(DbConnector connector) =>
    connector.Command("select name, height from widgets;")
        .Query(x => x.Get<(string, double)>(index: 0, count: 2));

C# 8 range syntax can also be used:

IReadOnlyList<(string Name, double Height)> GetWidgetInfo(DbConnector connector) =>
    connector.Command("select name, height from widgets;")
        .Query(x => x.Get<(string, double)>(0..2));

If every field of the record is being mapped, the field range can be omitted altogether:

IReadOnlyList<(string Name, double Height)> GetWidgetInfo(DbConnector connector) =>
    connector.Command("select name, height from widgets;")
        .Query(x => x.Get<(string, double)>());

And since callers usually want to map every field, that is the behavior of the method if the map parameter is omitted:

IReadOnlyList<(string Name, double Height)> GetWidgetInfo(DbConnector connector) =>
    connector.Command("select name, height from widgets;")
        .Query<(string, double)>();

DTOs

If the library doesn’t recognize a type, it is assumed to be a variable-field DTO (data transfer object) type, i.e. a type with a default constructor and one or more read/write properties.

class WidgetDto
{
    public long Id { get; set; }
    public string Name { get; set; }
    public double Height { get; set; }
}

When a DTO type is used, a new instance of the DTO is created, and each record field is mapped to a DTO property whose name matches the field name, ignoring case and any underscores (so full_name would map successfully to FullName, for example). Not every property of the DTO must be used, but every mapped field must have a corresponding property.

IReadOnlyList<WidgetDto> GetWidgets(DbConnector connector) =>
    connector.Command("select id, name, height from widgets;").Query<WidgetDto>();

object/dynamic

Record fields can also be mapped to object or dynamic. If a single field is mapped to object or dynamic, the object from IDataRecord.GetValue() is returned directly.

var heights = connector.Command("select height from widgets;")
    .Query<object>(); // returns boxed doubles

If multiple fields are mapped to object or dynamic, an ExpandoObject is returned where each property corresponds to the name and value of a mapped field.

dynamic widget = connector.Command("select name, height from widgets;")
    .Query<dynamic>()[0];
string name = widget.name;

Unfortunately, object and dynamic cannot have different algorithms. The implementation cannot distinguish between them, because typeof(T) == typeof(object) either way. To avoid confusion, use object when mapping a single field and dynamic when mapping multiple fields.

Dictionary

Record fields can also be mapped to a dictionary of strings to objects, in which case each field gets a key/value pair in the dictionary. The supported dictionary types are Dictionary<string, object>, IDictionary<string, object>, IReadOnlyDictionary<string, object>, and IDictionary.

var dictionary = connector.Command("select name, height from widgets;")
    .Query<Dictionary<string, object>>();
double height = (double) dictionary["height"];

Advanced tuples

Tuples can also include variable-field types like DTOs and dynamic. (Sorry, the examples are getting weird, but these features are useful with more interesting database schemas and queries.)

IReadOnlyList<(WidgetDto Widget, long NameLength)> GetWidgetAndNumber(DbConnector connector) =>
    connector.Command("select id, height, length(name) from widgets;")
        .Query<(WidgetDto, long)>();

If the tuple has two or more variable-field types, all but the last must be terminated by a null record value whose name is null.

IReadOnlyList<(WidgetDto Widget, dynamic Etc)> GetWidgetAndDynamic(DbConnector connector) =>
    connector.Command("select id, height, null, 1 as more, 2 as data from widgets;")
        .Query<(WidgetDto, dynamic)>();

If you don’t like that strategy, you can always use an explicit map parameter. To avoid having to count fields, we use a Get<T>() overload that takes a start name and an end name to specify the range.

IReadOnlyList<(WidgetDto Widget, dynamic Etc)> GetWidgetAndDynamic2(DbConnector connector) =>
    connector.Command("select id, height, 1 as more, 2 as data from widgets;")
        .Query(x => (x.Get<WidgetDto>("id", "height"), x.Get<dynamic>("more", "data")));

Parameterized queries

When executing parameterized queries, the parameter values are specified with the DbConnector.Command() method. The simplest way to specify command parameters is via one or more string/object tuples after the command SQL.

var tallWidgets = connector.Command(
    "select id from widgets where height >= @minHeight;",
    ("minHeight", 1.0)).Query<long>();

The DbParameters class can be used to build lists of parameters. DbParameters.FromDto creates parameters from the names and values of public properties and fields, e.g. of anonymous types.

var newWidget = new WidgetDto { Name = "Third", Height = 1.414 };
connector.Command(
    "insert into widgets (name, height) values (@Name, @Height);",
    DbParameters.FromDto(newWidget)).Execute();

var tallWidgets = connector.Command(
    "select id from widgets where height >= @minHeight;",
    DbParameters.FromDto(new { minHeight = 1.0 })).Query<long>();

Single-record queries

If your query is for a single record, call QuerySingle(), which throws an exception if the query returns multiple records, or QueryFirst(), which does not check for additional records and therefore may be more efficient.

double height = connector.Command(
    "select height from widgets where name = @name;",
    ("name", "First")).QuerySingle<double>();

If your single-record query might also return no records, call QuerySingleOrDefault() or QueryFirstOrDefault(), which return default(T) if no records were found.

double? height = connector.Command(
    "select height from widgets where name = @name;",
    ("name", "First")).QueryFirstOrDefault<double?>();

Multiple result sets

If your query has multiple result sets, all of the records from all of the result sets will be read and mapped to the same type.

If you want to map each result set to its own type, call QueryMultiple() and then call Read() for each result set.

using (var sets = connector.Command(
    "select name from widgets; select height from widgets;").QueryMultiple())
{
    names = sets.Read<string>();
    heights = sets.Read<double>();
}

Unbuffered queries

The Query and Read methods read all of the records into an IReadOnlyList<T>. Reading all of the data as quickly as possible is often best for performance, but you can read the records one at a time by calling Enumerate() instead.

var averageHeight = connector.Command("select height from widgets;")
    .Enumerate<double>().Average();

Enhancing the API

To avoid defining too many method overloads, starting a command and executing a query use chained methods. Feel free to reduce typing by creating your own extension methods that match your usage of the library.

For example, this extension method can be used to execute a query with parameters from a DTO in one method call:

public static IReadOnlyList<T> Query<T>(this DbConnector connector,
    string sql, object param) =>
        connector.Command(sql, DbParameters.FromDto(param))
            .Query<T>();

Use with Dapper

If you like the Dapper query API but want to use DbConnector to track the current transaction, use extension methods to call Dapper, accessing the Connection and Transaction properties as needed.

public static IEnumerable<T> Query<T>(this DbConnector connector,
    string sql, object param = null, bool buffered = true) =>
        connector.Connection
            .Query<T>(sql, param, connector.Transaction, buffered: buffered);