介绍：
Dapper.Common是基于Dapper的Lambda扩展,遵循Linq语法规则,上手快,支持Mysql,Sqlserver(目前只实现了这两个数据，实现其他数据库也很轻松)，支持单表，多表，自定义函数等功能。
开源地址：https://github.com/1448376744/Dapper.Common
Nuget：Install-Package Dapper.Common -Version 1.5.0
1.Mapper
public class User
{
/// <summary>
/// name:用于映射字段名和数据库字段不一致
/// key:
/// 目前实现了Primary的定义，设置为Primary的字段update实体时，默认采用该字段为更新条件
/// isIdentity：
/// 设置未true时在Insert时不会向该字段设置任何值
/// isColumn:
/// 标识该字段是否在数据库存在，用于扩展User而不在sql中生成该字段
/// </summary>
[Column(name: "id", key: ColumnKey.Primary, isIdentity: true, isColumn: true)]
public int? Id { get; set; }
[Column(name:"nick_name")]
public string NickName { get; set; }
[Column(name: "create_time")]
public DateTime? CreateTime { get; set; }
}
2.Config
//在App启动时执行一次即可
SessionFactory.AddDataSource(new DataSource()
{
Name = "mysql",
Source = () => new SqlConnection("connectionString"),
SourceType = DataSourceType.SQLSERVER,
UseProxy = true//使用Session的静态代理实现，记录日志，执行耗时
});
//获取数据库上下文
using (var session = SessionFactory.GetSession("msql"))
{
//linq to sql
}
3.Insert
var entity = new User()
{
CreateTime=DateTime.Now,
NickName="dapper",
};
//绝大部分接口可以设置condition已决定是否执行，支持批量更新
session.From<User>().Insert(entity,condition:1>2);
//查看日志
var loggers = session.Loggers;
2.Update
//更新所有字段（where id=2），支持批量
session.From<User>().Update(entity);
//更新部分字段
session.From<User>()
.Set(a => a.NickName, "李四", condition: true)//condition为true时更新该字段
.Set(a => a.Balance, a => a.Balance + 100)//余额在原来基础增加100
.Where(a => a.Id.In(1,2,3))//将id为1，2，3的记录进行更新
.Update();
3.Delete
//删除id>5||nick_name like ‘%da%‘
session.From<User>()
.Where(a=>a.Id>5||a.NickName.Like("da"))
.Delete();
4.Single
//查询全部字段
var user1 = session.From<User>()
.Where(a=>a.Id==2)
.Single();
//查询部分字段
var user2 = session.From<User>()
.Where(a => a.Id == 2)
.Single(s=>new
{
s.Id,
s.NickName
});
5.Select
//查询:where id in(1,2,3)
var list = session.From<User>()
.Where(a => a.Id.In("1,2,3".Split(‘,‘)))
.Select();
6.Where
//构建动态查询，condition: true执行，多个where之间用 and 连接
var list = session.From<User>()
.Where(a => a.Id.In(1, 2, 3), condition: true)
.Where(a => a.NickName.Like("da"), condition: false)
.Where(a => a.Id > 2 || (a.NickName.Like("da") && a.Balance > 50))
.Where("select * from user_bill where user_bill.user_id=user.id")//同样可以当作字符串拼接工具
.Select();
7.Function
/// <summary>
/// 自定义函数
/// </summary>
public static class MySqlFun
{
//这里使用泛型并不是必须的，只用函数名在数据库存在即可，泛型为了指定返回数据类型
[Function]//Dapper.Common严格区分C#函数和数据库函数，一定要用该特性标识数据库函数
public static T COUNT<T>(T column)
{
return default(T);
}
[Function]
public static T MAX<T>(T column)
{
return default(T);
}
[Function]
public static T DISTINCT<T>(T column)
{
return default(T);
}
8.GroupBy
//构建动态查询，condition: true执行，多个where之间用 and 连接
var list = session.From<Order>()
.GroupBy(a => a.UserId)
.Having(a => MySqlFun.COUNT(MySqlFun.DISTINCT(a.UserId)) > 10)//count(distinct(user_id))>10
.Select(s => new
{
s.UserId,
OrderCount = MySqlFun.COUNT(1L),//这里应该返回long int，
MaxFee = MySqlFun.MAX(s.TotalFee)
});
9.Join
var list = session.From<Order, User>()
.Join((a, b) => a.UserId == b.Id, JoinType.Inner)
.GroupBy((a, b) => a.UserId)
.Having((a, b) => MySqlFun.COUNT(MySqlFun.DISTINCT(a.UserId)) > 10)//count(distinct(user_id))>10
.Select((a, b) => new
{
a.UserId,
b.NickName,
OrderCount = MySqlFun.COUNT(1L),//这里应该返回long int，
MaxFee = MySqlFun.MAX(a.TotalFee)
});
10.SubQuery
var list = session.From<Order>()
.GroupBy(a => a.UserId)
.Having(a => MySqlFun.COUNT(MySqlFun.DISTINCT(a.UserId)) > 10)
.Select(a => new
{
a.UserId,
UserName=Convert.ToString("select nick_name from user where user.id=order.user_id"),//如果这个子查询返回的是int：Convert.ToInt32(sq)
OrderCount = MySqlFun.COUNT(1L),//这里应该返回long int，
MaxFee = MySqlFun.MAX(a.TotalFee)
});
11.Transaction
//获取数据库上下文
ISession session = null;
try
{
//开启事务
session.Open(true);
//sql
//提交事务
session.Commit();
}
catch (Exception)
{
session?.Rollback();
throw;
}
finally
{
session?.Close();
}