private DbCommand CreateRemoveCommand(string removeFrom, Expression <Func <ShadowRow, bool> > predicate = null) { var sb = new StringBuilder() .Append("DELETE FROM ") .Append(removeFrom); var store = new QueryParametersStore(); if (predicate != null) { var w = new WhereQueryTranslator(store, new[] { predicate }); var whereSql = w.TranslateToSql(Expression.Empty()); if (!string.IsNullOrWhiteSpace(whereSql)) { sb.Append(" WHERE "); sb.Append(whereSql); } } var cmd = Connection.Value.CreateCommand(); cmd.CommandText = sb.ToString(); foreach (var param in store.GetParams()) { var p = cmd.CreateParameter(); p.ParameterName = param.Key; p.Value = param.Value; cmd.Parameters.Add(p); } return(cmd); }
private void VisitWhereStatements(List <MethodCallExpression> callChain, Type typeResult) { string[] names = new[] { nameof(q.Where), nameof(q.Distinct) }; List <MethodCallExpression> tmp = callChain.Where(p => names.Contains(p.Method.Name)).ToList(); WhereQueryTranslator s = new WhereQueryTranslator(typeResult); foreach (MethodCallExpression item in tmp) { s.Accept(item); callChain.Remove(item); } WherePart = s.Statement; }
protected override Expression VisitMethodCall(MethodCallExpression node) { var name = node.Method.Name; LambdaExpression lambdaExpression; switch (name) { case "Where": lambdaExpression = GetLambdaExpression(node); var whereTranslator = new WhereQueryTranslator(); WhereClause = whereTranslator.Translate(lambdaExpression); Visit(node.Arguments[0]); break; case "FirstOrDefault": FirstOrDefaultClause = "TOP 1 *"; Visit(node.Arguments[0]); break; case "ThenBy": case "OrderBy": case "OrderByDescending": case "ThenByDescending": var sortingType = name.Contains("Descending") ? SortingType.DESC : SortingType.ASC; lambdaExpression = GetLambdaExpression(node); var orderByTranslator = new OrderByQueryTranslator(sortingType); orderByClauses_.Add(orderByTranslator.Translate(lambdaExpression)); Visit(node.Arguments[0]); break; case "Select": lambdaExpression = GetLambdaExpression(node); var selectTranslator = new SelectQueryTranslator(); SelectClause = selectTranslator.Translate(lambdaExpression); Visit(node.Arguments[0]); break; default: break; } return(node); }
private DbCommand CreateUpdateCommand(string updateTarget, Dictionary <string, object> updateFields, Expression <Func <ShadowRow, bool> > predicate = null) { var sb = new StringBuilder() .Append("UPDATE ") .Append(updateTarget); var store = new QueryParametersStore(); var fieldPNameMap = new Dictionary <string, string>(); foreach (var updateField in updateFields) { fieldPNameMap[updateField.Key] = store.Append(updateField.Value); } { var setQueryBuilder = new StringBuilder(); var loopCounter = 0; foreach (var field in fieldPNameMap.Keys) { setQueryBuilder.Append($"{field}={fieldPNameMap[field]}"); var itIsLast = loopCounter == fieldPNameMap.Count - 1; if (!itIsLast) { setQueryBuilder.Append(", "); } loopCounter++; } sb.Append(" SET "); sb.Append(setQueryBuilder); } if (predicate != null) { var w = new WhereQueryTranslator(store, new[] { predicate }); var whereSql = w.TranslateToSql(Expression.Empty()); if (!string.IsNullOrWhiteSpace(whereSql)) { sb.Append(" WHERE "); sb.Append(whereSql); } } var cmd = Connection.Value.CreateCommand(); cmd.CommandText = sb.ToString(); foreach (var param in store.GetParams()) { var p = cmd.CreateParameter(); p.ParameterName = param.Key; p.Value = param.Value; cmd.Parameters.Add(p); } return(cmd); }
public override string TranslateToSql(Expression expr) { var externalPredicates = new List <Expression <Func <ShadowRow, bool> > >(); var itIsCount = false; var itIsFirst = false; var skipUsed = ExpressionsInternalToolkit.SkipIsUsed(expr); var takeUsed = ExpressionsInternalToolkit.TakeIsUsed(expr); #pragma warning disable CS0642 if (itIsCount = ExpressionsInternalToolkit.IsCountQueryableCall(expr)) { ; } else if (itIsFirst = ExpressionsInternalToolkit.IsFirstQueryableCall(expr) || ExpressionsInternalToolkit.IsFirstOrDefaultQueryableCall(expr)) { ; } #pragma warning restore CS0642 var selectOnlyTranslator = new SelectOnlyTranslator(); if (itIsCount) { _sb.Append("SELECT COUNT ("); _sb.Append(selectOnlyTranslator.TranslateToSql(expr)); _sb.Append(") FROM "); var mCall = expr as MethodCallExpression; if (mCall.Arguments.Count == 2) { var lambda = ExpressionsInternalToolkit.SkipUnary(mCall.Arguments[1]) as LambdaExpression; // skip Quote var lambdaTyped = Expression.Lambda <Func <ShadowRow, bool> >(lambda.Body, ExpressionBuilders.DefaultRowParameter); externalPredicates.Add(lambdaTyped); } } else if (itIsFirst && !skipUsed) { _sb.Append("SELECT TOP 1 "); _sb.Append(selectOnlyTranslator.TranslateToSql(expr)); _sb.Append(" FROM "); var mCall = expr as MethodCallExpression; if (mCall.Arguments.Count == 2) { var lambda = ExpressionsInternalToolkit.SkipUnary(mCall.Arguments[1]) as LambdaExpression; // skip Quote var lambdaTyped = Expression.Lambda <Func <ShadowRow, bool> >(lambda.Body, ExpressionBuilders.DefaultRowParameter); externalPredicates.Add(lambdaTyped); } } else { _sb.Append("SELECT "); _sb.Append(selectOnlyTranslator.TranslateToSql(expr)); _sb.Append(" FROM "); } _sb.Append(_sourceName); var whereLine = new WhereQueryTranslator(_queryParamsStore, externalPredicates.Any() ? externalPredicates.ToArray() : null) .TranslateToSql(expr); if (!string.IsNullOrWhiteSpace(whereLine)) { _sb.Append(" WHERE "); _sb.Append(whereLine); } if (!itIsCount) { var orderByLine = new OrderByQueryTranslator(_queryParamsStore) .TranslateToSql(expr); var orderByUsed = !string.IsNullOrWhiteSpace(orderByLine); if (orderByUsed) { _sb.Append(" ORDER BY "); _sb.Append(orderByLine); } if (skipUsed) { if (!orderByUsed) { _sb.Append(" ORDER BY (SELECT NULL)"); } _sb.Append(" OFFSET "); var skipCount = ExpressionsInternalToolkit.GetSkipCount(expr); _sb.Append(_queryParamsStore.Append(skipCount)); _sb.Append(" ROWS"); } if (takeUsed) { if (!skipUsed) { if (!orderByUsed) { _sb.Append(" ORDER BY (SELECT NULL)"); } _sb.Append(" OFFSET 0 ROWS"); } _sb.Append(" FETCH NEXT "); var takeCount = ExpressionsInternalToolkit.GetTakeCount(expr); _sb.Append(_queryParamsStore.Append(takeCount)); _sb.Append(" ROW ONLY"); } } if (itIsFirst && skipUsed) { _sb.Append(" FETCH NEXT 1 ROW ONLY"); } return(_sb.ToString()); }