MilliOrm.PostgreSql 1.0.0

MilliOrm.PostgreSql – Lightweight ORM for PostgreSql

MilliOrm.PostgreSql is a lightweight, fast ORM for PostgreSql designed for developers who want simplicity, performance, and clean mapping without the heavy abstractions of Entity Framework.
It provides LINQ‑style filtering, attribute‑based entity mapping, dependency‑injection support, bulk operations, and raw SQL execution — all in a minimal and intuitive API.

Features

• Built for PostgreSql
• Fast – optimized SQL generation with minimal overhead
• Simple – small API surface, easy to learn
• Optional attribute-based mapping – [PrimaryKey], [AutoIncrement], [TableName]
• Bulk operations – high‑performance bulk insert & update
• Raw SQL support
• Built-in dependency injection
• LINQ expression filters

Installation

dotnet add package MilliOrm.PostgreSql

Registering MilliOrm.PostgreSql (Dependency Injection)

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddMilliOrm(
    builder.Configuration.GetConnectionString("DefaultConnection"),
    pluralizeTableNames: false
);

This registers IDatabase with scoped lifetime and automatically configures the ORM.

Usage in class:

public class CustomerService
{
    private readonly IDatabase _db;

    public CustomerService(IDatabase db)
    {
        _db = db;
    }
}

Entity Mapping

[TableName("dbo.Customers")]
public class Customer
{
    [PrimaryKey]
    [AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedAt { get; set; }
}

Supported attributes

Supported expressions

• Logical operators such as &&, ||, !
• Greater than, less than, equal to: >, <, >=, <=, ==, !=
• Null handling 'x == null' and 'x != null'
• Auto-expanded bool check (e.g. c => c.IsActive becomes c.IsActive = true)
• DateTime comparisons
• String methods: StartsWith, EndsWith, Contains, Equals
• Contains methods for arrays and lists: list.Contains(x.Property), works for lists, arrays, enumerables of primitives

Note: for date time comparissons initialize datetime value outside of the expression, that is use:

var cutoff = DateTime.UtcNow.AddDays(-7);
var recent = await db.GetAsync<Customer>(c => c.CreatedAt >= cutoff);

instead of:

var recent = await db.GetAsync<Customer>(c => c.CreatedAt >= DateTime.UtcNow.AddDays(-7));

NOT Supported expressions

• Any method outside string or collection .Contains
• Mathematical expressions
• Indexers
• MemberInit / New expressions
• Navigation properties (ORM is not relational)

API Usage Examples

Querying Data

var customers = await db.GetAsync<Customer>();

var active = await db.GetAsync<Customer>(
    c => c.IsActive,
    orderBy: c => c.Name
);

var first100 = await db.GetAsync<Customer>(
    c => c.IsActive,
    top: 100,
    orderBy: c => c.CreatedAt,
    ascending: true
);

var first100WithSkip = await db.GetAsync<Customer>(
    c => c.IsActive,
    top: 100,
    orderBy: c => c.CreatedAt,
    ascending: true,
    skip: 100
);

var first = await db.GetFirstAsync<Customer>(c => c.Id > 0);

More complex example:

var currentDay = DateTime.Now;
var employees = await _database
    .GetAsync<Employee>(where: x => x.Name.Contains("Joe") && !x.IsDeleted &&
                                    (x.EmploymentEnd > currentDay || x.EmploymentEnd == null),
                        top: 10,
                        orderBy: x => x.Id
                        );

Generated SQL:

SELECT *
FROM "Employee"
WHERE (( "Name" ILIKE '%' || 'Joe' || '%' AND NOT ("IsDeleted" = 1) ) AND 
        ( "EmploymentEnd" > DATE '2025-12-02' OR "EmploymentEnd" IS NULL )
)
ORDER BY "Id" ASC
LIMIT 10;

Insert Data

var customer = new Customer {
    Name = "John",
    CreatedAt = DateTime.UtcNow,
    IsActive = true
};

var id = await db.AddAsync(customer);

Insert multiple (up to 1000 rows):

await db.AddAsync(customers);

Bulk insert:

await db.BulkAddAsync(customers);

Update Data

await db.UpdateAsync(customer);

Update multiple (up to 1000 rows):

await db.UpdateAsync(customers);

Bulk update:

await db.BulkAddAsync(customers);

Update a single column for many rows:

await db.UpdateAsync<Customer>(
    c => c.IsActive, false,
    c => c.CreatedAt < DateTime.UtcNow.AddYears(-1)
);

Delete Data

await db.DeleteAsync<Customer>(c => !c.IsActive);

Raw SQL Support

await db.ExecuteRawCommandAsync("DELETE FROM dbo.Customers WHERE Id = 1");

var list = await db.ExecuteRawQueryAsync<Customer>(
    "SELECT * FROM dbo.Customers"
);

var ids = await db.ExecuteRawQueryScalarAsync<int>(
    "SELECT Id FROM dbo.Customers"
);

Transaction support

MilliOrm.PostgreSql supports native .Net transactions. Example usage:

try
{
    using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        var Id = await _database.AddAsync(employee);

        var employeeSkill = new EmployeeSkill()
        {
            EmployeeId = Id,
            SkillName = "C# Programming",
            ProficiencyLevel = "Advanced"
        };

        await _database.AddAsync(employeeSkill);
                    
        scope.Complete();
    }
}
catch(Exception e)
{
    //Rollback is automatic if Complete is not called
}

List of all available methods

Task<List<T>> GetAsync<T>(Expression<Func<T, bool>> where = null, int? top = null, Expression<Func<T, object>> orderBy = null, bool ascending = true, int? skip = null) where T : new();
Task<T> GetFirstAsync<T>(Expression<Func<T, bool>> where = null) where T : new();
Task AddAsync<T>(List<T> items);
Task<int?> AddAsync<T>(T item);
Task<int> DeleteAsync<T>(Expression<Func<T, bool>> where = null);
Task UpdateAsync<T>(List<T> items);
Task UpdateAsync<T>(T item);
Task<int> UpdateAsync<T>(Expression<Func<T, object>> column, object value, Expression<Func<T, bool>> where = null);
Task BulkAddAsync<T>(List<T> items);
Task BulkUpdateAsync<T>(List<T> items);
Task<int> ExecuteRawCommandAsync(string sql);
Task<List<T>> ExecuteRawQueryAsync<T>(string sql) where T : new();
Task<List<T>> ExecuteRawQueryScalarAsync<T>(string sql);

Benchmarks

CPU: AMD Ryzen 7 5700U
.NET 8.0 — BenchmarkDotNet v0.15.6
Note that results are based on Dapper Plus and EF Core Bulk Extensions libraries for bulk operation. Bulk operations are included by default in MilliOrm.PostgreSql library.

MilliOrm.PostgreSql performs close to Dapper and significantly faster than EF Core for large inserts and large result sets.

When to Use MilliOrm.PostgreSql

Use MilliOrm.PostgreSql if you want:

• You use PostgreSql as your database
• Lightweight library
• Minimal abstractions
• High performance
• Easier debugging than EF Core
• Simpler mental model compared to ORMs with change tracking

License

MIT License

Support

If you like the project, star it on GitHub and contribute!