C#/.NET data access

There are many ways to use relational databases from C#/.NET. Here's five of them.

DbCommand

Open database connection, execute command. Simple.
DbCommand has a couple of methods to execute db commands.

ExecuteReader/ExecuteReaderAsync to read rows

public async IAsyncEnumerable<Product> GetProducts()
{
    using var conn = new SqlConnection(connectionString);
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM Product";
    await conn.OpenAsync();
    using var reader = await cmd.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        yield return new Product
        {
            Id = reader.GetInt32(reader.GetOrdinal("Id")),
            Name = reader.GetString(reader.GetOrdinal("Name")),
            Price = reader.GetDecimal(reader.GetOrdinal("Price"))
        };
    }
}

ExecuteNonQuery/ExecuteNonQueryAsync to execute commands without result data

public async Task UpdateProduct(int id, string name, decimal price)
{
    using var conn = new SqlConnection(connectionString);
    using var cmd = conn.CreateCommand();
    cmd.CommandText = @"
    UPDATE Product SET
        Name=@Name,
        Price=@Price
    WHERE Id=@Id
    ";
    cmd.Parameters.AddWithValue("Id", id);
    cmd.Parameters.AddWithValue("Name", name);
    cmd.Parameters.AddWithValue("Price", price);
    await conn.OpenAsync();
    await cmd.ExecuteNonQueryAsync();
}

ExecuteScalar/ExecuteScalarAsync to get a single value

public async Task<int> CreateProduct(string name, decimal price)
{
    using var conn = new SqlConnection(connectionString);
    using var cmd = conn.CreateCommand();
    cmd.CommandText = @"
    INSERT INTO Product(Name,Price) 
    OUTPUT INSERTED.Id 
    VALUES (@Name,@Price)
    ";
    cmd.Parameters.AddWithValue("Name", name);
    cmd.Parameters.AddWithValue("Price", price);
    await conn.OpenAsync();
    return (int)(await cmd.ExecuteScalarAsync());
}

I think DbCommand is too low level with boring manual work. Adding Dapper or RepoDb takes no time really and doesn't reduce performance either.

Dapper

Dapper helps to reduce the manual repetitive work needed with DbCommands. Dapper can materialize rows to objects and also create parameters from objects. Dapper has methods similar to DbCommand.

Query/QueryAsync (also QueryFirst, QuerySingle etc.) to query row(s) and transforming them into instances of given type.

public async Task<IEnumerable<Product>> GetProducts()
{
    using var conn = new SqlConnection(connectionString);
    var commandText = "SELECT * FROM Product";

    // Properties are populated from columns with the same names
    return await conn.QueryAsync<Product>(commandText);
}

Execute/ExecuteAsync to execute commands without result data

public async Task UpdateProduct(int id, string name, decimal price)
{
    using var conn = new SqlConnection(connectionString);
    var commandText = @"
    UPDATE Product SET
        Name=@name,
        Price=@price
    WHERE Id=@id
    ";

    // property names are used as parameter names
    await conn.ExecuteAsync(commandText, new { id, name, price });
}

ExecuteScalar/ExecuteScalarAsync to get a single value

public async Task<int> CreateProduct(string name, decimal price)
{
    using var conn = new SqlConnection(connectionString);
    var commandText = @"
    INSERT INTO Product(Name,Price) 
    OUTPUT INSERTED.Id 
    VALUES (@name, @price)
    ";

    return await conn.ExecuteScalarAsync<int>(commandText, new { name, price });
}

Parameters can also be collection. Then the command is executed for each item (individually, not batched)

public async Task BulkInsert(IEnumerable<Product> products)
{
    using var conn = new SqlConnection(connectionString);
    var commandText = @"
    INSERT INTO Product(Id, Name,Price) 
    VALUES (@Id, @Name, @Price)
    ";
    await conn.ExecuteAsync(commandText, products);
}

QueryMultiple/QueryMultipleAsync can be used to execute queries with multiple result sets

public async Task<Product> GetProductWithReviews(int id)
{
    using var conn = new SqlConnection(connectionString);
    var commandText = @"
    SELECT * FROM Product WHERE Id=@id;
    SELECT * FROM Review WHERE ProductId=@id;
    ";

    using var multi = await conn.QueryMultipleAsync(commandText, new { id });
    var product = multi.ReadSingle<Product>();
    product.Reviews = multi.Read<Review>().ToList();

    return product;
}

Dapper also supports splitting rows to multiple objects. E.g. the second Id column starts category data which is then assigned to Category property.

public async Task<IEnumerable<Product>> GetProductsWithCategories(int id)
{
    using var conn = new SqlConnection(connectionString);
    var commandText = @"
    SELECT * FROM Product
    JOIN Category ON Category.Id = Product.CategoryId
    ";

    return conn.Query<Product, Category, Product>(commandText, (product, category) => 
    { 
        product.Category = category;
        return product;
    });
}

RepoDb

RepoDb is yet another step up from Dapper. It removes the necessity to write SQL statements for simple crud (create, read, update, delete) commands. In addition to ExecuteQuery/ExecuteScalar/ExecuteNonQuery similar to Dapper, it also includes methods like Insert, Update and Delete (and their async versions).

Queries are formed under the hood by using class names as table names and property names as column names.

public async Task<int> CreateProduct(string name, decimal price)
{
    using var conn = new SqlConnection(connectionString);
    return await conn.InsertAsync<Product, int>(new() { Name = name, Price = price });
}
public async Task UpdateProduct(int id, string name, decimal price)
{
    var product = new Product { Id = id, Name = name, Price = price };
    using var conn = new SqlConnection(connectionString);
    await conn.UpdateAsync(product);
}
public async Task<IEnumerable<Product>> GetProducts()
{
    using var conn = new SqlConnection(connectionString);
    return await conn.QueryAllAsync<Product>();
}
public async Task DeleteProduct(int id)
{
    using var conn = new SqlConnection(connectionString);
    await conn.DeleteAsync<Product>(id);
}

It's also possible to use LINQ expressions to query data

conn.Query<Product>(p => p.CategoryId == someCategoryId & p.Price < 1234m).FirstOrDefault();

There are a lot of other features for batch operations, multiple result sets etc.

EF Core

EF Core is a full blown ORM (object-relational mapper). It adds a lot of functionality compared to libraries introduced so far. With change tracking EF Core knows which objects have changed since they were loaded from the db, which need to be inserted etc. A single SaveChanges/SaveChangesAsync is often enough to make all changes to database. Migrations help keeping db up-to-date when incremental changes are made. Models can contain relationships which makes it possible to load related data when making queries.

Entities are configured in a context

public class ExampleContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("connection string");
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Product>().ToTable("Product");
    }
}

EF Core creates sql commands automatically based on entity mappings. EF Core can handle quite complex LINQ using e.g. Where, Select, OrderBy, Skip, Take, GroupBy

public async Task<int> CreateProduct(string name, decimal price)
{
    var product = new Product { Name = name, Price = price };
    await _context.Products.AddAsync(product);
    await _context.SaveChangesAsync();
    return product.Id;
}
public async Task UpdateProduct(int id, string name, decimal price)
{
    var product = await _context.Products.FindAsync(id);
    product.Name = name;
    product.Price = price;
    await _context.SaveChangesAsync();
}
public async Task<IEnumerable<Product>> GetProducts()
{
    return await _context.Products.ToListAsync();
}
public async Task<IEnumerable<Product>> GetProductsWithCategories(decimal maxPrice, IEnumerable<int> categoryIds)
{
    return await _context.Products
        .Where(p => p.Price < maxPrice && categoryIds.Contains(p.CategoryId))
        .Include(p => p.Category)
        .OrderByDescending(p => p.Price)
        .ToListAsync();
}

EF Core has sufficient performance for simple queries, but many Includes can be a real performance killer. Some entity mappings can also be quite difficult to do when they are done against an existing database.

Marten

Libraries so far have been for relational data. Sometimes data doesn't really fit relational model. Data may be greatly varying and lead to tables having a lot of columns relevant to only small part of the rows. Or maybe data is deeply nested leading to a lot of joins and poor performance (especially with Includes in EF Core). Marten has a totally different approach. It uses json type in PostgreSQL to store objects as documents. It can create and update schemas automatically.

_store = DocumentStore.For(connectionString);

public async Task<int> CreateProduct(string name, decimal price)
{
    var product = new Product { Name = name, Price = price };
    using var session = _store.LightweightSession();
    session.Insert(product);
    await session.SaveChangesAsync();
    return product.Id;
}
public async Task UpdateProduct(int id, string name, decimal price)
{
    using var session = _store.LightweightSession();
    session.Update(new Product { Id = id, Name = name, Price = price });
    await session.SaveChangesAsync();
}
public async Task<IEnumerable<Product>> GetProducts()
{
    using var session = _store.LightweightSession();
    return await session.Query<Product>().ToListAsync();
}

Marten stores document to a single JSON column with some additional columns like the id. Additional properties can also be copied to columns for better performance.

Marten can also handle quite complex LINQ, e.g. Where, Select, OrderBy, Skip, Take.

var products = await session.Query<Product>()
    .Where(p => p.Price < maxPrice && p.CategoryId.In(categoryIds))
    .OrderByDescending(p => p.Price)
    .ToListAsync();

There are a lot of features for configuration, indexing, document transformations etc.