static public string Where <T>(Expression <Func <T, bool> > lambda)
{
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
return(vistor.Condition());
}
public int Update <T>(T entity, Expression <Func <T, bool> > predicate = null)
{
StringBuilder sqlBuider = new StringBuilder($"Update [{typeof(T).Name}] ");
/* 传统实现方式
* List<string> assignments = new List<string>();
* foreach (var item in typeof(T).GetProperties())
* {
* assignments.Add(string.Format("[{0}]=@{0}",item.Name));
* }
*/
///linq简易实现如下:
System.Reflection.PropertyInfo[] propertyInfo = typeof(T).GetProperties();
var assignments = propertyInfo.Where(o => !o.Name.ToLower().Equals("id"))
.Select(o => string.Format("[{0}]=@{0}", o.Name));
sqlBuider.AppendFormat("set {0}", string.Join(",", assignments));
if (predicate != null)
{
ConditionBuilderVisitor conditionVisitor = new ConditionBuilderVisitor();
conditionVisitor.Visit(predicate);
string condition = conditionVisitor.Condition();
sqlBuider.Append($" where {condition}");
}
string sql = sqlBuider.ToString();
var parameters = propertyInfo.Where(o => !o.Name.ToLower().Equals("id"))
.Select(o => new SqlParameter
{
ParameterName = $"@{o.Name}",
Value = o.GetValue(entity) ?? DBNull.Value
}).ToArray();
return(_DbContext.Database.ExecuteSqlCommand(sql, parameters));
}
/// <summary>
/// linq to sql 实现
/// </summary>
public static void Test()
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 &&
x.Id < 5 &&
x.Id == 3 &&
x.Name.StartsWith("1") &&
x.Name.EndsWith("1") &&
x.Name.Contains("1");
//组装了expression
new List <People>().AsQueryable().Where(x => x.Age > 5 && x.Id == 10);
//解析expression并动态生成sql,区别反射动态拼装sql
//SELECT * FROM People WHERE Age>5
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
#region 表达式连接，多个判断条件的连接
{
Expression <Func <People, bool> > lambda1 = x => x.Age > 5;
Expression <Func <People, bool> > lambda2 = x => x.Id > 5;
Expression <Func <People, bool> > lambda3 = lambda1.And(lambda2);
Expression <Func <People, bool> > lambda4 = lambda1.Or(lambda2);
Expression <Func <People, bool> > lambda5 = lambda1.Not();
Do1(lambda3);
}
#endregion
}
private string BulidWhere(string sql, Expression expressionWhere)
{
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(expressionWhere);
return($"{sql} WHERE {vistor.Condition()}");
}
/// <summary>
/// 表达式树查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public List <T> ExpressQuery <T>(Expression <Func <T, bool> > predicate)
{
try
{
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(predicate);
string sqlStr = visitor.ToExpressionString();
Type modelType = typeof(T);
string sqlString = $"select * from [{modelType.Name}] where 1=1";
if (!string.IsNullOrEmpty(sqlStr))
{
sqlString += " AND " + sqlStr;
}
return(client.ExcuteSqlDelegate <List <T> >(sqlString, cmd =>
{
SqlDataReader reader = cmd.ExecuteReader();
var getData = client.GetDataReader <T>(reader);
return getData;
}, null));
}
catch (Exception ex)
{
throw ex;
}
}
public static void Show()
{
{
Expression <Func <int, int, int> > exp = (m, n) => m * n + 2 + 3 + Get(m);
// Change the expression exp to m*n -2
OperationsVisitor visitor = new OperationsVisitor();
Expression expNew = visitor.Modify(exp);
}
{
///Parse a lambda to a sql script, parse where condition
///ORM maps the database to program memory and manages the database by manipulating objects
///Developers don't need to know details of database.
var source = new List <People>().AsQueryable();
var result = source.Where <People>(p => p.Age > 5)
.Where(p => p.Name.Equals("124"))
.Where(p => p.Age < 10);
//.ToList() //Only here execute the sql
Expression <Func <People, bool> > lambda = x => x.Age > 5;
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(lambda);
Console.WriteLine(visitor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 && x.Id > 5 &&
x.Name.StartsWith("1") &&
x.Name.EndsWith("1") &&
x.Name.Contains("1");
string sql = string.Format("Delete From [{0}] WHERE {1}", typeof(People).Name, "[Age]>5 AND [ID]>5");
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(lambda);
var result = visitor.Condition();
Console.WriteLine(result);
// Queryable is to parse linq to sql script
}
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 && x.Name == "A" || x.Id > 5;
Expression <Func <People, bool> > lambda1 = x => x.Age > 5 || (x.Name == "A" && x.Id > 5);
Expression <Func <People, bool> > lambda2 = x => (x.Age > 5 || x.Name == "A") && x.Id > 5;
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
vistor.Visit(lambda1);
Console.WriteLine(vistor.Condition());
vistor.Visit(lambda2);
Console.WriteLine(vistor.Condition());
}
{
//Link expression
Expression <Func <People, bool> > lambda1 = x => x.Age > 5;
Expression <Func <People, bool> > lambda2 = x => x.Id > 5;
}
}
public static void Show()
{
{
//修改表达式目录树
Expression <Func <int, int, int> > exp = (m, n) => m * n + 2;
OperationsVisitor visitor = new OperationsVisitor();
//visitor.Visit(exp);
Expression expNew = visitor.Modify(exp);
}
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 && x.Id > 5 &&
x.Name.StartsWith("1") &&
x.Name.EndsWith("1") &&
x.Name.Contains("1");
string sql = string.Format("Delete From [{0}] WHERE {1}"
, typeof(People).Name
, " [Age]>5 AND [ID] >5"
);
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 && x.Name == "A" || x.Id > 5;
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 || (x.Name == "A" && x.Id > 5);
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => (x.Age > 5 || x.Name == "A") && x.Id > 5;
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
#region 表达式链接
{
Expression <Func <People, bool> > lambda1 = x => x.Age > 5;
Expression <Func <People, bool> > lambda2 = x => x.Id > 5;
Expression <Func <People, bool> > lambda3 = lambda1.And(lambda2);
Expression <Func <People, bool> > lambda4 = lambda1.Or(lambda2);
Expression <Func <People, bool> > lambda5 = lambda1.Not();
Do1(lambda3);
Do1(lambda4);
Do1(lambda5);
}
#endregion
}
public int DeleteByCondition(List <Tm> models, Expression <Func <Te, bool> > expression)
{
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(expression);
var sqlWhere = visitor.Condition();
var sql = string.Format("delete [{0}] where {1}", TableMapper.TableName, sqlWhere);
return(this.Execute(sql, models));
}
public int Delete <T>(Expression <Func <T, bool> > predicate)
{
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(predicate);
string condition = visitor.Condition();
string sql = $"Delete From {typeof(T).Name} where {condition}";
return(_DbContext.Database.ExecuteSqlCommand(sql));
}
public static void BatchDelete <T>(this IQueryable <T> entities, Expression <Func <T, bool> > expr)
{
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(expr);
string condition = visitor.Condition();
string sql = string.Format("DELETE FROM [{0}] WHERE {1};"
, typeof(T).Name//有可能还得根据泛型去获取
, condition);
//然后执行sql
}
public List <Te> FindByCondition(Tm model, Expression <Func <Te, bool> > expression)
{
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(expression);
var sqlWhere = visitor.Condition();
Type type = typeof(Te);
var sql = $" select * from {type.Name} where {sqlWhere}";
return(this.Querys <Te>(sql, model));
}
public static void Show()
{
{
Expression <Func <int, int, int> > exp = (x, y) => x * y + 2;
// 此时需求更改，需要将 x*y + 2 变成 x*y -2， 首先需要解析上面的expression 需要用到ExpressionVisitor类
OperationVisitor visitor = new OperationVisitor();
Expression expNew = visitor.Modify(exp);
}
{
Expression <Func <int, int, int> > exp1 = (m, n) => m * n + 2 + 3 + Get(m);
OperationVisitor visitor = new OperationVisitor();
Expression expNew = visitor.Modify(exp1);
}
{
//用处
{
var source = new List <People>().AsQueryable();
var result = source?.Where <People>(p => p.Age > 5); // Select * from People Where Age > 5
Expression <Func <People, bool> > lambda = x => x.Age > 5;
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(lambda);
Console.WriteLine(visitor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 &&
x.Id > 5 &&
x.Name.StartsWith("I") &&
x.Name.EndsWith("n") &&
x.Name.Contains("n");
var sql = string.Format("Delete from [{0}] where {1}", nameof(People), " [Age] > 5 AND [ID] > 5");
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(lambda);
Console.WriteLine(visitor.Condition());
}
}
}
public static void Show()
{
{
Expression <Func <int, int, int> > exp = (m, n) => m * n + 2;
OperationsVisitor visitor = new OperationsVisitor();
Expression exp1 = visitor.Visit(exp);
Expression expression = visitor.Modify(exp);
}
{
Expression <Func <People, bool> > exp = (p) => p.Age > 5 && p.Id > 5 && p.Name.StartsWith("t");
ConditionBuilderVisitor cond = new ConditionBuilderVisitor();
cond.Visit(exp);
string sResult = cond.Condition();
}
}
/// <summary>
/// 根据表达树批量更新数据
/// 使用前先确保实体类的主键被加上特性 [Key]
/// </summary>
/// <param name="entities"></param>
/// <param name="expression"></param>
/// <returns></returns>
public int UpdateByCondition(List <Te> entities, Expression <Func <Te, dynamic> > expression)
{
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(expression);
var paramList = visitor.Condition().Trim().Split(" ").ToList();
Type type = typeof(Te);
var sb = new StringBuilder();
foreach (var m in entities)
{
var set = string.Join(",", paramList.Select(c => $"{m.GetType().GetProperty(c).Name} = @{m.GetType().GetProperty(c).Name}"));
var where = $"{type.GetPropertiesKey().Name}=@{type.GetPropertiesKey().Name}";
sb.AppendFormat("update [{0}] set {1} where {2};", type.GetMappingName(), set, where);
}
return(this.Execute(sb.ToString(), entities));
}
public IEnumerable <T> QueryList <T>(Expression <Func <T, bool> > predicate) where T : BaseModel
{
List <T> list = new List <T>();
try
{
Type type = typeof(T);
PropertyInfo[] propertyArray = type.GetProperties();
string propertyStr = string.Join(",", propertyArray.Select(p => string.Format("[" + p.Name + "]")));
string sqlStr = string.Format("SELECT {0} FROM {1} WHERE 1=1 AND", propertyStr, typeof(T).Name);
ConditionBuilderVisitor visitor = new ConditionBuilderVisitor();
visitor.Visit(predicate);
sqlStr += visitor.Condition();//条件
using (SqlConnection conn = new SqlConnection(connectionStr))
{
SqlCommand comd = new SqlCommand(sqlStr, conn);
conn.Open();
SqlDataReader dr = comd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
var oObj = Activator.CreateInstance(type);
foreach (PropertyInfo item in propertyArray)
{
item.SetValue(oObj, dr[item.Name]);
}
list.Add((T)oObj);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(list);
}
//表达式目录树的拼装和拆解作为本周的家庭作业
public static void Show()
{
//ExpressionVisitor访问者类，Visit是一个入口，先判断，进一步的解析，然后
//1、lambada会区分参数和方法体，调度到更加专业的方法中解析；
//2、根据表达式的类型，调度到更加专业的方法中解析；
//3、默认根据旧的模式产生一个新的表达式目录树；
// 解析表达式目录树的时候，先从Visit进入，调度更加专业的方法进一步解析；
{
//修改表达式目录树
Expression <Func <int, int, int> > exp = (m, n) => m * n + 2;
OperationsVisitor visitor = new OperationsVisitor();
//visitor.Visit(exp);
Expression expNew = visitor.Modify(exp);
}
{
// 写成这样的表达式目录树怎么变成sql呢？
Expression <Func <People, bool> > lambda = x => x.Age > 5;
// select * from People where Age>5
new List <People>().AsQueryable().Where(x => x.Age > 5 && x.Id == 10);
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
Console.WriteLine("更加复杂一点");
{
//select * from People where Age>5 and Id>5
Expression <Func <People, bool> > lambda = x => x.Age > 5 &&
x.Id > 5 &&
x.Name.StartsWith("1") &&
x.Name.EndsWith("1") &&
x.Name.Contains("1");
string sql = string.Format("Delete From [{0}] WHERE {1}"
, typeof(People).Name
, " [Age]>5 AND [ID] >5"
);
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
//且或非
Expression <Func <People, bool> > lambda = x => x.Age > 5 && x.Name == "A" || x.Id > 5;
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 || (x.Name == "A" && x.Id > 5);
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => (x.Age > 5 || x.Name == "A") && x.Id > 5;
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
#region 表达式链接
{
// 把多个表达式拼接起来
Expression <Func <People, bool> > lambda1 = x => x.Age > 5;
Expression <Func <People, bool> > lambda2 = x => x.Id > 5;
Expression <Func <People, bool> > lambda3 = lambda1.And(lambda2);
Expression <Func <People, bool> > lambda4 = lambda1.Or(lambda2);
Expression <Func <People, bool> > lambda5 = lambda1.Not();
Do1(lambda3);
Do1(lambda4);
Do1(lambda5);
}
#endregion
}
public static void Show()
{
{
//修改表达式目录树
Expression <Func <int, int, int> > exp = (m, n) => m * n + 2 + 3 + Get(m);
//我想修改下，变成m*n-2
OperationsVisitor visitor = new OperationsVisitor();
//visitor.Visit(exp);
Expression expNew = visitor.Modify(exp);
}
{
var source = new List <People>().AsQueryable();//DbSet
var result = source.Where <People>(p => p.Age > 5)
.Where(p => p.Name.Equals("123"))
.Where(p => p.Age < 10)
//.ToList()
; //SELECT * FROM People Where Age> '5'
Expression <Func <People, bool> > lambda = x => x.Age > 5;
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
//ORM 把数据库映射到程序内存 通过操作对象来完成对数据库的管理
//屏蔽数据库，开发者完全不需要知道
//Linq To Object 实现IEnumerable
//Enumerable
//Queryable
//IQueryable
Expression <Func <People, bool> > lambda = x => x.Age > 5 && x.Id > 5 &&
x.Name.StartsWith("1") &&
x.Name.EndsWith("1") &&
x.Name.Contains("1");
string sql = string.Format("Delete From [{0}] WHERE {1}"
, typeof(People).Name
, " [Age]>5 AND [ID] >5"
);
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 && x.Name == "A" || x.Id > 5;
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => x.Age > 5 || (x.Name == "A" && x.Id > 5);
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
{
Expression <Func <People, bool> > lambda = x => (x.Age > 5 || x.Name == "A") && x.Id > 5;
ConditionBuilderVisitor vistor = new ConditionBuilderVisitor();
vistor.Visit(lambda);
Console.WriteLine(vistor.Condition());
}
#region 表达式链接
{
Expression <Func <People, bool> > lambda1 = x => x.Age > 5;
Expression <Func <People, bool> > lambda2 = x => x.Id > 5;
Expression <Func <People, bool> > lambda3 = lambda1.And(lambda2);
Expression <Func <People, bool> > lambda4 = lambda1.Or(lambda2);
Expression <Func <People, bool> > lambda5 = lambda1.Not();
Do1(lambda3);
Do1(lambda4);
Do1(lambda5);
}
#endregion
}
