Dapper

[Costas]

Is a simple object mapper for .NET and owns the title of King of Micro ORM in terms of speed and is virtually as fast as using a raw ADO.NET data reader. An ORM is an Object Relational Mapper, which is responsible for mapping between database and programming language.

Dapper extends the IDbConnection by providing useful extension methods to query your database.

home
https://github.com/DapperLib/Dapper
https://nuget.org/packages/Dapper/

example
https://dev.to/byme8/making-dapper-and-json-friends-5afc

byme8.Dapper.Json - Add JSON support for Dapper
https://github.com/byme8/Dapper.Json

Is the era of reflection-heavy C# libraries at an end?
https://blog.marcgravell.com/2021/05/is-era-of-reflection-heavy-c-libraries.html

 

[Costas]

How to map a one to many tsql to class (How to map a join to class) with Dapper

public async Task<IEnumerable<SalesOrder>> SalesOrdersOverMinAmountDue(int minimumAmount)
{
    var sql = @"SELECT
                    soh.SalesOrderID, soh.OrderDate, soh.TotalDue,
                    sd.SalesOrderDetailID, sd.ProductID, sd.OrderQty, sd.LineTotal
                FROM SalesLT.SalesOrderHeader soh
                JOIN SalesLT.SalesOrderDetail sd ON soh.SalesOrderID = sd.SalesOrderID
                WHERE soh.TotalDue > @minAmount";

    var orders = (IEnumerable<SalesOrder>) await connection.QueryAsync<SalesOrder, SalesOrderDetail, SalesOrder>(
        sql,
        map: (order, detail) =>
        {
            order.OrderDetails = order.OrderDetails ?? new List<SalesOrderDetail>();
            order.OrderDetails.Add(detail);
            return order;
        },
        param: new { minAmount = minimumAmount },
        splitOn: "SalesOrderItemID"
    );

    return orders;
}

How to many to many with Dapper

public async Task<IEnumerable<Product>> ProductsWithCategories()
{
    using (var connection = new SqlConnection(Config.ConnectionString))
    {
        await connection.OpenAsync();
        var sql = @"
        SELECT
            p.ProductID, p.Name, p.ProductNumber,
            c.CategoryID, c.Name AS CategoryName
        FROM SalesLT.Product p
        JOIN SalesLT.ProductCategoryLink pcl ON p.ProductID = pcl.ProductID
        JOIN SalesLT.Category c ON pcl.CategoryID = c.CategoryID
        ORDER BY p.ProductID";

        var productDict = new Dictionary<int, Product>();

        var products = await connection.QueryAsync<Product, Category, Product>(
            sql,
            (product, category) =>
            {
                if (!productDict.TryGetValue(product.ProductId, out var existingProduct))
                {
                    existingProduct = product;
                    existingProduct.Categories = new List<Category>();
                    productDict.Add(existingProduct.ProductId, existingProduct);
                }

                existingProduct.Categories.Add(category);
                return existingProduct;
            },
            splitOn: "CategoryID"
        );
        return productDict.Values;
    }
}

source - https://github.com/Dometrain/from-zero-to-hero-dapper-in-dotnet/tree/main/Working with Relationships