Funcular.Data.Orm 3.1.0-alpha1

Recent Changes

• v3.1.0: 🐘 PostgreSQL Support! FunkyORM now supports PostgreSQL via the new Funcular.Data.Orm.PostgreSql NuGet package. Full LINQ-to-SQL, remote keys/properties, transactions, and reserved word handling — everything you know from the MSSQL provider, now on Postgres. See Database Provider Differences for details.
• v3.0.1: Major Refactoring & New Features. Introduced ISqlDialect for multi-database support. Added [RemoteKey] and [RemoteProperty] attributes for easy relationship mapping. Added support for Guid and String primary keys, generic Insert<T, TKey> overloads, and non-identity key handling.

Funcular / Funky ORM: a speedy, lambda-powered .NET micro-ORM for MSSQL & PostgreSQL

For AI Agents: Please refer to COPILOT_INSTRUCTIONS.md for strict coding guidelines and "Happy Path" patterns. This file is included in both NuGet packages. A PostgreSQL-specific supplement is at COPILOT_INSTRUCTIONS.md.

Tip for Consumers: To help AI agents (Copilot, Cursor, etc.) generate correct FunkyORM code in your project, copy COPILOT_INSTRUCTIONS.md from the NuGet package to your project root or .github/ folder.

Overview

Welcome to Funcular ORM (aka FunkyORM), the micro-ORM designed for developers who want the speed of a micro-ORM with the simplicity and type safety of LINQ.

If you're tired of wrestling with raw SQL strings (Dapper) or debugging generated queries from a heavy framework (Entity Framework), FunkyORM is your sweet spot.

Why FunkyORM?

• Instant Lambda Queries: Write C# lambda expressions, get optimized SQL.
• Performance: Outperforms EF Core in single-row writes and matches it in reads. (See our Usage Guide for benchmarks).
• Zero Configuration: No DbContext, no mapping files. Just POCOs and a connection string.
• Safe: All queries are parameterized to prevent SQL injection.
• Mass Delete Prevention: Includes safeguards against accidental "delete all" operations (e.g., blocking 1=1), though this does not guarantee prevention of all crafty circumventions.
• Convention over Configuration: Sensible defaults for primary key naming conventions (like id, tablename_id, or TableNameId) mean less boilerplate and more productivity.
• Remote Keys & Properties: Flatten your object graph by mapping properties directly to columns in related tables (e.g., Person.EmployerCountryName) without writing joins. The ORM handles the graph traversal for you.
• Explicit Collection Population: Leverage RemoteKey properties to easily populate related collections without the overhead of massive object graphs or N+1 queries.
• Cached Reflection: Funcular ORM caches reflection results to minimize overhead and maximize performance.
• Nullable-Friendly: Nullable properties work seamlessly in LINQ queries—no need for .Value or .HasValue. The ORM handles the unwrapping for you.

Getting Started

1. Installation

Add the provider package for your database:

SQL Server:

dotnet add package Funcular.Data.Orm

PostgreSQL:

dotnet add package Funcular.Data.Orm.PostgreSql

2. Initialization

Create a provider instance. You can register it as a singleton in your DI container, or create it as needed.

SQL Server:

using Funcular.Data.Orm.SqlServer;

var connectionString = "Server=.;Database=MyDb;Integrated Security=True;TrustServerCertificate=True;";
var provider = new SqlServerOrmDataProvider(connectionString);

PostgreSQL:

using Funcular.Data.Orm.PostgreSql;

var connectionString = "Host=localhost;Port=5432;Database=mydb;Username=myuser;Password=mypassword";
var provider = new PostgreSqlOrmDataProvider(connectionString);

Note: Both providers implement the same base class and support the same LINQ query API, CRUD operations, remote keys/properties, and transactions. Your entity classes and query code are fully portable between providers.

3. Define Your Data Models

FunkyORM is designed to keep your code clean. You usually don't need attributes.

By default, we map the intersection of your class properties and the database table columns.

• If your class has a FullName property but the table doesn't, we ignore it. No [NotMapped] needed.
• If the table has a CreatedDate column but your class doesn't, we ignore it. No errors.

We also infer table names and primary keys automatically.

// No attributes needed!
// Maps to table 'Person' or 'PERSON' (case-insensitive)
public class Person
{
    // Automatically detected as Primary Key
    public int Id { get; set; }
    
    // Maps to column 'FirstName', 'first_name', etc.
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
    
    // Ignored automatically if no matching column exists
    public string FullName => $"{FirstName} {LastName}";
}

If you need to deviate from conventions (e.g., mapping Person class to tbl_Users), you can still use standard System.ComponentModel.DataAnnotations:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("tbl_Users")]
public class Person
{
    [Key]
    [Column("user_id")]
    public int Id { get; set; }
    // ...
}

4. Start Querying

// Insert a new record and get the ID
var newPerson = new Person { FirstName = "Jane", LastName = "Doe", Age = 25 };
var newId = provider.Insert<Person, int>(newPerson);

// Get by ID
var person = provider.Get<Person>(newId);

// Complex Querying with LINQ
var adults = provider.Query<Person>()
    .Where(p => p.Age >= 18)
    .Where(p => p.LastName.StartsWith("D"))
    .OrderByDescending(p => p.Age)
    .Take(10)
    .ToList();

5. Superpowers: Remote Keys & Properties

This is where FunkyORM shines. You can map properties in your entity to columns in related tables without writing joins or loading the entire object graph.

• [RemoteProperty]: Fetch a value from a related table (e.g., Employer.Name) directly into your Person object.
• [RemoteKey]: Fetch the ID of a related entity (e.g., Employer.CountryId) directly.

public class Person
{
    // ... standard properties ...

    // Link to the Organization table
    [RemoteLink(targetType: typeof(Organization))]
    public int? EmployerId { get; set; }

    // SUPERPOWER 1: Get the Employer's Name without loading the Organization object
    [RemoteProperty(remoteEntityType: typeof(Organization), keyPath: new[] { nameof(EmployerId), nameof(Organization.Name) })]
    public string EmployerName { get; set; }

    // SUPERPOWER 2: Get the Employer's Country ID (2 hops away!)
    // Person -> Organization -> Address -> Country
    [RemoteKey(remoteEntityType: typeof(Country), keyPath: new[] {
        nameof(EmployerId), 
        nameof(Organization.HeadquartersAddressId), 
        nameof(Address.CountryId), 
        nameof(Country.Id) })]
    public int? EmployerCountryId { get; set; }
}

// Now you can query and filter by these remote properties as if they were local!
var usEmployees = provider.Query<Person>()
    .Where(p => p.EmployerCountryId == 1) // Filters by joined table!
    .ToList();

The "Superpower" Advantage

To achieve this without FunkyORM, you'd typically have to do this:

Entity Framework Core:

// Requires loading the entire graph or creating a custom DTO
var person = context.People
    .Include(p => p.Employer)
    .ThenInclude(e => e.Address)
    .ThenInclude(a => a.Country) // Heavy!
    .FirstOrDefault();
// Access: person.Employer.Address.Country.Name

Dapper:

// Requires writing raw SQL joins and manual mapping
var sql = @"SELECT p.*, c.Name as CountryName 
            FROM Person p 
            JOIN Organization o ON p.EmployerId = o.Id ..."; // ... and so on

FunkyORM:

// Just add the attribute. We handle the joins.
// Path: Person -> Organization (via EmployerId) -> Address -> Country
[RemoteProperty(remoteEntityType: typeof(Country), keyPath: new[] {
    nameof(EmployerId),
    nameof(Organization.HeadquartersAddressId),
    nameof(Address.CountryId),
    nameof(Country.Name) })]
public string EmployerCountryName { get; set; }

Database Provider Differences

FunkyORM generates database-specific SQL through its ISqlDialect abstraction. Your entity classes and LINQ queries are portable, but the generated SQL differs to match each platform's conventions.

PostgreSQL-Specific Notes

• Naming convention: PostgreSQL is case-sensitive for quoted identifiers. Unquoted identifiers are folded to lowercase. FunkyORM quotes reserved words automatically (e.g., "User", "Order"), and leaves non-reserved names unquoted (matching PostgreSQL's lowercase convention).
• Npgsql versions: The PostgreSQL provider uses Npgsql 9.x for net8.0 and Npgsql 8.x for netstandard2.0 (last version with netstandard support).
• Timestamps: The provider sets AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true) to ensure DateTime values are handled consistently without requiring timestamptz conversions.

Documentation

For detailed usage examples, performance benchmarks, and a comparison with other ORMs, please see our Usage Guide.

Comparison: FunkyORM vs. The World