MicroOrm.Dapper.Repositories

🚀 Stop Writing SQL for Every Insert

Why manually write INSERT statements for every entity? Dapper Plus provides powerful bulk operations that save development time and simplify your code.

Simplify Your Inserts →

Description

If you like your code to run fast, you probably know about Micro ORMs. They are simple and one of their main goals is to be the fastest execution of your SQL sentences in your data repository. For some Micro ORMs you need to write your own SQL sentences and this is the case of the most popular Micro ORM Dapper.

This library abstracts the generation of SQL for CRUD operations based on each C# POCO class metadata. The SQL Generator is a generic component that builds all CRUD sentences for a POCO class based on its definition, with the possibility to override the SQL generator and the way it builds each sentence.

The library is designed to help with common, simple queries so you don't have to write repetitive SQL by hand. It does not try to cover every possible scenario - for complex queries you should write SQL directly using Dapper.

Installation

language-sh

dotnet add package MicroOrm.Dapper.Repositories

Configuration

There are two ways to configure SQL generation:

1. Global static configuration - MicroOrmConfig sets defaults for all repositories at once. Useful when your application uses a single database provider.

language-c#

MicroOrmConfig.SqlProvider = SqlProvider.PostgreSQL;
MicroOrmConfig.UseQuotationMarks = true;
MicroOrmConfig.TablePrefix = "app_";
MicroOrmConfig.AllowKeyAsIdentity = true;

2. Per-repository configuration - pass a SqlGenerator<TEntity> with specific settings to each repository constructor. Useful when you need different settings per entity or work with multiple providers.

language-c#

var sqlGenerator = new SqlGenerator<User>(SqlProvider.PostgreSQL, useQuotationMarks: true);
var repo = new DapperRepository<User>(connection, sqlGenerator);

Property	Default	Description
`SqlProvider`	`MSSQL`	SQL dialect to use
`UseQuotationMarks`	`false`	Quote table and column names in generated SQL
`TablePrefix`	`null`	String prepended to all table names (e.g. for multi-tenant scenarios)
`AllowKeyAsIdentity`	`false`	Treat `[Key]` as `[Identity]` when `[Identity]` is not explicitly set

Supported providers: MSSQL, MySQL, PostgreSQL, SQLite, Oracle

Entity Mapping

Metadata Attributes

Attribute	Description
`[Key]`	Primary key. Apply to multiple properties for composite keys
`[Identity]`	Auto-incrementing property. Excluded from INSERT, populated after insert
`[Table]`	Overrides table name. Supports `Schema` parameter
`[Column]`	Overrides column name. Supports `Order` parameter
`[NotMapped]`	Excludes property from SQL generation
`[IgnoreUpdate]`	Excludes property from UPDATE statements only
`[Deleted]`	Enables logical (soft) delete - DELETE becomes UPDATE. Works with `bool` or `enum` (apply to the enum member representing deleted state)
`[Status]`	Used together with `[Deleted]` on the status property
`[UpdatedAt]`	Auto-sets timestamp on Insert, Update and logical Delete. Defaults to `DateTime.UtcNow`. Properties: `TimeKind` (`Utc`, `Local`, `Unspecified`), `OffSet` (hours)

Join Attributes

All join attributes support both single-object and collection navigation properties. Constructor parameters: (tableName, key, externalKey) with optional tableSchema and tableAlias. [CrossJoin] takes only (tableName) since CROSS JOIN has no ON clause.

Attribute	SQL
`[LeftJoin]`	LEFT JOIN
`[InnerJoin]`	INNER JOIN
`[RightJoin]`	RIGHT JOIN
`[CrossJoin]`	CROSS JOIN

Entity Examples

language-c#

[Table("Users")]
public class User
{
    [Key, Identity]
    public int Id { get; set; }

    public string ReadOnly => "test"; // read-only properties are ignored

    public string Name { get; set; }

    public int AddressId { get; set; }

    [LeftJoin("Cars", "Id", "UserId")]
    public List<Car> Cars { get; set; }

    [LeftJoin("Addresses", "AddressId", "Id")]
    public Address Address { get; set; }

    [InnerJoin("Phones", "Id", "UserId")]
    public Phone Phone { get; set; }

    [Status, Deleted]
    public bool Deleted { get; set; }

    [UpdatedAt]
    public DateTime? UpdatedAt { get; set; }
}

Enum-based soft delete:

language-c#

[Table("Cars")]
public class Car
{
    [Key, Identity]
    public int Id { get; set; }

    public string Name { get; set; }

    public int UserId { get; set; }

    [LeftJoin("Users", "UserId", "Id")]
    public User User { get; set; }

    [Status]
    public StatusCar Status { get; set; }
}

public enum StatusCar
{
    Inactive = 0,
    Active = 1,

    [Deleted]
    Deleted = -1
}

Composite primary key:

language-c#

[Table("Reports")]
public class Report
{
    [Key]
    public int Id { get; set; }

    [Key]
    public int AnotherId { get; set; }
}

Table with schema:

language-c#

[Table("Phones", Schema = "DAB")]
public class Phone
{
    [Key, Identity]
    public int Id { get; set; }

    [IgnoreUpdate]
    public string Code { get; set; }

    public string PNumber { get; set; }

    [Status, Deleted]
    public bool? IsDeleted { get; set; }
}

Setting Up Repositories

Using DbContext

Create a context class that inherits from DapperDbContext and exposes repository properties:

language-c#

public class MyDbContext : DapperDbContext
{
    private IDapperRepository<User>? _users;
    private IDapperRepository<Car>? _cars;
    private IReadOnlyDapperRepository<Report>? _reports;

    public MyDbContext(IDbConnection connection) : base(connection) { }

    public IDapperRepository<User> Users =>
        _users ??= new DapperRepository<User>(Connection);

    public IDapperRepository<Car> Cars =>
        _cars ??= new DapperRepository<Car>(Connection);

    // Use IReadOnlyDapperRepository for read-only access
    public IReadOnlyDapperRepository<Report> Reports =>
        _reports ??= new ReadOnlyDapperRepository<Report>(Connection);
}

With a custom SQL generator (to specify provider or quotation marks per-repository):

language-c#

public IDapperRepository<User> Users =>
    _users ??= new DapperRepository<User>(Connection, new SqlGenerator<User>(SqlProvider.PostgreSQL, true));

Standalone Repository

language-c#

var connection = new SqlConnection(connectionString);
var userRepo = new DapperRepository<User>(connection);

Or with a custom repository class:

language-c#

public class UserRepository : DapperRepository<User>
{
    public UserRepository(IDbConnection connection, ISqlGenerator<User> sqlGenerator)
        : base(connection, sqlGenerator)
    {
    }
}

🚀 Stop Writing SQL for Every Insert

Why manually write INSERT statements for every entity? Dapper Plus provides powerful bulk operations that save development time and simplify your code.

Simplify Your Inserts →

CRUD Operations

All operations have both synchronous and async overloads. Async methods accept an optional CancellationToken. Most methods accept an optional IDbTransaction.

Find

language-c#

// Find by predicate (returns first match or null)
var user = await repo.FindAsync(x => x.Id == 5);

// Find without predicate (returns first record)
var first = await repo.FindAsync();

// FindById
var user = await repo.FindByIdAsync(5);

FindAll

language-c#

// All records
var users = await repo.FindAllAsync();

// With WHERE clause
var activeUsers = await repo.FindAllAsync(x => x.Name == "John");

// With multiple conditions
var users = await repo.FindAllAsync(x => x.AddressId == 1 && !x.Deleted);

Supported LINQ expressions in predicates:

Equality: x => x.Name == "John"
Comparison: x => x.Id > 5
Contains (IN clause): x => idList.Contains(x.Id)
String methods: x => x.Name.StartsWith("J"), EndsWith(), Contains()
Case-insensitive string methods: x => x.Name.Contains("john", StringComparison.OrdinalIgnoreCase), also StartsWith(), EndsWith(), Equals()
Boolean: x => x.IsActive, x => !x.Deleted
Null checks: x => x.Name != null
Logical operators: &&, ||

Passing a StringComparison that ignores case (OrdinalIgnoreCase, InvariantCultureIgnoreCase, CurrentCultureIgnoreCase) makes the comparison case-insensitive. PostgreSQL uses ILIKE; other providers wrap both sides in LOWER(). Other StringComparison values keep the default behavior, so the database collation decides.

language-c#

// PostgreSQL: "Users"."Name" ILIKE @Name_p0
// Others:     LOWER(Users.Name) LIKE LOWER(@Name_p0)
var users = await repo.FindAllAsync(x => x.Name.Contains("john", StringComparison.OrdinalIgnoreCase));

FindAllBetween

language-c#

// Between with DateTime
var users = await repo.FindAllBetweenAsync(
    new DateTime(2023, 1, 1),
    new DateTime(2023, 12, 31),
    x => x.CreatedAt);

// Between with predicate
var users = await repo.FindAllBetweenAsync(
    new DateTime(2023, 1, 1),
    new DateTime(2023, 12, 31),
    x => x.CreatedAt,
    x => x.Name == "John");

Count

language-c#

// Count all
var total = await repo.CountAsync();

// Count with predicate
var count = await repo.CountAsync(x => x.Name == "John");

// Count with DISTINCT
var uniqueCount = await repo.CountAsync(x => x.AddressId);

// Count with DISTINCT and predicate
var count = await repo.CountAsync(x => x.IsActive, x => x.AddressId);

Insert

language-c#

var user = new User { Name = "John", AddressId = 1 };
bool success = await repo.InsertAsync(user);
// user.Id is populated after insert if [Identity] is used

Bulk Insert

language-c#

var users = new List<User>
{
    new() { Name = "John", AddressId = 1 },
    new() { Name = "Jane", AddressId = 2 }
};
int insertedCount = await repo.BulkInsertAsync(users);

Update

language-c#

var user = await repo.FindByIdAsync(1);
user.Name = "Updated Name";
bool success = await repo.UpdateAsync(user);

The SET clause holds every column of the entity except the [Key] and [IgnoreUpdate] ones, whether or not the value changed.

Update with predicate (WHERE clause):

language-c#

bool success = await repo.UpdateAsync(x => x.Id == 1, user);

Update Columns

UpdateColumns writes only the columns you list, plus [UpdatedAt] when the entity has one:

language-c#

// UPDATE Users SET Users.Name = @UserName, Users.UpdatedAt = @UserUpdatedAt WHERE Users.Id = @UserId
bool success = await repo.UpdateColumnsAsync(user, x => x.Name);

// Several columns
bool success = await repo.UpdateColumnsAsync(user, x => x.Name, x => x.AddressId);

// Rows matched by predicate instead of by [Key]
bool success = await repo.UpdateColumnsAsync(x => x.AddressId == 1, user, x => x.Name);

Values are read from the entity you pass. A column that isn't mapped, or that is marked [Key] or [IgnoreUpdate], throws an ArgumentException.

Bulk Update

language-c#

var users = (await repo.FindAllAsync()).ToList();
foreach (var user in users)
    user.Name = "Updated";
bool success = await repo.BulkUpdateAsync(users);

Delete

With entity instance:

language-c#

var user = await repo.FindByIdAsync(1);
bool success = await repo.DeleteAsync(user);

With predicate:

language-c#

bool success = await repo.DeleteAsync(x => x.Id == 1);

With timeout:

language-c#

bool success = await repo.DeleteAsync(x => x.Id == 1, TimeSpan.FromSeconds(30));

Note: If the entity has a [Deleted] attribute, delete operations generate an UPDATE statement (logical delete) instead of a physical DELETE.

Joins

Load related entities by specifying navigation property expressions:

language-c#

// Find with one join
var user = await repo.FindAsync<Car>(
    x => x.Id == 1,
    x => x.Cars);

// Find with multiple joins (up to 6 supported)
var user = await repo.FindAsync<Car, Address>(
    x => x.Id == 1,
    x => x.Cars,
    x => x.Address);

// FindAll with joins
var users = await repo.FindAllAsync<Car>(
    x => x.AddressId == 1,
    x => x.Cars);

// FindById with joins
var user = await repo.FindByIdAsync<Car, Address>(
    1,
    x => x.Cars,
    x => x.Address);

WHERE clauses can reference joined entity properties:

language-c#

var users = await repo.FindAllAsync(x => x.Phone.PNumber == "123");

Update with Joins

Update takes the same navigation property expressions, and writes the joined rows in the same statement:

language-c#

// UPDATE Users LEFT JOIN Addresses ON Users.AddressId = Addresses.Id SET Users.Name = ..., Addresses.Street = ...
bool success = await repo.UpdateAsync(user, x => x.Address);

The navigation property has to be set on the entity, otherwise that join is skipped. UPDATE ... JOIN ... SET ... is MySQL syntax, so every other provider throws NotSupportedException. Pass a property without a join attribute and you get an ArgumentException naming it.

Filtering and Pagination

SetLimit, SetOrderBy, SetGroupBy, and SetSelect return the repository instance for fluent chaining. By default, filter settings are cleared after each query. Use the permanent parameter to persist them across queries.

Limit and Offset

language-c#

// Limit
var users = await repo.SetLimit(10).FindAllAsync();

// Limit with offset (pagination)
var page2 = await repo.SetLimit(10, 10).FindAllAsync();

// Permanent limit (persists across queries)
repo.SetLimit(100, 0, permanent: true);

OrderBy

language-c#

// Order by expression
var users = await repo.SetOrderBy(OrderInfo.SortDirection.DESC, x => x.Name).FindAllAsync();

// Order by multiple columns
var users = await repo.SetOrderBy(OrderInfo.SortDirection.ASC, "Name", "Id").FindAllAsync();

// Order by joined table column
var users = await repo.SetOrderBy<Address>(OrderInfo.SortDirection.ASC, x => x.CityId).FindAllAsync();

// Custom order query
var users = await repo.SetOrderBy("Name ASC, Id DESC").FindAllAsync();

// Clear ordering
repo.SetOrderBy();

MSSQL note: When using MSSQL, limit with offset requires an ORDER BY clause. Otherwise the limit is ignored.

GroupBy

language-c#

var users = await repo.SetGroupBy(x => x.AddressId).FindAllAsync();

// Permanent group by
repo.SetGroupBy(permanent: true, x => x.AddressId);

Select (Column Projection)

language-c#

// Select specific columns
var users = await repo.SetSelect(x => new { x.Id, x.Name }).FindAllAsync();

// Select from joined table
repo.SetSelect<Address>(x => new { x.CityId, x.Street });

// Custom column list
repo.SetSelect("Id", "Name");

Transactions

Use DapperDbContext for transaction management:

language-c#

using var context = new MyDbContext(connection);
var transaction = context.BeginTransaction();

try
{
    var user = new User { Name = "John", AddressId = 1 };
    await context.Users.InsertAsync(user, transaction);

    var car = new Car { Name = "Tesla", UserId = user.Id };
    await context.Cars.InsertAsync(car, transaction);

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

Async transaction:

language-c#

var transaction = await context.BeginTransactionAsync();

Transactions can also be passed directly to any repository method:

language-c#

await repo.InsertAsync(entity, transaction);
await repo.UpdateAsync(entity, transaction);
await repo.DeleteAsync(entity, transaction);
await repo.FindAsync(x => x.Id == 1, transaction);

Notes

By default, the SQL Generator maps the POCO class name to the table name, and each public property with a getter and setter to a column.
Read-only properties (expression-bodied or without a setter) are automatically ignored.
If [Deleted] is used, DELETE operations generate UPDATE statements instead.
Supports composite primary keys (multiple [Key] attributes).
Supports up to 6 joined entities in a single query.
Property metadata is cached using ConcurrentDictionary for performance.
GUID primary keys do not work with Oracle due to Dapper limitations. See: Dapper#633, Dapper#637.

License

Inspired by Diego García's Dapper.DataRepositories.

All contents of this package are licensed under the MIT license.

🔄 Insert or Update with BulkMerge

Need an upsert operation? BulkMerge automatically inserts new records and updates existing ones, making data synchronization easier and faster.

Explore BulkMerge →

Date Modified: 2026-09-18
Author: ZZZ Projects

Edit this page in GitHub

Got any Dapper Question?

Ask any Dapper Questions and Get Instant Answers from ChatGPT AI:

Dapper Chat GPT Console

MicroOrm.Dapper.Repositories

Description

Installation

Configuration

Entity Mapping

Metadata Attributes

Join Attributes

Entity Examples

Setting Up Repositories

Using DbContext

Standalone Repository

CRUD Operations

Find

FindAll

FindAllBetween

Count

Insert

Bulk Insert

Update

Update Columns

Bulk Update

Delete

Joins

Update with Joins

Limit and Offset

OrderBy

GroupBy

Select (Column Projection)

Transactions

Notes

Sponsors

License

Got any Dapper Question?

Quick Links

On this page