C#手把手教你写一个自己的ORM（二）
前言：
前面我们讲了使用反射，特性的方式来确定表名，进而进行封装使用基本的ORM。
但是，如果我们想要进一步的使用linq的方式来实现，那我们又应该如何做呢？这个时候，我们就需要引入一个新的概念：表达式树，即解析linq表达式。
常见expression类别
这里我列举几个常见的expression表达式类别：
1、BinaryExpression：表示具有二进制运算符的表达式。
2、BlockExpression：表示包含一个表达式序列的块，表达式中可定义变量。
3、ConstantExpression：表示具有常数值的表达式。
4、DynamicExpression：表示一个动态操作。
5、Expression：提供一种基类，表示表达式树节点的类派生自该基类。 它还包含用来创建各种节点类型的 static（在 Visual Basic 中为 Shared）工厂方法。 这是一个 abstract 类。
6、LambdaExpression：介绍 lambda 表达式。 它捕获一个类似于 .NET 方法主体的代码块。
7、MemberExpression：表示访问字段或属性。
8、MemberInitExpression：表示调用构造函数并初始化新对象的一个或多个成员。
9、UnaryExpression：表示具有一元运算符的表达式。
更多的其他类型请见Microsoft官方文档 https://learn.microsoft.com/zh-cn/dotnet/api/system.linq.expressions?view=netframework-4.8。
概述
首先，我们可以自己开发这个东西。学学可以，但是我并不建议大家废寝忘食的搞这个东西。因为弊端太多，下面我说说他们的优缺点把！
优点
1、简历镀金，自己开发过ORM，在github上有自己的开源项目，听听这多牛逼啊。
2、学习提升，在开发过程中，你会学习到很多表达式树，以及反射，框架相关的东西，这些在其他领域也是很有用处的。
缺点
1、工作量大，需要兼容许多数据库，每种数据库的语句不一，都需要单独处理。
2、维护成本，表达式树有很多的情况，每一种你都需要单独处理，代码会经常报bug，需要你来解决。
3、使用问题，一般用不到自己的项目（出了事自己得负责呀），通常用市面上常用的OMR就行了，封装得都很不错。
开发
废话也不多说了，我讲讲怎么做这个东西吧。
1、紧接上集：C#手把手教你写一个自己的ORM（一），我们可以在现有的ORM里面增加一个函数，转向linq接口，后续使用linq表达式解析。
/// <summary>
/// 转到linq查询 返回一个新的查询实例
/// </summary>
/// <typeparam name="T1"></typeparam>
/// <returns></returns>
public ISelect<T1> Select<T1>() where T1: new () => new SelectProvider<T1>(this);
由于量确实有点多，我就直接贴代码。
ISelect文件在这儿
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
namespace Watch.Common
{
public interface ISelect<T1>
{
/// <summary>
/// 按列排序，OrderBy(a => a.Time)
/// </summary>
/// <typeparam name="TMember"></typeparam>
/// <param name="column"></param>
/// <returns></returns>
ISelect1<T1,T2> Join<T2>(Expression<Func<T1, T2>> column);
/// <summary>
/// 按列排序，OrderBy(a => a.Time)
/// </summary>
/// <typeparam name="TMember"></typeparam>
/// <param name="column"></param>
/// <returns></returns>
ISelect<T1> OrderBy<TMember>(Expression<Func<T1, TMember>> column);
/// <summary>
/// 分组，GroupBy(a => a.Time)
/// </summary>
/// <typeparam name="TMember"></typeparam>
/// <param name="column"></param>
/// <returns></returns>
ISelectGrouping<TKey, T1> GroupBy<TKey>(Expression<Func<T1, TKey>> column);
/// <summary>
/// 分组，GroupBy(a => a.Time)
/// </summary>
/// <typeparam name="TMember"></typeparam>
/// <param name="column"></param>
/// <returns></returns>
//ISelect<T1> GroupBy<TKey>(Expression<Func<T1, TKey>> column);
/// <summary>
/// 按列排序，OrderBy(true, a => a.Time)
/// </summary>
/// <typeparam name="TMember"></typeparam>
/// <param name="condition">true 时生效</param>
/// <param name="column"></param>
/// <returns></returns>
ISelect<T1> OrderBy<TMember>(bool condition, Expression<Func<T1, TMember>> column);
/// <summary>
/// 按列倒序，OrderBy(a => a.Time)
/// </summary>
/// <typeparam name="TMember"></typeparam>
/// <param name="column"></param>
/// <returns></returns>
ISelect<T1> OrderByDesc<TMember>(Expression<Func<T1, TMember>> column);
//
// 摘要:
// 查询的记录数量，以参数out形式返回
//
// 参数:
// count:
// 返回的变量
ISelect<T1> Count(out long count);
/// <summary>
/// 分页
/// </summary>
/// <param name="pageIndex">第几页</param>
/// <param name="pageSize">每页多少</param>
/// <returns></returns>
ISelect<T1> Page(int pageIndex, int pageSize);
/// <summary>
/// 按列倒叙，OrderBy(true, a => a.Time)
/// </summary>
/// <typeparam name="TMember"></typeparam>
/// <param name="condition">true 时生效</param>
/// <param name="column"></param>
/// <returns></returns>
ISelect<T1> OrderByDesc<TMember>(bool condition, Expression<Func<T1, TMember>> column);
/// <summary>
/// 查询条件， Where(a => a.Id > 10)
/// </summary>
/// <param name="expression">lambda表达式</param>
/// <returns></returns>
ISelect<T1> Where(Expression<Func<T1, bool>> expression);
/// <summary>
/// 查询条件 WhereIf(a => a.Id > 10)
/// </summary>
/// <param name="condition">true 时生效</param>
/// <param name="expression">lambda表达式</param>
/// <returns></returns>
ISelect<T1> WhereIf(bool condition, Expression<Func<T1, bool>> expression);
/// <summary>
/// 执行SQL查询，返回 T1 实体所有字段的记录，记录不存在时返回 Count 为 0 的列表<para></para>
/// </summary>
/// <returns></returns>
List<T1> ToList();
/// <summary>
/// 执行SQL查询，返回 T1 实体所有字段的第一条记录，记录不存在时返回 null
/// </summary>
/// <returns></returns>
T1 ToOne();
/// <summary>
/// 返回即将执行的SQL语句
/// </summary>
/// <returns></returns>
string ToSql();
/// <summary>
/// 执行SQL查询，返回 T1 实体所有字段的记录，记录不存在时返回 Count 为 0 的列表<para></para>
/// 注意：<para></para>
/// 1、ToList<dto>() 可以返回 dto 所有实体<para></para>
/// </summary>
/// <returns></returns>
List<TReturn> ToList<TReturn>() where TReturn : class, new();
/// <summary>
/// 执行SQL查询，返回 T1 实体所有字段的记录，记录不存在时返回 Count 为 0 的列表<para></para>
/// 注意：<para></para>
/// 1、ToList<dto>() 可以返回 dto 所有实体<para></para>
/// 2、ToList(a => new { a }) 这样也可以<para></para>
/// </summary>
/// <returns></returns>
List<TReturn> ToList<TReturn>(Expression<Func<T1, TReturn>> expression) where TReturn : class, new();
}
}
实现类SelectProvider如下：
using Newtonsoft.Json.Linq;
using System;
using System.CodeDom;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Watch.Common
{
/// <summary>
///
/// </summary>
/// <typeparam name="T1"></typeparam>
public class SelectProvider<T1> : SelectProviderReader<ISelect<T1>, T1>, ISelect<T1> where T1 : new()
{
public string _table = "";
private string _key;
public string _select = "select *";
public string _where = " where 1=1";
public string _having = "";
public string _paramter = "t";
public string _limit = "";
public string _groupby = "";
public string _groupkey = "";
public string _orderType = "";
public int _pageIndex = 0;
public int _pageSize = 0;
private ORM _orm = null;
public SelectProvider(ORM sqlHelper)
{
this._orm = sqlHelper;
this._table = typeof(T1).CustomAttributes.FirstOrDefault(t => t.AttributeType.Name.ToLower().Contains("tablename"))?.ConstructorArguments.FirstOrDefault().Value.ToString() ?? typeof(T1).Name;
_key = typeof(T1).CustomAttributes.FirstOrDefault(t => t.AttributeType.Name.ToLower().Contains("key")).ConstructorArguments.FirstOrDefault().Value.ToString();
}
public List<T1> ToList()
{
var columns = typeof(T1).GetProperties().Select(t => $"{_paramter}.{t.Name}").ToList();
_select = $"select {_limit} {string.Join(", ", columns)} ";
return this._orm.QueryList<T1>(this.ToSql());
}
public T1 ToOne()
{
_limit = "top 1";
var columns = typeof(T1).GetProperties().Select(t => $"{_paramter}.{t.Name}").ToList();
_select = $"select {_limit} {string.Join(", ", columns)} ";
return this._orm.QueryList<T1>(this.ToSql()).FirstOrDefault();
}
public string ToSql()
{
var sql = $"{_select} from {_table} {_paramter} {_where} {_groupby} {_having}";
if (_pageSize != 0)
{
return $@"
select * from (
select ass1.*, ROW_NUMBER() OVER(Order by {_key} ) AS RowId from ({sql}) ass1
) as finalTable where finalTable.RowId between {(_pageIndex - 1) * _pageSize + 1} and {_pageIndex * _pageSize}
";
}
return $"{sql} {_orderby}";
}
public List<TReturn> ToList<TReturn>() where TReturn : class, new()
{
var propertiesT = typeof(T1).GetProperties().ToList();
var propertiesR = typeof(TReturn).GetProperties().ToList();
var commonPropertyNames = propertiesT.Where(t => propertiesR.Select(t1 => t1.Name).Contains(t.Name)).Select(t => t.Name).ToList();
propertiesT = propertiesT.Where(t => commonPropertyNames.Contains(t.Name)).OrderBy(t => t.Name).ToList();
propertiesR = propertiesR.Where(t => commonPropertyNames.Contains(t.Name)).OrderBy(t => t.Name).ToList();
var columns = propertiesT.Select(t => $"{t.Name} as {propertiesR.FirstOrDefault(t1 => t1.Name == t.Name).Name}").ToList();
_select = $"select {_limit} {string.Join(", ", columns)} ";
return this._orm.QueryList<TReturn>(this.ToSql());
}
public List<TReturn> ToList<TReturn>(Expression<Func<T1, TReturn>> expression) where TReturn : class, new()
{
_select = $"select {ExpressionLambdaToSql(expression)}";
return this._orm.QueryList<TReturn>(this.ToSql()); ;
}
public ISelect<T1> Where(Expression<Func<T1, bool>> expression) => WhereIf(true, expression);
public ISelect<T1> WhereIf(bool condition, Expression<Func<T1, bool>> expression)
{
if (condition)
{
_where += $" and {ExpressionLambdaToSql(expression)}";
_paramter = expression.Parameters[0].Name;
}
return this;
}
public ISelect<T1> OrderBy<TMember>(Expression<Func<T1, TMember>> column) => OrderBy(true, column);
public ISelect<T1> OrderBy<TMember>(bool condition, Expression<Func<T1, TMember>> column)
{
if (condition)
{
var order = ExpressionLambdaToSql(column);
_key = $"{order.Split(",").FirstOrDefault()?.Split(".")?.LastOrDefault()}";
_orderby += $"order by {order}";
}
return this;
}
public ISelect<T1> OrderByDesc<TMember>(Expression<Func<T1, TMember>> column) => OrderByDesc(true, column);
public ISelect<T1> OrderByDesc<TMember>(bool condition, Expression<Func<T1, TMember>> column)
{
if (condition)
{
var order = ExpressionLambdaToSql(column);
_key = $"{order.Split(",").FirstOrDefault()?.Split(".")?.LastOrDefault()} desc";
_orderby += $"order by {order} desc";
}
return this;
}
public ISelect1<T1, T2> Join<T2>(Expression<Func<T1, T2>> column)
{
throw new NotImplementedException();
}
public ISelect<T1> GroupBy<TMember>(Expression<Func<T1, TMember>> column) => GroupByIf(true, column);
public ISelect<T1> GroupByIf<TMember>(bool condition, Expression<Func<T1, TMember>> column)
{
if (condition)
{
_groupby = $"group by {ExpressionLambdaToSql(column)}";
}
return this;
}
static readonly Dictionary<ExpressionType, string> dicExpressionOperator = new Dictionary<ExpressionType, string>() {
{ ExpressionType.OrElse, "OR" },
{ ExpressionType.Or, "|" },
{ ExpressionType.AndAlso, "AND" },
{ ExpressionType.And, "&" },
{ ExpressionType.GreaterThan, ">" },
{ ExpressionType.GreaterThanOrEqual, ">=" },
{ ExpressionType.LessThan, "<" },
{ ExpressionType.LessThanOrEqual, "<=" },
{ ExpressionType.NotEqual, "<>" },
{ ExpressionType.Add, "+" },
{ ExpressionType.Subtract, "-" },
{ ExpressionType.Multiply, "*" },
{ ExpressionType.Divide, "/" },
{ ExpressionType.Modulo, "%" },
{ ExpressionType.Equal, "=" },
};
public static Dictionary<Type, MethodInfo> _dicMethodDataReaderGetValue = new Dictionary<Type, MethodInfo>
{
[typeof(bool)] = typeof(DbDataReader).GetMethod("GetBoolean", new Type[] { typeof(int) }),
[typeof(int)] = typeof(DbDataReader).GetMethod("GetInt32", new Type[] { typeof(int) }),
[typeof(long)] = typeof(DbDataReader).GetMethod("GetInt64", new Type[] { typeof(int) }),
[typeof(double)] = typeof(DbDataReader).GetMethod("GetDouble", new Type[] { typeof(int) }),
[typeof(float)] = typeof(DbDataReader).GetMethod("GetFloat", new Type[] { typeof(int) }),
[typeof(decimal)] = typeof(DbDataReader).GetMethod("GetDecimal", new Type[] { typeof(int) }),
[typeof(DateTime)] = typeof(DbDataReader).GetMethod("GetDateTime", new Type[] { typeof(int) }),
[typeof(string)] = typeof(DbDataReader).GetMethod("GetString", new Type[] { typeof(int) }),
//[typeof(Guid)] = typeof(DbDataReader).GetMethod("GetGuid", new Type[] { typeof(int) }) 有些驱动不兼容
};
public class MyExpressionType
{
public Expression Expression { get; set; }
public MemberInfo Member { get; set; }
public MemberBindingType BindingType { get; set; }
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public string ExpressionLambdaToSql(Expression exp)
{
var result = string.Empty;
var type = exp.NodeType;
switch (type)
{
case ExpressionType.Lambda: return ExpressionLambdaToSql((exp as LambdaExpression)?.Body);
case ExpressionType.Not:
{
var notExp = (exp as UnaryExpression)?.Operand;
if (notExp.Type.IsNumberType()) return $"~{ExpressionLambdaToSql(notExp)}"; //位操作
if (notExp.NodeType == ExpressionType.MemberAccess)
{
var notBody = ExpressionLambdaToSql(notExp);
if (notBody.Contains(" IS NULL")) return notBody.Replace(" IS NULL", " IS NOT NULL");
if (notBody.Contains(" IS NOT NULL")) return notBody.Replace(" IS NOT NULL", " IS NULL");
if (notBody.Contains("=")) return notBody.Replace("=", "!=");
if (notBody.Contains("!=")) return notBody.Replace("!=", "=");
return $"{notBody}";
}
return $"not({ExpressionLambdaToSql(notExp)})";
}
case ExpressionType.Quote:
case ExpressionType.Invoke:
case ExpressionType.TypeAs:
case ExpressionType.Convert:
{
var nullType = Nullable.GetUnderlyingType(exp.Type);
if (nullType != null)
{
switch (nullType.FullName)
{
case "System.DateTime":
return $"'{Expression.Lambda<Func<DateTime?>>(exp).Compile()().Value.ToString("yyyy/MM/dd HH:mm:ss")}'";
case "System.String":
return $"'{Expression.Lambda<Func<string>>(exp).Compile()().ToString()}'";
case "System.Int32":
return Expression.Lambda<Func<int?>>(exp).Compile()().ToString();
default:
break;
}
}
result = Regex.Replace(exp.ToString().Replace(".Value.", "."), "(?<=\\()\\w+\\.", "");
switch (exp.Type.FullName)
{
case "System.Int32":
result = result.Replace("Convert(", "Convert(int,");
break;
case "System.String":
result = result.Replace("Convert(", "Convert(varchar,");
break;
case "System.Decimal":
result = result.Replace("Convert(", "Convert(float,");
break;
default:
break;
}
}
return result;
case ExpressionType.ConvertChecked:
case ExpressionType.Negate:
case ExpressionType.NegateChecked:
case ExpressionType.Constant:
case ExpressionType.Conditional:
case ExpressionType.Parameter:
{
switch (exp.Type.FullName)
{
case "System.Int32":
result = exp.ToString();
break;
case "System.String":
result = exp.ToString().Replace("\"", "'");
break;
default:
var nullType = Nullable.GetUnderlyingType(exp.Type);
switch (nullType.FullName)
{
case "System.DateTime":
result = $"'{Expression.Lambda<Func<DateTime?>>(exp).Compile()().Value.ToString("yyyy/MM/dd HH:mm:ss")}'";
break;
case "System.String":
result = $"'{Expression.Lambda<Func<object>>(exp).Compile()().ToString()}'";
break;
case "System.Int32":
result = Expression.Lambda<Func<int>>(exp).Compile()().ToString();
break;
default:
break;
}
break;
}
return result;
}
case ExpressionType.MemberInit:
{
var memberInit = exp as MemberInitExpression;
return string.Join(", ", memberInit.Bindings.Select(t => ExpressionLambdaToSql(t.GetType().GetProperty("Expression").GetValue(t) as Expression) + " as " + t.Member.Name));
}
case ExpressionType.MemberAccess:
{
var memberAccess = exp as MemberExpression;
if(memberAccess.Expression == null)
{
switch (memberAccess.Type.FullName)
{
case "System.DateTime":
result = Expression.Lambda<Func<DateTime>>(exp).Compile()().ToString("yyyy/MM/dd HH:mm:ss");
result = $"'{result}'";
break;
default:
break;
}
return result;
}
result = memberAccess.ToString().Replace("\"", "\'").Replace(".Key.", ".");
if(Regex.IsMatch(result, "value\\(.*DisplayClass"))
{
switch (memberAccess.Type.FullName)
{
case "System.String":
result = Expression.Lambda<Func<object>>(exp).Compile()().ToString();
result = $"'{result}'";
break;
case "System.Int32":
result = Expression.Lambda<Func<int>>(exp).Compile()().ToString();
break;
default:
result = Expression.Lambda<Func<object>>(exp).Compile()().ToString();
break;
}
}
return result;
}
case ExpressionType.Call:
{
var method = exp as MethodCallExpression;
Func<Expression, string> getExp = exparg => ExpressionLambdaToSql(exparg);
switch (method.Method.Name)
{
case "IsNullOrEmpty":
var arg1 = getExp(method.Arguments[0]);
return $"({arg1} is null or {arg1} = '')";
case "IsNullOrWhiteSpace":
var arg2 = getExp(method.Arguments[0]);
return $"({arg2} is null or {arg2} = '' or ltrim({arg2}) = '')";
case "Concat":
return $"concat({string.Join(", ", method.Arguments.Select(a => getExp(a)).ToArray())})";
case "ToString":
return $"cast({getExp(method.Object)} as varchar)";
case "Contains":
var condition = getExp(method.Object);
if(Regex.IsMatch(condition, "List"))
{
if (Regex.IsMatch(condition, "Int"))
{
return $"{getExp(method.Arguments[0])} in ({string.Join(", ", Expression.Lambda<Func<List<int>>>(method.Object).Compile()())})";
}
if (Regex.IsMatch(condition, "String"))
{
return $"{getExp(method.Arguments[0])} in ('{string.Join("', '", Expression.Lambda<Func<List<int>>>(method.Object).Compile()())}')";
}
}
return $"{getExp(method.Object)} like {getExp(method.Arguments[0])}";
case "Count":
return $"Count(1)";
case "Sum":
return $"Sum({getExp(method.Arguments[0]).Replace(".Value.", ".")})";
case "Format":
return $"'{string.Format(Expression.Lambda<Func<string>>(method.Arguments[0]).Compile()(), Expression.Lambda<Func<string>>(method.Arguments[1]).Compile()())}'";
}
break;
}
case ExpressionType.OrElse:
break;
case ExpressionType.New: return $"{string.Join(", ", (exp as NewExpression).Arguments)}";
default:
break;
}
var expBinary = exp as BinaryExpression;
return $"{ExpressionLambdaToSql(expBinary.Left)} {dicExpressionOperator[expBinary.NodeType]} {ExpressionLambdaToSql(expBinary.Right)}";
}
ISelectGrouping<TKey, T1> ISelect<T1>.GroupBy<TKey>(Expression<Func<T1, TKey>> column)
{
_groupby = $"group by {InnerNewLambda(column?.Body)}";
return new SelectGroupingProvider<TKey, T1>(this, _orm);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public string InnerNewLambda(Expression exp)
{
var result = "";
var noteType = exp.NodeType;
switch (noteType)
{
case ExpressionType.Lambda: return ExpressionLambdaToSql((exp as LambdaExpression)?.Body);
case ExpressionType.MemberAccess:
{
var memberAccess = exp as MemberExpression;
switch (memberAccess.Expression.NodeType)
{
case ExpressionType.Parameter:
_groupkey = memberAccess.Member.Name;
result = memberAccess.ToString().Replace("\"", "\'");
break;
case ExpressionType.MemberAccess:
result = memberAccess.ToString().Replace("\"", "\'").Replace(".Key.", ".");
break;
case ExpressionType.Constant:
switch (memberAccess.Type.FullName)
{
case "System.String":
result = Expression.Lambda<Func<object>>(exp).Compile()().ToString();
result = $"'{result}'";
break;
case "System.Int32":
result = Expression.Lambda<Func<int>>(exp).Compile()().ToString();
break;
}
break;
}
return result;
}
case ExpressionType.New: return $"{string.Join(", ", (exp as NewExpression).Arguments)}";
default:
break;
}
return result;
}
public ISelect<T1> Page(int pageIndex, int pageSize)
{
if(pageSize > 0)
{
_pageSize = pageSize;
_pageIndex = pageIndex;
}
return this;
}
public ISelect<T1> Count(out long count)
{
count = _orm.Count(this.ToSql());
return this;
}
}
}
呈现效果，看看是不是很酷呢：
static void Test()
{
var sqll = _orm.Select<Device_Info>()
.WhereIf(!string.IsNullOrWhiteSpace(req.SerialNumber), t => t.SerialNumber.Contains($"%{req.SerialNumber}%"))
.WhereIf(!string.IsNullOrWhiteSpace(req.DeviceName), t => t.DeviceName.Contains($"%{req.DeviceName}%"))
.WhereIf(req.Status.HasValue, t => t.Status == req.Status.Value)
.WhereIf(req.Model.HasValue, t => t.Model == req.Model.Value)
.WhereIf(req.ContinentId.HasValue, t => t.ContinentId == req.ContinentId.Value)
.WhereIf(req.CuserId.HasValue, t => t.CusertId == req.CuserId.Value)
.WhereIf(req.CountryId.HasValue, t => t.CountryId == req.CountryId.Value)
.WhereIf(req.CreateTime.IsNotNull() && req.CreateTime.Count == 2, t => t.Created >= req.CreateTime[0] && t.Created <= req.CreateTime[1])
.OrderByDesc(t => t.LastConnTime)
.Page((int)1, (int)10)
.ToSql(); // ToList<T>();
}
总结
博主这里写的是单表的sqlserver ORM linq解析代码，包含了分组 排序 及一些常用函数的用法，有想法的朋友可以自行完善多表 多库，还是很有意义的，有什么不懂的可以微信私我。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
