Dapper最佳扩展Dommel+Dapper.FluentMap

Dommel为Dapper提供了丰富简便的CRUD 操作接口，比Dapper.Contrib更加方便，Dapper.FluentMap支持了FluentApi，让POCO代码更加纯粹简洁。

扩展简介

Dommel

Dommel提供了简便的CRUD 操作Api，支持Linq表达式。

dotnet add package Dommel

Dapper-FluentMap

Dapper.FluentMap扩展使你优雅的配置POCO属性和数据库列之间的映射，这使你的POCO属性保持干净。功能类似与EntityFramework的FluentApi。

dotnet add package Dapper.FluentMap

Dommel与Dapper.FluentMap结合使用需要Dapper.FluentMap.Dommel。

dotnet add package Dapper.FluentMap.Dommel

CRUD 操作

根据ID查询

var product = await connection.GetAsync<Product>(1);

查询整张表

var products = await connection.GetAllAsync<Product>();

条件查询

var products = await connection.SelectAsync<Product>(p => p.Name == "Awesome bike" && p.Created < new DateTime(2014, 12, 31) && p.InStock > 5);

// and or
var products = await connection.SelectAsync<Product>(p => p.Name == "Awesome bike" && (p.Created < new DateTime(2014, 12, 31) || p.InStock > 5));

Like查询

var products = await connection.SelectAsync<Product>(p => p.Name.Contains("bike"));
var products = await connection.SelectAsync<Product>(p => p.Name.StartsWith("bike"));
var products = await connection.SelectAsync<Product>(p => p.Name.EndsWith("bike"));

插入

var product = new Product { Name = "Awesome bike", InStock = 4 };
var id = await connection.InsertAsync(product);

更新

var product = await connection.GetAsync<Product>(1);
product.Name = "New name";
await connection.UpdateAsync(product);

删除

// 单个删除
var product = await connection.GetAsync<Product>(1);
await connection.DeleteAsync(product);

// 批量条件删除
await connection.DeleteMultipleAsync<Product>(p => p.InStock == 4 );

连接查询

• 一对一

public class Product
{
    public int Id { get; set; }

    public string Name { get; set; }

    // Maps to the foreign key column
    public int CategoryId { get; set; }

    // The navigation property
    public Category Category { get; set; }
}


public class Category
{
    public int Id { get; set; }

    public string Name { get; set; }
}

var product = await product.GetAsync<Product, Category, Product>(1, (product, category) =>
{
    product.Category = category;
    return product;
});

• 一对多

public class Order
{
    public int Id { get; set; }

    // The navigation property
    public ICollection<OrderLine> OrderLines { get; set; } = new List<OrderLine>();
}

public class OrderLine
{
    public int Id { get; set; }

    // The foreign key column to the Order table
    public int OrderId { get; set; }

    public string Description { get; set; }
}

var product = await product.GetAsync<Order, OrderLine, Order>(1, (order, line) =>
{
    // Naive mapping example, in reality it requires more gymnastics
    order.OrderLines.Add(line);
    return order;
});

还有...

复杂查询

var products = await connection.FromAsync<Product>(sql => sql
    .Where(x => x.Name.StartsWith("bike") && x.DeletedOn == null)
    .OrWhere(x => x.InStock > 5)
    .OrderBy(x => x.DateCreated)
    .Page(1, 25)
    .Select()));

Fluent配置

属性配置

public class ProductMap : EntityMap<Product>
{
    public ProductMap()
    {
        // Map property 'Name' to column 'strName'.
        Map(p => p.Name)
            .ToColumn("strName");

        // Ignore the 'LastModified' property when mapping.
        Map(p => p.LastModified)
            .Ignore();
    }
}

配置映射

FluentMapper.Initialize(config =>
    {
       config.AddMap(new ProductMap());
    });

补：实现ProductMap和初始化后，Dommel的CRUD操作对Product表都生效，Product的Name字段对应数据库表strName列。