public virtual string GenerateUpdate <T>(object item, Expression <Func <T, bool> > where, out IList <QueryInfo> queryParameters) where T : class { var tableName = DotEntityDb.GetTableNameForType <T>(); var builder = new StringBuilder(); // convert the query parms into a SQL string and dynamic property object builder.Append("UPDATE "); builder.Append(tableName.ToEnclosed()); builder.Append(" SET "); Dictionary <string, object> updateValueMap = QueryParserUtilities.ParseObjectKeyValues(item); var updateString = string.Join(",", updateValueMap.Select(x => $"{x.Key.ToEnclosed()} = @{x.Key}")); builder.Append(updateString); var parser = new ExpressionTreeParser(); var whereString = parser.GetWhereString(where); queryParameters = parser.QueryInfoList; if (!string.IsNullOrEmpty(whereString)) { //update where string to handle common parameters var commonKeys = updateValueMap.Keys.Intersect(queryParameters.Select(x => x.PropertyName)); whereString = commonKeys.Aggregate(whereString, (current, ck) => current.Replace($"@{ck}", $"@{ck}2")); } queryParameters = MergeParameters(ToQueryInfos(updateValueMap), queryParameters); if (!string.IsNullOrEmpty(whereString)) { builder.Append(" WHERE " + whereString); } return(builder.ToString().Trim() + ";"); }
public virtual string GenerateDelete <T>(Expression <Func <T, bool> > where, out IList <QueryInfo> parameters) where T : class { var tableName = DotEntityDb.GetTableNameForType <T>(); var parser = new ExpressionTreeParser(); var whereString = parser.GetWhereString(where).Trim(); parameters = parser.QueryInfoList; return($"DELETE FROM {tableName.ToEnclosed()} WHERE {whereString};"); }
public virtual string GenerateCount <T>(IList <Expression <Func <T, bool> > > @where, out IList <QueryInfo> parameters) where T : class { parameters = new List <QueryInfo>(); var tableName = DotEntityDb.GetTableNameForType <T>(); var whereString = ""; if (where != null) { var parser = new ExpressionTreeParser(); var whereStringBuilder = new List <string>(); foreach (var wh in where) { whereStringBuilder.Add(parser.GetWhereString(wh)); } parameters = parser.QueryInfoList; whereString = string.Join(" AND ", whereStringBuilder).Trim(); } return($"SELECT COUNT(*) FROM {tableName.ToEnclosed()} WHERE {whereString};"); }
public virtual string GenerateSelectWithTotalMatchingCount <T>(out IList <QueryInfo> parameters, List <Expression <Func <T, bool> > > @where = null, Dictionary <Expression <Func <T, object> >, RowOrder> orderBy = null, int page = 1, int count = Int32.MaxValue) where T : class { parameters = new List <QueryInfo>(); var builder = new StringBuilder(); var tableName = DotEntityDb.GetTableNameForType <T>(); var whereString = ""; if (where != null) { var parser = new ExpressionTreeParser(); var whereStringBuilder = new List <string>(); foreach (var wh in where) { whereStringBuilder.Add(parser.GetWhereString(wh)); } parameters = parser.QueryInfoList; whereString = string.Join(" AND ", whereStringBuilder).Trim(); } var orderByStringBuilder = new List <string>(); var orderByString = ""; if (orderBy != null) { var parser = new ExpressionTreeParser(); foreach (var ob in orderBy) { orderByStringBuilder.Add(parser.GetOrderByString(ob.Key) + (ob.Value == RowOrder.Descending ? " DESC" : "")); } orderByString = string.Join(", ", orderByStringBuilder).Trim(','); } var paginatedSelect = PaginateOrderByString(orderByString, page, count, out string newWhereString); if (paginatedSelect != string.Empty) { paginatedSelect = "," + paginatedSelect; orderByString = string.Empty; } // make the query now builder.Append($"SELECT {QueryParserUtilities.GetSelectColumnString(new List<Type>() { typeof(T) })}{paginatedSelect} FROM "); builder.Append(tableName.ToEnclosed()); if (!string.IsNullOrEmpty(whereString)) { builder.Append(" WHERE " + whereString); } if (!string.IsNullOrEmpty(orderByString)) { builder.Append(" ORDER BY " + orderByString); } var query = builder.ToString().Trim(); if (paginatedSelect != string.Empty) { //wrap everything query = $"SELECT * FROM ({query}) AS __PAGINATEDRESULT__ WHERE {newWhereString};"; } //and the count query query = query + $"{Environment.NewLine}SELECT COUNT(*) FROM {tableName.ToEnclosed()}" + (string.IsNullOrEmpty(whereString) ? "" : $" WHERE {whereString}") + ";"; return(query); }
private static string GetOperator(ExpressionType type, ExpressionTreeParser parser) { var typeToCheck = type; if (parser._notCount > 0) { switch (type) { case ExpressionType.Equal: typeToCheck = ExpressionType.NotEqual; break; case ExpressionType.NotEqual: typeToCheck = ExpressionType.Equal; break; case ExpressionType.LessThan: typeToCheck = ExpressionType.GreaterThan; break; case ExpressionType.LessThanOrEqual: typeToCheck = ExpressionType.GreaterThanOrEqual; break; case ExpressionType.GreaterThan: typeToCheck = ExpressionType.LessThan; break; case ExpressionType.GreaterThanOrEqual: typeToCheck = ExpressionType.LessThanOrEqual; break; case ExpressionType.AndAlso: case ExpressionType.And: typeToCheck = ExpressionType.Or; break; case ExpressionType.Or: case ExpressionType.OrElse: typeToCheck = ExpressionType.And; break; } } switch (typeToCheck) { case ExpressionType.Equal: return("="); case ExpressionType.NotEqual: return("!="); case ExpressionType.LessThan: return("<"); case ExpressionType.LessThanOrEqual: return("<="); case ExpressionType.GreaterThan: return(">"); case ExpressionType.GreaterThanOrEqual: return(">="); case ExpressionType.AndAlso: case ExpressionType.And: return("AND"); case ExpressionType.Or: case ExpressionType.OrElse: return("OR"); case ExpressionType.Default: return(string.Empty); default: throw new NotImplementedException(); } }