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; } }
static public string Where <T>(Expression <Func <T, bool> > lambda) { ConditionBuilderVisitor vistor = new ConditionBuilderVisitor(); vistor.Visit(lambda); return(vistor.Condition()); }
private string BulidWhere(string sql, Expression expressionWhere) { ConditionBuilderVisitor vistor = new ConditionBuilderVisitor(); vistor.Visit(expressionWhere); return($"{sql} WHERE {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 }
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() { { //修改表达式目录树 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 }
//表达式目录树的拼装和拆解作为本周的家庭作业 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 }