Breezy.SourceGenerator 1.0.0

Breezy is a lightweight Object-Relational Mapping (ORM) library for mapping objects using Source Generator in C#. It provides seamless asynchronous operations for enhanced performance.

Installation

To install Breezy, simply add the package reference to your project using NuGet Package Manager or by adding the following line to your .csproj file:

<ItemGroup>
<PackageReference Include="Breezy" OutputItemType="Analyzer" ReferenceOutputAssembly="false" />
</ItemGroup>

Getting Started

Breezy simplifies the mapping of objects and performing database operations. Here's a simple example of querying houses using Breezy's asynchronous operations :

public static async Task<IEnumerable<House>> QueryAsync<T>(this DbConnection connection, string sql, object param, ICacheableQuery<House> cacheableQuery, CancellationToken cancellationToken = default) where T : House

using Breezy;

var houses = await connection.QueryAsync<House>("SELECT * FROM house");

In the above example, the QueryAsync method executes the provided SQL query and maps the results to a list of House objects asynchronously.

Mapping Objects with Relations (N to N || 1 to N)

Breezy supports mapping objects with relationships. Here's an example of querying posts with tags using Breezy's asynchronous operations :

using Breezy;

var posts = await connection.QueryAsync<Post>(
    @"SELECT * FROM test.post p INNER JOIN posts_tags pt ON p.id = pt.post_id INNER JOIN tag t ON t.id = pt.tag_id");

The QueryAsync method executes the provided SQL query and maps the results to a list of Post objects. The Post class is defined as follows :

[Table("post")]
[SplitOn(3, 4)]
public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Body { get; set; }
    public List<Tag> Tags { get; set; } = new();
}

[Table("tag")]
public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Post> Posts { get; set; } = new();
}

In the Post class, the Table attribute specifies the table name, and the SplitOn attribute indicates the column indices to split when mapping the object from the database.

Circular reference doesn't throw exception !

Vs Dapper

var sql = @"SELECT p.id, p.title, p.body, t.id, t.name
                FROM post p 
                INNER JOIN posts_tags pt ON pt.post_id = p.id
                INNER JOIN tag t ON t.id = pt.tag_id";
				
    var posts = await connection.QueryAsync<Post, Tag, Post>(sql, (post, tag) => {      
        post.Tags.Add(tag);
        return post;
    }, splitOn: "id");
	
    var result = posts.GroupBy(p => p.PostId).Select(g =>
    {
        var groupedPost = g.First();
        groupedPost.Tags = g.Select(p => p.Tags.Single()).ToList();
        return groupedPost;
    });
    
   // Dapper is less user friendly for theses using case

Mapping Objects with Reference Type(s)

public class UserReference
{
    public int Id { get; set; }
    public Position Position { get; set; }
}

public sealed class Position
{
    public string ZipCode { get; set; }
    public string City { get; set; }
    public string Address { get; set; }
}

var users = await connection.QueryAsync<UserReference>("SELECT u.id, u.zip_code, u.city, u.address FROM user_ref u");

The QueryAsync method executes the SQL query and automatically maps the result columns to the corresponding properties of the UserReference entity, including the reference type Position.

Querying with Anonymous Types

Breezy allows you to query using anonymous types as parameters. Here's an example :

var houses = await connection.QueryAsync<House>("SELECT * FROM house h WHERE h.id = @Id", new {Id = 1});

The anonymous type is used to pass the Id parameter.

*IMPORTANT 😗 Make sure that the column index in the SQL query match the property index in any class for the mapping to work correctly. You need to add any relations at the end of you main object !

Caching for Performance Optimization

Breezy supports implementing caching mechanisms, such as in-memory or distributed caching, to reduce the memory footprint and improve query execution time. You can implement your own caching strategy based on your specific requirements.

public interface ICacheableQuery<T> where T : class
{
	public Task<IEnumerable<T>> GetCacheableResultsAsync(IdentityQuery identityQuery);
	
	public Task SetCacheableResultsAsync(IdentityQuery identityQuery, IEnumerable<T> results);
}	

// Check if the query result is already cached

var identityQuery = new IdentityQuery(sql);

var cacheableResults = await cacheableQuery.GetCacheableResultsAsync(identityQuery);

if (cacheableResults.Any())
    return cacheableResults;
    
// Execute the query    

var results = new List<T>();

while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false)) 
{ 
    // processing...
}

// Cache the query result for X ms/s

await cacheableQuery.SetCacheableResultsAsync(identityQuery, results);

Execute a Command that return result

Breezy provides the ExecuteAsync method for executing SQL statements that can return results. Here's an example of using ExecuteAsync to insert data into a table and retrieve the last inserted ID:

public static async Task<int> ExecuteAsync(this DbConnection connection, string sql, object param, CancellationToken cancellationToken = default)

var lastId = await connection.ExecuteAsync("INSERT INTO myTable (x, y) VALUES (x, y); SELECT LAST_INSERT_ID();");

Execute a Command that return results with Transaction

public static async Task<int[]> ExecuteAsync(this DbConnection connection, string[] sql, DbTransaction transaction, CancellationToken cancellationToken = default)

var dbTransaction = await _mySqlConnection.BeginTransactionAsync();

var results = await connection.ExecuteAsync(new [] { "INSERT INTO myTable (x, y) VALUES (x, y); SELECT LAST_INSERT_ID();" }, { /* ... */ }, dbTransaction);

Performance ~ 10k rows

BenchmarkDotNet=v0.13.5, OS=Windows 10 (10.0.19044.2965/21H2/November2021Update)
AMD Ryzen 5 3500X, 1 CPU, 6 logical and 6 physical cores
.NET SDK=8.0.100-preview.2.23157.25
[Host]     : .NET 7.0.5 (7.0.523.17405), X64 RyuJIT AVX2
DefaultJob : .NET 7.0.5 (7.0.523.17405), X64 RyuJIT AVX2

Why Breezy ?

I wanted to offer similary fonctionalities faster than Dapper with source generator