// Queryable's "single value" standard query operators call this method. // It is also called from QueryableTerraServerData.GetEnumerator(). public TResult Execute <TResult>(Expression expression) { //bool IsEnumerable = (typeof(TResult).Name == "IEnumerable`1"); var obj = DataQueryContext.Execute <TResult>(expression); var resultType = typeof(TResult); if (resultType.IsValueType || resultType == typeof(string)) { return(DataConvert.Convert <TResult>(obj));//例如求平均数,TResult可能是double,而实际得到的结果为int,int不能直接强制转换为double. } return((TResult)obj); }
internal HavingHandler(MethodCallExpression node, DataQueryContext context) { this.Node = node; this.Context = context; }
internal static object Execute <TResult>(Expression expression) { bool isEnumerable = (typeof(TResult).Name == "IEnumerable`1"); var returnType = typeof(TResult); if (!(expression is MethodCallExpression)) { throw new InvalidProgramException("No query over the data source was specified."); } DataQueryContext context = new DataQueryContext(); context.Translate(expression); var modelType = GetModelType(expression); var tableName = GetTableName(modelType); PropertyInfo primaryProperty = modelType.GetProperties().FirstOrDefault((property) => { var attributes = (ColumnAttribute[])property.GetCustomAttributes(typeof(ColumnAttribute), false); var attrPrimary = attributes.FirstOrDefault(attr => attr.IsPrimaryKey); return(attrPrimary != null); }); var primaryKey = primaryProperty.Name; context.MethodList.Reverse(); var array = new List <List <IMethodHandler> >(); var _list = new List <IMethodHandler>(); for (var i = 0; i < context.MethodList.Count; i++) { var node = context.MethodList[i]; if (node is GroupByHandler)//GroupByHandler后面必然是SelectHandler,所以在SelectHandler时添加. { continue; } if (node is SkipHandler) { if (_list.Count > 0) { array.Add(_list); } _list = new List <IMethodHandler>(); _list.Add(node); if (!(i < context.MethodList.Count - 1 && (context.MethodList[i + 1] is TakeHandler))) { array.Add(_list); _list = new List <IMethodHandler>(); } } else if (node is TakeHandler) { var lastNode = _list.LastOrDefault(); if (lastNode != null && (lastNode is SkipHandler)) { _list.Add(node); array.Add(_list); _list = new List <IMethodHandler>(); } else { if (_list.Count > 0) { array.Add(_list); } _list = new List <IMethodHandler>(); _list.Add(node); array.Add(_list); _list = new List <IMethodHandler>(); } } else if (node is SelectHandler) { var mce = node.Node.Arguments[0] as MethodCallExpression; if (mce != null && mce.Method.Name == "GroupBy") { //如: //group obj by obj.Content into g select g.Min(item=>item.Id); //group obj by obj.Content into g select g; if (_list.Count > 0) { array.Add(_list); } _list = new List <IMethodHandler>(); _list.Add(new GroupByHandler(mce, context)); _list.Add(node); array.Add(_list); _list = new List <IMethodHandler>(); } else { if (_list.Count > 0) { array.Add(_list); } _list = new List <IMethodHandler>(); } } else { _list.Add(node); if (i == context.MethodList.Count - 1) { array.Add(_list); } } } var sqlQuery = string.Empty; if (array.Count == 0) { sqlQuery = "select * from " + tableName + ""; } else { sqlQuery = ExecuteMethod(array, 0, tableName, primaryKey, context); } Database db = null; var customAttributes = modelType.GetCustomAttributes(typeof(System.Data.Linq.Mapping.DatabaseAttribute), false); if (customAttributes.Length > 0) { var dbName = (customAttributes[0] as System.Data.Linq.Mapping.DatabaseAttribute).Name; if (!string.IsNullOrWhiteSpace(dbName)) { db = DatabaseFactory.CreateDatabase(dbName); } } if (db == null) { db = DatabaseManager.CreateDatabase(); } var ds = db.ExecuteDataSet(CommandType.Text, sqlQuery); var dt = ds.Tables[0]; //if (dt.Rows.Count == 1 && dt.Columns.Count == 1)//聚合查询,如Count(*) var colIndex = 0; if (dt.Columns[0].ColumnName.ToLower() == "rownumber")//groupby...skip...take { colIndex = 1; } if (returnType.IsValueType || returnType == typeof(string))//聚合查询,如Count(*) { return(dt.Rows[0][colIndex]); } //returnType可能是匿名对象,如select new{},然后FirstOrDefault,对于匿名对象,returnType.GetGenericArguments()数量等于匿名对象的属性个数. //if (returnType == modelType)//如First、FirstOrDefault只查询第一条,结果是一个实体,不是集合. //{ // return dt.ToEntities<TResult>().FirstOrDefault(); //} //if (!isEnumerable)//如First、FirstOrDefault只查询第一条,结果是一个实体,不是集合. //{ //} //var executor = Activator.CreateInstance(typeof(Executor<>).MakeGenericType(modelType), dt);//executor是IEnumerable<TModel> Type genericType = null; if (isEnumerable) { //var genericTypeArray = returnType.GetGenericArguments(); //if (genericTypeArray.Length > 0) //{ // genericType = genericTypeArray[0]; //} genericType = returnType.GetGenericArguments()[0]; } else//如First、FirstOrDefault只查询第一条,结果是一个实体,不是集合. { genericType = returnType; } if (genericType.Name.StartsWith("<>") && genericType.Name.Contains("AnonymousType")) //匿名类型.如select new{A=obj.Id} { genericType = modelType; //DataTable要转成modelType的集合,没办法转成匿名类型的集合. } var executor = Activator.CreateInstance(typeof(Executor <>).MakeGenericType(genericType), dt); //executor是IEnumerable<TResult> if (genericType.IsValueType || genericType == typeof(string) || genericType.Name == "IGrouping`2") { return(executor); } //对executor(已从数据库中取出的数据)进一步处理,如实现new{}运算、实现排序等,如from obj in Employee.Enities where obj.Id>0 select new{A=obj.Id,B=obj.TrueName} var enumerator = executor as IEnumerable; var q = enumerator.AsQueryable(); ExpressionTreeModifier treeCopier = new ExpressionTreeModifier(q); Expression newExpressionTree = treeCopier.CopyAndModify(expression); if (isEnumerable) { return(q.Provider.CreateQuery(newExpressionTree)); } else { return(q.Provider.Execute(newExpressionTree));//如First、FirstOrDefault } }
private static string ExecuteMethod(List <List <IMethodHandler> > array, int index, string tableName, string primaryKey, DataQueryContext context) { string sqlQuery = null; for (var i = 0; i < array.Count; i++) { sqlQuery = ToSql(array[i], i, tableName, primaryKey, sqlQuery, context); } return(sqlQuery); }
private static string ToSql(List <IMethodHandler> methods, int index, string tableName, string primaryKey, string innerSql, DataQueryContext context) { if (methods.Count == 0) { return(null); } if (methods[0] is GroupByHandler) { var tableAlias = "t" + context.tIndex + ""; context.tIndex++; var groupByHandler = methods[0] as GroupByHandler; var selectHandler = methods[1] as SelectHandler; var lambdaExpression = ((UnaryExpression)selectHandler.Node.Arguments[1]).Operand as LambdaExpression; var field = "" + tableAlias + "." + groupByHandler + ""; context.overOrderby = groupByHandler.ToString(); if (lambdaExpression.Body is MethodCallExpression) { var mce = lambdaExpression.Body as MethodCallExpression; if (mce.Arguments.Count == 2) { var me = (mce.Arguments[1] as LambdaExpression).Body as MemberExpression; field = "" + mce.Method.Name + "(" + tableAlias + "." + me.Member.Name + ") [value]"; } else { field = "" + mce.Method.Name + "(*) [value]";//如count } context.overOrderby = "[value]"; } string sql = "select " + field + " from (" + innerSql + ") " + tableAlias + " group by " + tableAlias + "." + groupByHandler + " "; return(sql); } if (methods[0] is SkipHandler) { var skipHandler = methods[0] as SkipHandler; TakeHandler takeHandler = null; if (methods.Count > 1 && methods[1] is TakeHandler) { takeHandler = methods[1] as TakeHandler; } var tableAlias = "t" + context.tIndex + ""; context.tIndex++; var _orderby = context.overOrderby; if (string.IsNullOrEmpty(_orderby) || string.IsNullOrEmpty(_orderby.Trim())) { _orderby = primaryKey; } var innerQuery = innerSql; if (string.IsNullOrEmpty(innerQuery) || string.IsNullOrEmpty(innerQuery.Trim())) { innerQuery = tableName; } else { innerQuery = "(" + innerSql + ")"; } var sql = "select ROW_NUMBER() Over(order by " + tableAlias + "." + _orderby + ") as RowNumber,* from " + innerQuery + " " + tableAlias + ""; tableAlias = "t" + context.tIndex + ""; context.tIndex++; sql = "select * from (" + sql + ") " + tableAlias + " where " + tableAlias + ".RowNumber"; if (skipHandler != null && takeHandler != null) { var skip = Convert.ToInt32(skipHandler.ToString()); var take = Convert.ToInt32(takeHandler.ToString()); int rowNumberStart = skip + 1; int rowNumberEnd = skip + take; sql += " between " + rowNumberStart + " and " + rowNumberEnd + ""; } else if (skipHandler != null && takeHandler == null) { var skip = Convert.ToInt32(skipHandler.ToString()); int rowNumberStart = skip; sql += "> " + rowNumberStart + ""; } else { var take = Convert.ToInt32(takeHandler.ToString()); int rowNumberEnd = take + 1; sql += "< " + rowNumberEnd + ""; } return(sql); } if (methods[0] is TakeHandler) { var takeHandler = methods[0] as TakeHandler; var tableAlias = "t" + context.tIndex + ""; context.tIndex++; var _orderby = context.overOrderby; if (string.IsNullOrEmpty(_orderby) || string.IsNullOrEmpty(_orderby.Trim())) { _orderby = primaryKey; } var innerQuery = innerSql; if (string.IsNullOrEmpty(innerQuery) || string.IsNullOrEmpty(innerQuery.Trim())) { innerQuery = tableName; } else { innerQuery = "(" + innerSql + ")"; } var sql = "select ROW_NUMBER() Over(order by " + _orderby + ") as RowNumber,* from " + innerQuery + " " + tableAlias + ""; tableAlias = "t" + context.tIndex + ""; context.tIndex++; sql = "select * from (" + sql + ") " + tableAlias + " where " + tableAlias + ".RowNumber"; var take = Convert.ToInt32(takeHandler.ToString()); int rowNumberEnd = take + 1; sql += "< " + rowNumberEnd + ""; return(sql); } var where = string.Empty; //where //context.WhereList.Reverse(); var whereList = methods.Where(node => node is WhereHandler); if (whereList.Count() > 0) { for (var i = 0; i < whereList.Count(); i++) { if (i > 0) { where += " and"; } where += " " + whereList.ElementAt(i); } } var firstHandler = methods.FirstOrDefault(node => node is FirstHandler); if (firstHandler != null) { var _first = firstHandler as FirstHandler; if (!string.IsNullOrWhiteSpace(_first.Where))//如Lovewall.Entities.FirstOrDefault(item=>item.Id==100); { if (!string.IsNullOrWhiteSpace(where)) { where += " and"; } where += " " + _first.Where; } } //order by var orderby = string.Empty; //调整Order顺序(将多个OrderBy之间倒序.OrderBy与ThenBy间顺序保持不变),如: //"orderby A descending,B orderby C"调整后sql变为"order by C asc,A desc,B asc" var orderbyList = methods.Where(node => node is OrderByHandler || node is ThenByHandler).ToList(); List <List <IMethodHandler> > orderByArray = new List <List <IMethodHandler> >(); List <IMethodHandler> orderGroup = null; for (var i = 0; i < orderbyList.Count; i++) { var order = orderbyList[i]; if (order is OrderByHandler) { orderGroup = new List <IMethodHandler>(); orderGroup.Add(order); if (i == orderbyList.Count - 1) { orderByArray.Add(orderGroup); } } else if (order is ThenByHandler) { orderGroup.Add(order); orderByArray.Add(orderGroup); } } orderByArray.Reverse(); foreach (var array in orderByArray) { foreach (var order in array) { orderby += "," + order + ""; } } if (!string.IsNullOrWhiteSpace(orderby)) { orderby = orderby.Substring(1); } //if (orderbyList.Count() > 0) //{ // for (var i = 0; i < orderbyList.Count(); i++) // { // orderby += orderbyList.ElementAt(i); // if (i < orderbyList.Count() - 1) // { // orderby += ","; // } // } //} if (!string.IsNullOrWhiteSpace(orderby)) { context.overOrderby = orderby; } var having = string.Empty; var havingList = methods.Where(node => node is HavingHandler); if (havingList.Count() > 0) {//未实现 } var sqlQuery = string.Empty; //var _fields = "*"; var _fields = "top 100 percent *"; //如:var query = from obj in Lovewall.Entities orderby obj.Id descending select new { A = obj.Id, B = obj.Content, C = obj.DateCreated };var q=query.Count();//执行Count()出现嵌套查询,所以用top 100 percent *,否则出错 if (string.IsNullOrWhiteSpace(context.overOrderby)) //context.overOrderby未设置过,说明是嵌套查询中的最底层. { context.overOrderby = primaryKey; //按照系统的Linq to Sql是表中全部字段,如select ROW_NUMBER() Over(order by t0.Id,t0.Content) as RowNumber,这里只用主键排序就够了. } var aggregateHandler = methods.FirstOrDefault(node => node is AggregateHandler); //var firstHandler = methods.FirstOrDefault(node => node is FirstHandler); if (aggregateHandler != null) { _fields = "" + aggregateHandler.ToString() + " as [value]"; context.overOrderby = "[value]"; } else if (firstHandler != null) { _fields = firstHandler.ToString(); context.overOrderby = null;//取firstHandler,返回值是一个实体,不可能是一个集合,所以不可能再查询了. } var _tableAlias = "t" + context.tIndex + ""; context.tIndex++; _fields = _fields.ToLower(); sqlQuery = "select "; sqlQuery += _fields; sqlQuery += " from "; var _from = tableName; if (index > 0 && !string.IsNullOrWhiteSpace(innerSql)) { _from = "(" + innerSql + ")"; } sqlQuery += "" + _from + " " + _tableAlias + ""; if (!string.IsNullOrWhiteSpace(where)) { sqlQuery += " where " + where + ""; } if (!_fields.StartsWith("count(") && !_fields.StartsWith("min(") && !_fields.StartsWith("max(") && !_fields.StartsWith("sum(") && !_fields.StartsWith("avg(")) { if (!string.IsNullOrWhiteSpace(orderby)) { sqlQuery += " order by " + _tableAlias + "." + orderby + ""; } } return(sqlQuery); }
public object Execute(Expression expression) { //return DataQueryContext.Execute(expression,false); return(DataQueryContext.Execute <object>(expression)); }