Skip to main content

Breezy by Ludovicdln

Nuget / site data​

Nuget GitHub last commit GitHub Repo stars

Details​

Info​

info

Name: Breezy

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.

Author: Ludovicdln

NuGet: https://www.nuget.org/packages/Breezy.SourceGenerator/

You can find more details at https://github.com/Ludovicdln/Breezy

Source : https://github.com/Ludovicdln/Breezy

Original Readme​

note

NuGet Badge License: MIT

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​

Nugget Package : https://www.nuget.org/packages/Breezy.SourceGenerator/​

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.SourceGenerator" Version="1.0.1" />
</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);
Example of implementation (Memory Cache)
public sealed class MemoryCacheableQuery<T> : ICacheableQuery<T> where T : class
{
    private readonly Dictionary<IdentityQuery, Tuple<DateTime, IEnumerable<T>>> _cacheableData = new();
    
    public Task<IEnumerable<T>> GetCacheableResultsAsync(IdentityQuery identityQuery)
    {
        if (_cacheableData.TryGetValue(identityQuery, out var results))
        {
            var (addDate, collection) = results;

            if ((DateTime.Now - addDate) < TimeSpan.FromSeconds(10))
                return Task.FromResult<IEnumerable<T>>(collection);

            _cacheableData.Remove(identityQuery);
        }

        return Task.FromResult<IEnumerable<T>>(Array.Empty<T>());
    }

    public Task SetCacheableResultsAsync(IdentityQuery identityQuery, IEnumerable<T> results)
    {
        _cacheableData.Add(identityQuery, new Tuple<DateTime, IEnumerable<T>>(DateTime.Now, results));

        return Task.CompletedTask;
    }
}

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
ORMMethodReturnMeanStdDevGen0Gen1Gen2Allocated
BreezyQueryAsync<T>No relation491.1 ns4.08 ns0.0801--672 B
DapperQueryAsync<T>No relation14,005,807.3 ns85,785.13 ns437.5000265.6250125.00003899691 B
BreezyQueryFirstOrDefault<T>No relation589.8 ns7.28 ns0.0935--784 B
DapperQueryFirstOrDefault<T>No relation540,714.1 ns44,717.07 ns0.9766--13081 B
BreezyQueryAsync<T>1 To N relations588.5 ns9.26 ns0.0801--672 B
DapperQueryAsync<T>1 To N relations98,695,865.6 ns740,908.87 ns2000.0000833.3333500.000017760052 B
BreezyQueryFirstOrDefault<T>1 To N relations690.7 ns13.41 ns0.0935--784 B
DapperQueryFirstOrDefault<T>1 To N relations14,866,187.7 ns385,888.24 ns---30835 B

Why Breezy ?​

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

About​

note

ORM Mapper

How to use​

Example ( source csproj, source files )​

This is the CSharp Project that references Breezy

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net7.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
    <PropertyGroup>
        <EmitCompilerGeneratedFiles>true</EmitCompilerGeneratedFiles>
        <CompilerGeneratedFilesOutputPath>$(BaseIntermediateOutputPath)\GX</CompilerGeneratedFilesOutputPath>
    </PropertyGroup>
    <ItemGroup>
      <PackageReference Include="Breezy.SourceGenerator" Version="1.0.1"  OutputItemType="Analyzer" ReferenceOutputAssembly="false" />
      <PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.1" />
    </ItemGroup>
</Project>

Generated Files​

Those are taken from $(BaseIntermediateOutputPath)\GX

// <auto-generated /> 
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Threading.Tasks;
using System.Collections.Generic;

namespace DbConnectionExtensions
{
    public static class DbConnectionExtensions
    {
        /// <summary>
        /// Execute a command asynchronously using Task.
        /// </summary>       
        /// <param name = "sql">The SQL to execute for the query.</param>
        /// <returns>The number of rows affected.</returns>
        public static async Task<int> ExecuteAsync(this DbConnection connection, string sql, CancellationToken cancellationToken = default)
        {
            bool wasClosed = connection.State == ConnectionState.Closed;
            if (wasClosed)
                await connection.OpenAsync(cancellationToken);
            await using var command = connection.CreateCommand();
            command.CommandText = sql;
            try
            {
                return await command.ExecuteNonQueryAsync(cancellationToken);
            }
            finally
            {
                if (wasClosed)
                    connection.Close();
            }
        }

        /// <summary>
        /// Execute a command asynchronously using Task.
        /// </summary>       
        /// <param name = "sql">The SQL to execute for the query.</param>
        /// <param name = "param">The parameters to pass, if any.</param>
        /// <returns>The number of rows affected.</returns>
        public static async Task<int> ExecuteAsync(this DbConnection connection, string sql, object param, CancellationToken cancellationToken = default)
        {
            bool wasClosed = connection.State == ConnectionState.Closed;
            if (wasClosed)
                await connection.OpenAsync(cancellationToken);
            await using var command = connection.CreateCommand();
            command.CommandText = sql;
            foreach (var property in param.GetType().GetProperties())
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = "@" + property.Name;
                parameter.Value = property.GetValue(param);
                command.Parameters.Add(parameter);
            }

            try
            {
                return await command.ExecuteNonQueryAsync(cancellationToken);
            }
            finally
            {
                if (wasClosed)
                    connection.Close();
            }
        }

        /// <summary>
        /// Execute a command asynchronously using Task.
        /// </summary>       
        /// <param name = "sql">The SQL to execute for the query.</param>
        /// <param name = "transaction">The transaction to use for this query.</param>
        /// <returns>The number of rows affected.</returns>
        public static async Task<int[]> ExecuteAsync(this DbConnection connection, string[] sql, DbTransaction transaction, CancellationToken cancellationToken = default)
        {
            bool wasClosed = connection.State == ConnectionState.Closed;
            if (wasClosed)
                await connection.OpenAsync(cancellationToken);
            var commands = new DbCommand[sql.Length];
            for (var i = 0; i < sql.Length; i++)
            {
                await using var command = connection.CreateCommand();
                command.CommandText = sql[i];
                command.Transaction = transaction;
                commands[i] = command;
            }

            try
            {
                var results = new int[sql.Length];
                for (var i = 0; i < commands.Length; i++)
                    results[i] = await commands[i].ExecuteNonQueryAsync(cancellationToken);
                await transaction.CommitAsync();
                return results;
            }
            catch (DbException e)
            {
                await transaction.RollbackAsync();
                return Array.Empty<int>();
            }
            finally
            {
                transaction.Dispose();
                if (wasClosed)
                    connection.Close();
            }
        }

        /// <summary>
        /// Execute a command asynchronously using Task.
        /// </summary>       
        /// <param name = "sql">The SQL to execute for the query.</param>
        /// <param name = "param">The parameters to pass, if any.</param>
        /// <param name = "transaction">The transaction to use for this query.</param>
        /// <returns>The number of rows affected.</returns>
        public static async Task<int[]> ExecuteAsync(this DbConnection connection, string[] sql, object[] param, DbTransaction transaction, CancellationToken cancellationToken = default)
        {
            bool wasClosed = connection.State == ConnectionState.Closed;
            if (wasClosed)
                await connection.OpenAsync(cancellationToken);
            var commands = new DbCommand[sql.Length];
            for (var i = 0; i < sql.Length; i++)
            {
                await using var command = connection.CreateCommand();
                command.CommandText = sql[i];
                command.Transaction = transaction;
                var paramt = param[i];
                foreach (var property in paramt.GetType().GetProperties())
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = "@" + property.Name;
                    parameter.Value = property.GetValue(paramt);
                    command.Parameters.Add(parameter);
                }

                commands[i] = command;
            }

            try
            {
                var results = new int[sql.Length];
                for (var i = 0; i < commands.Length; i++)
                    results[i] = await commands[i].ExecuteNonQueryAsync(cancellationToken);
                await transaction.CommitAsync();
                return results;
            }
            catch (DbException e)
            {
                await transaction.RollbackAsync();
                return Array.Empty<int>();
            }
            finally
            {
                transaction.Dispose();
                if (wasClosed)
                    connection.Close();
            }
        }
    }
}

Usefull​

Download Example (.NET C# )​

tip

Download Example project Breezy

Share Breezy​

https://ignatandrei.github.io/RSCG_Examples/v2/docs/Breezy

In the same category (Database) - 3 other generators​

Dapper.AOT​

Gedaq​

TableStorage​