C# Expression 树转化为SQL语句（一）
https://www.cnblogs.com/linxingxunyan/p/6245396.html
sql有有四中基本语句，分别是增删改查，在建立model后如何生成这四中sql语句，降低开发时间。
我们先模拟出一张学生表：
public class Student
{
public int id { get; set; }
public string name { get; set; }
public int math { get; set; } //数学成绩
public DateTime createTime { get; set; }
}
首先我们来看看增加，也就是插入语句。插入语句语法比较固定变化少通过泛型和反射可以直接生成。string类型和DateTime类型需要加单引号，其他类型不需要加。
public static void Main(string[] args)
{
Student stu = new Student
{
id = 1,
name = "张三",
matn = 59,
createTime = DateTime.Now
};
string sql = CreateInsertSql(stu);
Console.WriteLine(sql);
Console.ReadLine();
}
public static string CreateInsertSql<T>(T model)
{
string sql = "Insert into {0}({1}) Values({2})";
string table = string.Empty; //表名
List<string> member = new List<string>(); //全部列名
List<string> member_value = new List<string>(); //全部的值
Type type = typeof(T);
table = type.Name;
foreach (PropertyInfo item in type.GetProperties())
{
string name = item.Name;
member.Add(name);
object vaule = item.GetValue(model);
string v_str = string.Empty;
if (vaule is string)
{
v_str = string.Format("'{0}'", vaule.ToString());
}
else if (vaule is DateTime)
{
DateTime time = (DateTime)vaule;
v_str = string.Format("'{0}'", time.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
v_str = vaule.ToString();
}
member_value.Add(v_str);
}
sql = string.Format(sql, table, string.Join(",", member), string.Join(",", member_value));
return sql;
}
调试结果为：
接下来我们来看看其他三种简单sql 删，改，查。简单的分析一下：
删除：前面固定(delete from ) + 表名 + where条件
修改：前面固定(update) +表名 +set + 修改内容+ where条件
查找（根据model 我们默认查找全部字段）： 前面固定（select * from ） + 表名 +where 条件
从上面可以看出来我们需要3中参数（表名，修改内容，where条件）表名非常简单，可以把类名作为表名，反射一个就可以得到，接下来就是修改内容和where条件，修改内容比较简单格式为 set a=a_value,b=b_value .........，where 条件较为复杂。
用Expression 表达式树是受EntityFrame的启发，有些不了解的可以看看EF的一些函数的定义。
现在就开始使用Expression表达式树来生产这2种sql语句。
Expression表达式树有一个基类是Expression，然后有非常都的类继承这个类，我们想获取继承类的名称和命名空间的的时候可以用 ：obj.GetType().Name 和obj.getType().Namespace来获取，这样便于调试。
1. update中的修改内容。假设我们要对学生的分数进行修改（改成60，让人及格），还有姓名（就是凑个字段），其他的数据不修改。
参数类型为： Expression<Func<Student, Student>> la = n => new Student { name = "李四", matn = 60 }; 我们要生成的sql为 name='李四'，math=60,为什么类型是这个可以去看看EF扩展的内容。
la类型是LambdaExpression，我们要解析的事la.Body 其类型为MemberInitExpression
1 public static void Main(string[] args)
2 {
3 Expression<Func<Student, Student>> la = n => new Student { name = "李四", matn = 60 };
4 Console.WriteLine(GetExpressStr((MemberInitExpression)(la.Body)));
5 Console.ReadLine();
6 }
7 public static string GetExpressStr(MemberInitExpression exp)
8 {
9 string result = string.Empty;
10 List<string> member = new List<string>();
11 foreach (MemberAssignment item in exp.Bindings)
12 {
13 string update = item.Member.Name + "=" + GetConstantStr((ConstantExpression)item.Expression);
14 member.Add(update);
15 }
16 result = string.Join(",", member);
17 return result;
18 }
19 public static string GetConstantStr(ConstantExpression exp)
20 {
21 object vaule = exp.Value;
22 string v_str = string.Empty;
23 if (vaule is string)
24 {
25 v_str = string.Format("'{0}'", vaule.ToString());
26 }
27 else if (vaule is DateTime)
28 {
29 DateTime time = (DateTime)vaule;
30 v_str = string.Format("'{0}'", time.ToString("yyyy-MM-dd HH:mm:ss"));
31 }
32 else
33 {
34 v_str = vaule.ToString();
35 }
36 return v_str;
37 }
调试结果：
2.where条件
where条件是最麻烦的地方。之前的只有等于号，而where却有大于，小于，且或等等符号，不过Exresion基类中提供了NodeType 类型为ExpressionType，我们可以获取到对应的运算符。参数类型为
Expression<Func<Student,bool>> 简单理解where条件是每条数据符合不符合，所以返回值为bool
1 public static void Main(string[] args)
2 {
3 Expression<Func<Student,bool>> la =( n=>n.id > 1 && n.id <100 &&n.name !="张三" && n.matn >=60 && n.id != 50 && n.createTime != null);
4 Console.WriteLine(DealExpress(la));
5 Console.ReadLine();
6 }
7 public static string DealExpress(Expression exp)
8 {
9 if (exp is LambdaExpression )
10 {
11 LambdaExpression l_exp = exp as LambdaExpression;
12 return DealExpress(l_exp.Body);
13 }
14 if (exp is BinaryExpression)
15 {
16 return DealBinaryExpression(exp as BinaryExpression);
17 }
18 if (exp is MemberExpression)
19 {
20 return DealMemberExpression(exp as MemberExpression);
21 }
22 if (exp is ConstantExpression)
23 {
24 return DealConstantExpression(exp as ConstantExpression);
25 }
26 if (exp is UnaryExpression)
27 {
28 return DealUnaryExpression(exp as UnaryExpression);
29 }
30 return "";
31 }
32 public static string DealUnaryExpression(UnaryExpression exp)
33 {
34 return DealExpress(exp.Operand);
35 }
36 public static string DealConstantExpression(ConstantExpression exp)
37 {
38 object vaule = exp.Value;
39 string v_str = string.Empty;
40 if (vaule == null)
41 {
42 return "NULL";
43 }
44 if (vaule is string)
45 {
46 v_str = string.Format("'{0}'", vaule.ToString());
47 }
48 else if (vaule is DateTime)
49 {
50 DateTime time = (DateTime)vaule;
51 v_str = string.Format("'{0}'", time.ToString("yyyy-MM-dd HH:mm:ss"));
52 }
53 else
54 {
55 v_str = vaule.ToString();
56 }
57 return v_str;
58 }
59 public static string DealBinaryExpression(BinaryExpression exp)
60 {
61
62 string left = DealExpress(exp.Left);
63 string oper = GetOperStr(exp.NodeType);
64 string right = DealExpress(exp.Right);
65 if (right == "NULL")
66 {
67 if (oper == "=")
68 {
69 oper = " is ";
70 }
71 else
72 {
73 oper = " is not ";
74 }
75 }
76 return left + oper + right;
77 }
78 public static string DealMemberExpression(MemberExpression exp)
79 {
80 return exp.Member.Name;
81 }
82 public static string GetOperStr(ExpressionType e_type)
83 {
84 switch (e_type)
85 {
86 case ExpressionType.OrElse: return " OR ";
87 case ExpressionType.Or: return "|";
88 case ExpressionType.AndAlso: return " AND ";
89 case ExpressionType.And: return "&";
90 case ExpressionType.GreaterThan: return ">";
91 case ExpressionType.GreaterThanOrEqual: return ">=";
92 case ExpressionType.LessThan: return "<";
93 case ExpressionType.LessThanOrEqual: return "<=";
94 case ExpressionType.NotEqual: return "<>";
95 case ExpressionType.Add: return "+";
96 case ExpressionType.Subtract: return "-";
97 case ExpressionType.Multiply: return "*";
98 case ExpressionType.Divide: return "/";
99 case ExpressionType.Modulo: return "%";
100 case ExpressionType.Equal: return "=";
101 }
102 return "";
103 }
调试结果：
这些代码中一些漏洞，仅供大家参考学习，这些代码目前不能接受参数,如n=>n.id==m（int m=1）,下一篇将会对Expression表达式树的参数进行解析，欢迎大家指正。
更新2019-11-21
解决 bool 转数据库 bit 类型
1 public static void Main(string[] args)
2 {
3 Expression<Func<Student, bool>> la = (n =>
4 n.id > 1
5 && n.id < 100 && n.name != "张三"
6 && n.math >= 60 && n.id != 50
7 && n.createTime != null
8 && n.delete
9 && n.IsDel == true
10 && !n.isAdmin
11 && 1 + 2 == 3
12 && 1 == 2);
13
14 Expression<Func<Student, bool>> la2 = n=> 1+2==3;
15
16 Console.WriteLine(DealExpress(la));
17 Console.ReadLine();
18 }
19 public static string DealExpress(Expression exp)
20 {
21 if (exp is LambdaExpression)
22 {
23 LambdaExpression l_exp = exp as LambdaExpression;
24 return DealBoolExp(l_exp.Body);
25 }
26 if (exp is BinaryExpression)
27 {
28 return DealBinaryExpression(exp as BinaryExpression);
29 }
30 if (exp is MemberExpression)
31 {
32 return DealMemberExpression(exp as MemberExpression);
33 }
34 if (exp is ConstantExpression)
35 {
36 return DealConstantExpression(exp as ConstantExpression);
37 }
38 if (exp is UnaryExpression)
39 {
40 return DealUnaryExpression(exp as UnaryExpression);
41 }
42 return "";
43 }
44 public static string DealBoolExp(Expression exp)
45 {
46 if (exp == null)
47 {
48 var t = 1;
49 }
50 // 关于bool类型特殊处理
51 if (exp is BinaryExpression)
52 {
53 return DealExpress(exp);
54 }
55 if (exp is MemberExpression) // n.isDelete
56 {
57 return DealMemberExpression(exp as MemberExpression) + "=1";
58 }
59 if (exp is UnaryExpression) //!n.isDelete
60 {
61 return DealUnaryExpression(exp as UnaryExpression) + "<>1";
62 }
63 if (exp is ConstantExpression) //
64 {
65 var str = DealConstantExpression(exp as ConstantExpression);
66 return str == "1" ? "1==1" : "1!=1";
67 }
68 return "";
69 }
70
71
72 public static string DealUnaryExpression(UnaryExpression exp)
73 {
74 return DealExpress(exp.Operand);
75 }
76 public static string DealConstantExpression(ConstantExpression exp)
77 {
78 object vaule = exp.Value;
79 string v_str = string.Empty;
80 if (vaule == null)
81 {
82 return "NULL";
83 }
84 if (vaule is string)
85 {
86 v_str = string.Format("'{0}'", vaule.ToString());
87 }
88 else if (vaule is DateTime)
89 {
90 DateTime time = (DateTime)vaule;
91 v_str = string.Format("'{0}'", time.ToString("yyyy-MM-dd HH:mm:ss"));
92 }
93 else if (vaule is Boolean)
94 {
95 Boolean data = Convert.ToBoolean(vaule);
96 v_str = data? "1" : "0";
97 }
98 else
99 {
100 v_str = vaule.ToString();
101 }
102 return v_str;
103 }
104 public static string DealBinaryExpression(BinaryExpression exp)
105 {
106
107 switch (exp.NodeType)
108 {
109 case ExpressionType.OrElse:
110 case ExpressionType.AndAlso:
111 {
112 string left = DealBoolExp(exp.Left);
113 string oper = GetOperStr(exp.NodeType);
114 string right = DealBoolExp(exp.Right);
115 return left + oper + right;
116
117 }
118
119 default:
120 {
121 string left = DealExpress(exp.Left);
122 string oper = GetOperStr(exp.NodeType);
123 string right = DealExpress(exp.Right);
124 if (right == "NULL")
125 {
126 if (oper == "=")
127 {
128 oper = " is ";
129 }
130 else
131 {
132 oper = " is not ";
133 }
134 }
135 return left + oper + right;
136 }
137
138
139 }
140
141
142 }
143 public static string DealMemberExpression(MemberExpression exp)
144 {
145 var name = exp.Member.Name;
146 if (name == "delete")
147 {
148 var t = name;
149 }
150 return exp.Member.Name;
151 }
152 public static string GetOperStr(ExpressionType e_type)
153 {
154 switch (e_type)
155 {
156 case ExpressionType.OrElse: return " OR ";
157 case ExpressionType.Or: return "|";
158 case ExpressionType.AndAlso: return " AND ";
159 case ExpressionType.And: return "&";
160 case ExpressionType.GreaterThan: return ">";
161 case ExpressionType.GreaterThanOrEqual: return ">=";
162 case ExpressionType.LessThan: return "<";
163 case ExpressionType.LessThanOrEqual: return "<=";
164 case ExpressionType.NotEqual: return "<>";
165 case ExpressionType.Add: return "+";
166 case ExpressionType.Subtract: return "-";
167 case ExpressionType.Multiply: return "*";
168 case ExpressionType.Divide: return "/";
169 case ExpressionType.Modulo: return "%";
170 case ExpressionType.Equal: return "=";
171
172 }
173 return "";
174 }
简单来讲 1。表达式整体 必须是 BinaryExpression 这个类型 2. 如果符号是 OR 或And 那么左右2边也必须是 BinaryExpression
代码有些取巧应该有些不足之处，仅供大家参考
