Funcular.Data.Orm 1.5.2

Release Notes:

🤕 1.5.2: Fixed #3 (Unmapped properties lacking [NotMapped] attribute are included in some SQL statements)

🌟 New in v1.5.0: Ternary operator support in WHERE clauses, projections, and ORDER BY clauses. See "Ternary Operator Support" heading below for details.

Funcular / Funky ORM: a speedy, lambda-powered .NET ORM designed for MSSQL

Welcome to Funcular ORM, aka FunkyORM, the micro-ORM designed for speed, simplicity, and lambda expression support.

• If you just want the NuGet package, look here at NuGet.org.
• If you just want to see how to get started, look at our code samples below.

If you are tired of ORMs that make you write raw SQL or use name/value pairs to create query predicates, Funcular ORM might be your answer; it's designed for developers who like the ability to use strongly-typed LINQ queries, and who need to get up and running fast with minimal setup. Funcular ORM offers:

• Instant Lambda Queries: No more wrestling with raw SQL to perform complex queries; use familiar C# lambda expressions to craft your SQL statements effortlessly.
• Parameterized Queries: All queries are parameterized to protect against SQL injection attacks.
• Cached Reflection: Funcular ORM caches reflection results to minimize overhead and maximize performance.
• Minimal Configuration: Forget about DbContexts, entity models, or extensive configurations. Just define your entity classes, and you're ready to query.
• Convention over Configuration: Sensible defaults for primary key naming conventions (like id, tablename_id, or TableNameId) mean less boilerplate and more productivity.
• Skip Data Annotations: Funcular ORM maps case-insensitively by default, ignoring underscores. Data annotation attributes are supported, but not required for properties that match the column name, so a FirstName property would automatically be mapped to column FirstName, First_Name, or first_name.
• Ignores unmatched properties and columns: While the [NotMapped] attribute is supported, it is not required for simple cases like properties that do not map to a database column or vice-versa.
• Performance without bulk: Outperforms many alternatives in benchmarks, offering the power you need without the bloat; in our testing, the framework was able to query, instantiate and map and populate over 10,000 rows in 44 to 59 milliseconds. Inserts performed at 3,000 to 4,000 rows per second. Updates are currently row-at-a-time, at about .5 to .6 milliseconds per row (bulk updates are an enhancement consideration).

Performance vs. Entity Framework (rows/second)

Funcular ORM is designed to be fast. In our benchmarks, it performed significantly faster than Entity Framework Core 7 in single-row write operations, and on-par with EF in read operations. Below are some sample results from our benchmarking tests, showing rows per second for various operations.

<a id="code-samples"></a>

Usage

Funcular ORM provides a lightweight micro-ORM with lambda-style queries, supporting operations like insert, update, retrieval, and advanced querying with minimal setup. Below are examples of key features, demonstrated using a Person entity class (assuming a SQL Server provider and database schema with tables for Person, Address, and PersonAddress).

Setup

Initialize the ORM provider with a connection string. You can also configure logging for SQL statements.

using Funcular.Data.Orm.SqlServer;

var connectionString = "Data Source=localhost;Initial Catalog=funky_db;Integrated Security=SSPI;TrustServerCertificate=true;";
var provider = new SqlServerOrmDataProvider(connectionString)
{
    Log = s => Console.WriteLine(s)  // Optional: Log generated SQL
};

Inserting Entities

Insert a new entity into the database. The ORM handles identity fields implicitly via conventions (name = id or table_id or TableId)_ or annotations ("[Key]").

var person = new Person
{
    FirstName = "John",
    LastName = "Doe",
    Birthdate = DateTime.Today.AddYears(-30),
    Gender = "Male",
    UniqueId = Guid.NewGuid(),
    DateUtcCreated = DateTime.UtcNow,
    DateUtcModified = DateTime.UtcNow
};
provider.Insert(person);
// person.Id is now populated with the identity value

For related entities, insert separately and link via junction tables.

var address = new Address
{
    Line1 = "123 Main St",
    City = "Springfield",
    StateCode = "IL",
    PostalCode = "62704"
};
provider.Insert(address);

var link = new PersonAddress { PersonId = person.Id, AddressId = address.Id };
provider.Insert(link);

Retrieving Entities

Retrieve a single entity by its primary key.

var retrievedPerson = provider.Get<Person>(person.Id);
Console.WriteLine(retrievedPerson.FirstName);  // Outputs: John

Retrieve a list of all entities of a type.

var links = provider.GetList<PersonAddress>().ToList();
// links contains all PersonAddress records

Updating Entities

Update an existing entity after modifying its properties.

var personToUpdate = provider.Get<Person>(person.Id);
personToUpdate.FirstName = "Updated John";
provider.Update(personToUpdate);

var updatedPerson = provider.Get<Person>(person.Id);
Console.WriteLine(updatedPerson.FirstName);  // Outputs: Updated John

Querying with Where Clauses

Use lambda expressions for filtering.

var filteredPersons = provider.Query<Person>()
    .Where(p => p.LastName == "Doe" && p.Gender == "Male")
    .ToList();
// Returns persons matching the criteria

Handle null checks.

var nullBirthdatePersons = provider.Query<Person>()
    .Where(p => p.Birthdate == null)
    .ToList();

Handle deletes (must be in a transaction).

provider.BeginTransaction();
// returns the number of rows deleted:
int deleted = _provider.Delete<Person>(x => x.Id == 123);
provider.RollbackTransaction();
// or provider.CommitTransaction();

String Operations in Queries

Support for StartsWith, EndsWith, and Contains on string properties.

var startsWithResults = provider.Query<Person>()
    .Where(p => p.LastName.StartsWith("Do"))
    .ToList();

var endsWithResults = provider.Query<Person>()
    .Where(p => p.LastName.EndsWith("e"))
    .ToList();

var containsResults = provider.Query<Person>()
    .Where(p => p.LastName.Contains("oh"))
    .ToList();

Collection Operations in Queries

Use Contains for filtering against lists or arrays.

var lastNames = new[] { "Doe", "Smith" };
var personsInList = provider.Query<Person>()
    .Where(p => lastNames.Contains(p.LastName))
    .ToList();

For GUIDs:

var guids = new[] { Guid.NewGuid(), Guid.NewGuid() };
var personsWithGuids = provider.Query<Person>()
    .Where(p => guids.Contains(p.UniqueId))
    .ToList();

DateTime Operations in Queries

Filter by date ranges, OR conditions, or nulls.

var fromDate = DateTime.Today.AddYears(-35);
var toDate = DateTime.Today.AddYears(-20);
var personsInRange = provider.Query<Person>()
    .Where(p => p.Birthdate >= fromDate && p.Birthdate <= toDate)
    .ToList();

OR conditions:

var oldDate = DateTime.Today.AddYears(-100);
var futureDate = DateTime.Today.AddYears(100);
var extremeDates = provider.Query<Person>()
    .Where(p => p.Birthdate <= oldDate || p.Birthdate >= futureDate)
    .ToList();

Ordering Results

Order by one or more properties, ascending or descending.

var orderedPersons = provider.Query<Person>()
    .OrderBy(p => p.LastName)
    .ToList();

Descending:

var descendingPersons = provider.Query<Person>()
    .OrderByDescending(p => p.Birthdate)
    .ToList();

Chained ordering with ThenBy or ThenByDescending:

var multiOrdered = provider.Query<Person>()
    .OrderBy(p => p.LastName)
    .ThenBy(p => p.FirstName)
    .ToList();

var multiDescending = provider.Query<Person>()
    .OrderBy(p => p.LastName)
    .ThenByDescending(p => p.MiddleInitial)
    .ToList();

Ternary Operator Support

Funcular ORM supports the C# ternary operator (?:) in WHERE clauses, projections (Select), and ORDER BY clauses, translating them to SQL CASE statements for efficient server-side evaluation. This allows conditional logic to be pushed to the database. Supports chained ternaries. Note that inner expressions within ternaries are not evaluated in C#; the entire expression is translated to SQL.

Use ternary in WHERE clauses for conditional filtering:

var adults = provider.Query<Person>()
    .Where(p => (p.Birthdate!.Value.Year < 2000 ? "Adult" : "Minor") == "Adult")
    .ToList();
// Filters persons classified as "Adult" based on birth year

Use ternary in projections to compute derived properties:

var cutoff = DateTime.Now.AddYears(-21).Date;
var results = provider.Query<Person>()
    .Select(p => new Person
    {
        Id = p.Id,
        FirstName = p.FirstName,
        LastName = p.LastName,
        Birthdate = p.Birthdate,
        IsTwentyOneOrOver = p.Birthdate.HasValue && p.Birthdate.Value <= cutoff ? true : false
    })
    .ToList();
// Computes IsTwentyOneOrOver based on birthdate

Chained ternaries in projections:

var results = provider.Query<Person>()
    .Select(p => new Person
    {
        Id = p.Id,
        FirstName = p.FirstName,
        Salutation = p.FirstName == "Fred" ? "Mr." :
                p.FirstName == "Lisa" ? "Ms." :
                p.FirstName == "Maude" ? "Mrs." :
                null
    })
    .ToList();
// Assigns salutation based on first name using chained ternaries

Use ternary in ORDER BY clauses:

var result = provider.Query<Person>()
    .OrderBy(p => p.Gender == "Male" ? p.LastName : p.FirstName)
    .ToList();
// Orders by LastName for males, FirstName for others

Descending order with ternary:

var result = provider.Query<Person>()
    .OrderByDescending(p => p.Gender == "Male" ? p.LastName : p.FirstName)
    .ToList();
// Orders descending by LastName for males, FirstName for others

Paging Results

Use Skip and Take for pagination.

var pagedResults = provider.Query<Person>()
    .OrderBy(p => p.LastName)
    .Skip(5)
    .Take(10)
    .ToList();
// Skips first 5, takes next 10

Aggregate Functions

Important: Aggregate functions like Count, Max, Min, Any, and All must start with a predicate-less query, or the query will be executed and the aggregate operation will occur in .NET rather than SQL.

Performance Note: Immediate vs. Deferred Execution

Funcular ORM provides two ways to query data, each with different execution behaviors that significantly impact performance, especially for aggregates:

• Query<T>(predicate): Executes immediately, loading all matching entities into memory before any further operations. Use this for small result sets or when you need the full collection right away.
• Query<T>(): Returns an IQueryable<T> for deferred execution. Chain LINQ operations (e.g., .Where(predicate), .Count(predicate)) and the query is translated to SQL only when enumerated. This allows SQL Server to optimize aggregates and filtering, avoiding unnecessary data transfer.

For aggregates, always prefer the deferred approach to leverage SQL Server's efficiency:

// Efficient: Aggregate handled by SQL Server
var count = provider.Query<Person>()
    .Count(p => p.Gender == "Male");

// Less efficient: Loads all matching records first, then counts in .NET
var count = provider.Query<Person>(p => p.Gender == "Male")
    .Count();

Count matching records.

// notice no predicate in the Query() call:
var count = provider.Query<Person>()
    .Count(p => p.Gender == "Male");
// Returns the number of male persons

Max and Min on properties.

// notice no predicate in the Query() call:
var maxId = provider.Query<Person>()
    .Max(p => p.Id);

var minBirthdate = provider.Query<Person>()
    .Min(p => p.Birthdate);

Check existence with Any.

// notice no predicate in the Query() call:
var hasMales = provider.Query<Person>().Any(p => p.Gender == "Male");

Check all match a condition with All.

// notice no predicate in the Query() call:
var allHaveLastName = provider.Query<Person>()
    .Where(p => p.FirstName == "John")
    .All(p => p.LastName == "Doe");

Transactions

Manage atomic operations with transactions.

Commit example:

provider.BeginTransaction();

var transactionalPerson = new Person { /* properties */ };
provider.Insert(transactionalPerson);

// More operations...

provider.CommitTransaction();
// Changes are persisted

Rollback example:

provider.BeginTransaction();

var tempPerson = new Person { /* properties */ };
provider.Insert(tempPerson);

provider.RollbackTransaction();
// Changes are discarded

Multiple operations in a transaction:

provider.BeginTransaction();

var personInTx = new Person { /* properties */ };
provider.Insert(personInTx);

var addressInTx = new Address { /* properties */ };
provider.Insert(addressInTx);

var linkInTx = new PersonAddress { PersonId = personInTx.Id, AddressId = addressInTx.Id };
provider.Insert(linkInTx);

provider.CommitTransaction();

Details

FunkyORM is designed to be a low-impedance, no-frills interface to MSSQL. It is a micro-ORM designed to fill a niche between heavy solutions like EntityFramework and other micro-ORMs like Dapper. FunkyORM features what we think is a more natural query syntax: Lambda expressions. It supports the most commonly used types and operators used in query criteria.

FunkyORM requires little configuration. Most of its behaviors can be achieved using bare entities with few or no annotations. No contexts and no entity models are needed. Annotations are supported where needs diverge from the most common use cases; just write or generate entities for your tables and start querying.

Key attributes:

• Fast: Competitive benchmarks when compared to similar alternatives
• Small footprint: completely agnostic to DbContexts, models, joins, and relationships
• Easy to use: Support lambda queries out of the box
• Usability over complexity:
  • Implements sensible defaults for common PK naming conventions (e.g., any of id, tablename_id, TableNameId are detected automatically)
  • Supports [key] attribute for cases where primary key column names diverge from these conventions
  • Auto maps matching column names by default, /ignoring case and underscores/
  • Ignores properties/columns not present in both source table/view and target entity by default
• Easily customized: Supports System.ComponentModel.DataAnnotations attributes like [Table], [Column], [Key], [NotMapped] Our goal is to make it easy for developers to get up and running quickly doing what they do 80% of the time, while making it hard for them to shoot themselves in the foot; we avoid automating complex behaviors that should really be thought through more thoroughly, like joins, inclusions, recursions, etc.

Features

FunkyORM is designed to be a near-drop-in replacement for Entity Framework that is as dependency-free as possible.

What It Does:

• GET command (by id / PK)
• SELECT queries
  • Lambdas, with operators IS NULL , IS NOT NULL , = , <> , > , >= , < , <= , LIKE , AND , OR , IN
  • C# .StartsWith, EndsWith and Contains invocations on strings
  • C# .Contains invocations on arrays (converts these to IN clauses with a SqlParameter for each member of the IN set)
  • C# OrderBy, ThenBy, OrderByDescending, ThenByDescending, Skip, Take
  • C# ternary operator (?:) support in WHERE clauses, projections, and ORDER BY clauses, translating to SQL CASE statements. Supports chained ternaries. Note that inner expressions within ternaries are not evaluated in C#; the entire expression is translated to SQL.
  • C# Any (with an optional predicate), All (predicate is required)
  • C# Aggregates like Count, Average, Min, Max on single column expressions
• UPDATE commands
  • By id / PK
  • By WHERE clause
• INSERT commands
• Observes several System.ComponentModel.DataAnnotations.Schema annotations
  • Table
  • Column
  • Key
  • NotMapped
  • DatabaseGenerated
• DELETE commands* (Note: deletes can only be performed within a transaction, and they require a valid WHERE clause.)

What It Does Not Do

• Bulk inserts
• Joins / relationships / foreign-keys / descendants
• Execute query criteria that don't translate to SQL (see the supported operators above)
• Query on columns without corresponding entity properties
• Query on derived expressions or calculated properties (e.g., you can't use expressions like order.UnitPrice * order.Quantity > 100)

Funky is made for developers who don't want a bunch of ceremony, and who prefer to do their own relational queries, i.e., get a collection of Customers, project their ids to an array, then get children: var customerOrders = Orders.Where(x => customerIds.Contains(x.CustomerId)), instead of letting EntityFramework set you up for an ‘N+1 selects’ problem or inefficient joins.

We made FunkyORM to use ourselves, and we enjoy using it. We hope you do too!

Quickstart

The easiest way to get started with FunkyORM is to execute the provided scripts to create and populate the integration test database ([funky_db]). Everything needed to do this is provided in the solution. The test project already contains entities and business objects to demonstrate the basic features of Funky.

Walkthrough - trying the unit tests is the fastest way to get started:

• Clone the repository to your local machine.
• Connect to a SQL Server you control.
• Create the SQL database: Execute the included integration_test_db script to create the database.
• Generate mock data: Execute the provided integration_test_data script to populate the database with mock data.
  • If you created the database as funky_db on the default SQL instance on localhost and can connect via SSPI/integrated security, you're good to go and you should be able to run the integration tests.
  • If the database is in any other location (different server, SQL instance, database name, etc.), you can edit the connection string in the unit tests, or create and set an environment variable, FUNKY_CONNECTION, to point to the server/database you created. The unit tests should recognize this, and it will help prevent you from accidentally checking in SQL credentials.
• Set your connection string: You can edit this in the unit test initialization, or set an environment variable, FUNKY_CONNECTION, which your environment should pick up automatically (may require a restart of VS to refresh environment variables).
• Ensure you have either TrustServerCertificate = true or Encrypt = false in the connection string; see "Important" note below for explanation
• Run the unit tests ...and boom! There you go. 💥

IMPORTANT: FunkyORM uses Microsoft.Data.SqlServer, which superseded System.Data.SqlServer as of .NET Core 3.0. This introduced a breaking change with connection strings; if your server does not have a CI-trusted certificate installed, you must include either TrustServerCertificate = true or Encrypt = false in the connection string, or the connection will fail. See https://stackoverflow.com/questions/17615260/the-certificate-chain-was-issued-by-an-authority-that-is-not-trusted-when-conn for more info on this.

Next steps:

• Review the contents of the integration tests
• Write a simple lambda-based query just like you would with Entity Framework