/// <summary>
/// Build the final `query statement and parameters`
/// </summary>
/// <param name="queryProperties"></param>
/// <param name="sqlBuilder"></param>
/// <param name="conditions"></param>
/// <param name="qLevel">Parameters of the ranking</param>
/// <remarks>
/// Support `group conditions` syntax
/// </remarks>
private void BuildQuerySql(IList <QueryExpression> queryProperties,
ref StringBuilder sqlBuilder, ref List <KeyValuePair <string, object> > conditions, ref int qLevel)
{
foreach (var expr in queryProperties)
{
if (!string.IsNullOrEmpty(expr.LinkingOperator))
{
if (sqlBuilder.Length > 0)
{
sqlBuilder.Append(" ");
}
sqlBuilder
.Append(expr.LinkingOperator)
.Append(" ");
}
switch (expr)
{
case QueryParameterExpression qpExpr:
var tableName = TableName;
string columnName;
if (qpExpr.NestedProperty)
{
var joinProperty = SqlJoinProperties.First(x => x.PropertyName == qpExpr.PropertyName);
tableName = joinProperty.TableAlias;
columnName = joinProperty.ColumnName;
}
else
{
columnName = SqlProperties.First(x => x.PropertyName == qpExpr.PropertyName).ColumnName;
}
if (qpExpr.PropertyValue == null)
{
sqlBuilder.AppendFormat("{0}.{1} {2} NULL", tableName, columnName, qpExpr.QueryOperator == "=" ? "IS" : "IS NOT");
}
else
{
var vKey = string.Format("{0}_p{1}", qpExpr.PropertyName, qLevel); //Handle multiple uses of a field
sqlBuilder.AppendFormat("{0}.{1} {2} @{3}", tableName, columnName, qpExpr.QueryOperator, vKey);
conditions.Add(new KeyValuePair <string, object>(vKey, qpExpr.PropertyValue));
}
qLevel++;
break;
case QueryBinaryExpression qbExpr:
var nSqlBuilder = new StringBuilder();
var nConditions = new List <KeyValuePair <string, object> >();
BuildQuerySql(qbExpr.Nodes, ref nSqlBuilder, ref nConditions, ref qLevel);
if (qbExpr.Nodes.Count == 1) //Handle `grouping brackets`
{
sqlBuilder.Append(nSqlBuilder);
}
else
{
sqlBuilder.AppendFormat("({0})", nSqlBuilder);
}
conditions.AddRange(nConditions);
break;
}
}
}
private void AppendWherePredicateQuery(SqlQuery sqlQuery, Expression <Func <TEntity, bool> > predicate, QueryType queryType)
{
IDictionary <string, object> dictionaryParams = new Dictionary <string, object>();
if (predicate != null)
{
// WHERE
var queryProperties = new List <QueryParameter>();
FillQueryProperties(predicate.Body, ExpressionType.Default, ref queryProperties);
sqlQuery.SqlBuilder.Append("WHERE ");
for (var i = 0; i < queryProperties.Count; i++)
{
var item = queryProperties[i];
var tableName = TableName;
string columnName;
if (item.NestedProperty)
{
var joinProperty = SqlJoinProperties.First(x => x.PropertyName == item.PropertyName);
tableName = joinProperty.TableAlias;
columnName = joinProperty.ColumnName;
}
else
{
columnName = SqlProperties.First(x => x.PropertyName == item.PropertyName).ColumnName;
}
if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
{
sqlQuery.SqlBuilder.Append(item.LinkingOperator + " ");
}
if (item.PropertyValue == null)
{
sqlQuery.SqlBuilder.Append(tableName + "." + columnName + " " + (item.QueryOperator == "=" ? "IS" : "IS NOT") + " NULL ");
}
else
{
sqlQuery.SqlBuilder.Append(tableName + "." + columnName + " " + item.QueryOperator + " @" + item.PropertyName + " ");
}
dictionaryParams[item.PropertyName] = item.PropertyValue;
}
if (LogicalDelete && queryType == QueryType.Select)
{
sqlQuery.SqlBuilder.Append("AND " + TableName + "." + StatusPropertyName + " != " + LogicalDeleteValue + " ");
}
}
else
{
if (LogicalDelete && queryType == QueryType.Select)
{
sqlQuery.SqlBuilder.Append("WHERE " + TableName + "." + StatusPropertyName + " != " + LogicalDeleteValue + " ");
}
}
if (LogicalDelete && HasUpdatedAt && queryType == QueryType.Delete)
{
dictionaryParams.Add(UpdatedAtPropertyMetadata.ColumnName, DateTime.UtcNow);
}
sqlQuery.SetParam(dictionaryParams);
}
/// <summary>
/// Get query properties
/// </summary>
/// <param name="expr">The expression.</param>
/// <param name="linkingType">Type of the linking.</param>
private QueryExpression GetQueryProperties(Expression expr, ExpressionType linkingType)
{
var isNotUnary = false;
if (expr is UnaryExpression unaryExpression)
{
if (unaryExpression.NodeType == ExpressionType.Not && unaryExpression.Operand is MethodCallExpression)
{
expr = unaryExpression.Operand;
isNotUnary = true;
}
}
if (expr is MethodCallExpression methodCallExpression)
{
var methodName = methodCallExpression.Method.Name;
var exprObj = methodCallExpression.Object;
MethodLabel:
switch (methodName)
{
case "Contains":
{
if (exprObj != null &&
exprObj.NodeType == ExpressionType.MemberAccess &&
exprObj.Type == typeof(string))
{
methodName = "StringContains";
goto MethodLabel;
}
var propertyName = ExpressionHelper.GetPropertyNamePath(methodCallExpression, out var isNested);
if (!SqlProperties.Select(x => x.PropertyName).Contains(propertyName) &&
!SqlJoinProperties.Select(x => x.PropertyName).Contains(propertyName))
{
throw new NotSupportedException("predicate can't parse");
}
var propertyValue = ExpressionHelper.GetValuesFromCollection(methodCallExpression);
var opr = ExpressionHelper.GetMethodCallSqlOperator(methodName, isNotUnary);
var link = ExpressionHelper.GetSqlOperator(linkingType);
return(new QueryParameterExpression(link, propertyName, propertyValue, opr, isNested));
}
case "StringContains":
case "StartsWith":
case "EndsWith":
{
if (exprObj == null ||
exprObj.NodeType != ExpressionType.MemberAccess ||
exprObj.Type != typeof(string))
{
goto default;
}
bool isNested = false;
var propertyName = ExpressionHelper.GetPropertyNamePath(exprObj, out isNested);
if (!SqlProperties.Select(x => x.PropertyName).Contains(propertyName) &&
!SqlJoinProperties.Select(x => x.PropertyName).Contains(propertyName))
{
throw new NotSupportedException("predicate can't parse");
}
var propertyValue = ExpressionHelper.GetValuesFromStringMethod(methodCallExpression);
var likeValue = ExpressionHelper.GetSqlLikeValue(methodName, propertyValue);
var opr = ExpressionHelper.GetMethodCallSqlOperator(methodName, isNotUnary);
var link = ExpressionHelper.GetSqlOperator(linkingType);
return(new QueryParameterExpression(link, propertyName, likeValue, opr, isNested));
}
default:
throw new NotSupportedException($"'{methodName}' method is not supported");
}
}
if (expr is BinaryExpression binaryExpression)
{
if (binaryExpression.NodeType != ExpressionType.AndAlso && binaryExpression.NodeType != ExpressionType.OrElse)
{
var propertyName = ExpressionHelper.GetPropertyNamePath(binaryExpression, out var isNested);
if (!SqlProperties.Select(x => x.PropertyName).Contains(propertyName) &&
!SqlJoinProperties.Select(x => x.PropertyName).Contains(propertyName))
{
throw new NotSupportedException("predicate can't parse");
}
var propertyValue = ExpressionHelper.GetValue(binaryExpression.Right);
var opr = ExpressionHelper.GetSqlOperator(binaryExpression.NodeType);
var link = ExpressionHelper.GetSqlOperator(linkingType);
return(new QueryParameterExpression(link, propertyName, propertyValue, opr, isNested));
}
var leftExpr = GetQueryProperties(binaryExpression.Left, ExpressionType.Default);
var rightExpr = GetQueryProperties(binaryExpression.Right, binaryExpression.NodeType);
switch (leftExpr)
{
case QueryParameterExpression lQPExpr:
if (!string.IsNullOrEmpty(lQPExpr.LinkingOperator) && !string.IsNullOrEmpty(rightExpr.LinkingOperator)) // AND a AND B
{
switch (rightExpr)
{
case QueryBinaryExpression rQBExpr:
if (lQPExpr.LinkingOperator == rQBExpr.Nodes.Last().LinkingOperator) // AND a AND (c AND d)
{
var nodes = new QueryBinaryExpression
{
LinkingOperator = leftExpr.LinkingOperator,
Nodes = new List <QueryExpression> {
leftExpr
}
};
rQBExpr.Nodes[0].LinkingOperator = rQBExpr.LinkingOperator;
nodes.Nodes.AddRange(rQBExpr.Nodes);
leftExpr = nodes;
rightExpr = null;
// AND a AND (c AND d) => (AND a AND c AND d)
}
break;
}
}
break;
case QueryBinaryExpression lQBExpr:
switch (rightExpr)
{
case QueryParameterExpression rQPExpr:
if (rQPExpr.LinkingOperator == lQBExpr.Nodes.Last().LinkingOperator) //(a AND b) AND c
{
lQBExpr.Nodes.Add(rQPExpr);
rightExpr = null;
//(a AND b) AND c => (a AND b AND c)
}
break;
case QueryBinaryExpression rQBExpr:
if (lQBExpr.Nodes.Last().LinkingOperator == rQBExpr.LinkingOperator) // (a AND b) AND (c AND d)
{
if (rQBExpr.LinkingOperator == rQBExpr.Nodes.Last().LinkingOperator) // AND (c AND d)
{
rQBExpr.Nodes[0].LinkingOperator = rQBExpr.LinkingOperator;
lQBExpr.Nodes.AddRange(rQBExpr.Nodes);
// (a AND b) AND (c AND d) => (a AND b AND c AND d)
}
else
{
lQBExpr.Nodes.Add(rQBExpr);
// (a AND b) AND (c OR d) => (a AND b AND (c OR d))
}
rightExpr = null;
}
break;
}
break;
}
var nLinkingOperator = ExpressionHelper.GetSqlOperator(linkingType);
if (rightExpr == null)
{
leftExpr.LinkingOperator = nLinkingOperator;
return(leftExpr);
}
return(new QueryBinaryExpression
{
NodeType = QueryExpressionType.Binary,
LinkingOperator = nLinkingOperator,
Nodes = new List <QueryExpression> {
leftExpr, rightExpr
},
});
}
return(GetQueryProperties(ExpressionHelper.GetBinaryExpression(expr), linkingType));
}
/// <summary>
/// Fill query properties
/// </summary>
/// <param name="expr">The expression.</param>
/// <param name="linkingType">Type of the linking.</param>
/// <param name="queryProperties">The query properties.</param>
private void FillQueryProperties(Expression expr, ExpressionType linkingType, ref List <QueryParameter> queryProperties)
{
if (expr is MethodCallExpression body)
{
var innerBody = body;
var methodName = innerBody.Method.Name;
switch (methodName)
{
case "Contains":
{
var propertyName = ExpressionHelper.GetPropertyNamePath(innerBody, out var isNested);
if (!SqlProperties.Select(x => x.PropertyName).Contains(propertyName) && !SqlJoinProperties.Select(x => x.PropertyName).Contains(propertyName))
{
throw new NotSupportedException("predicate can't parse");
}
var propertyValue = ExpressionHelper.GetValuesFromCollection(innerBody);
var opr = ExpressionHelper.GetMethodCallSqlOperator(methodName);
var link = ExpressionHelper.GetSqlOperator(linkingType);
queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr, isNested));
break;
}
default:
throw new NotSupportedException($"'{methodName}' method is not supported");
}
}
else if (expr is BinaryExpression)
{
var innerbody = (BinaryExpression)expr;
if (innerbody.NodeType != ExpressionType.AndAlso && innerbody.NodeType != ExpressionType.OrElse)
{
var propertyName = ExpressionHelper.GetPropertyNamePath(innerbody, out var isNested);
if (!SqlProperties.Select(x => x.PropertyName).Contains(propertyName) && !SqlJoinProperties.Select(x => x.PropertyName).Contains(propertyName))
{
throw new NotSupportedException("predicate can't parse");
}
var propertyValue = ExpressionHelper.GetValue(innerbody.Right);
var opr = ExpressionHelper.GetSqlOperator(innerbody.NodeType);
var link = ExpressionHelper.GetSqlOperator(linkingType);
queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr, isNested));
}
else
{
FillQueryProperties(innerbody.Left, innerbody.NodeType, ref queryProperties);
FillQueryProperties(innerbody.Right, innerbody.NodeType, ref queryProperties);
}
}
else
{
FillQueryProperties(ExpressionHelper.GetBinaryExpression(expr), linkingType, ref queryProperties);
}
}
/// <summary>
/// Build the final `query statement and parameters`
/// </summary>
/// <param name="queryProperties"></param>
/// <param name="sqlBuilder"></param>
/// <param name="conditions"></param>
/// <param name="qLevel">Parameters of the ranking</param>
/// <remarks>
/// Support `group conditions` syntax
/// </remarks>
private void BuildQuerySql(IList <QueryExpression> queryProperties,
ref StringBuilder sqlBuilder, ref List <KeyValuePair <string, object?> > conditions, ref int qLevel)
{
foreach (var expr in queryProperties)
{
if (!string.IsNullOrEmpty(expr.LinkingOperator))
{
if (sqlBuilder.Length > 0)
{
sqlBuilder.Append(" ");
}
sqlBuilder
.Append(expr.LinkingOperator)
.Append(" ");
}
switch (expr)
{
case QueryParameterExpression qpExpr:
var tableName = TableName;
string columnName;
if (qpExpr.NestedProperty)
{
var joinProperty = SqlJoinProperties.First(x => x.PropertyName == qpExpr.PropertyName);
tableName = string.IsNullOrEmpty(joinProperty.TableAlias) ? joinProperty.TableName : joinProperty.TableAlias;
columnName = joinProperty.ColumnName;
}
else
{
columnName = SqlProperties.First(x => x.PropertyName == qpExpr.PropertyName).ColumnName;
}
if (qpExpr.PropertyValue == null)
{
sqlBuilder.AppendFormat("{0}.{1} {2} NULL", tableName, columnName, qpExpr.QueryOperator == "=" ? "IS" : "IS NOT");
}
else
{
var vKey = string.Format("{0}_p{1}", qpExpr.PropertyName, qLevel); //Handle multiple uses of a field
sqlBuilder.AppendFormat("{0}.{1} {2} " + ParameterSymbol + "{3}", tableName, columnName, qpExpr.QueryOperator, vKey);
conditions.Add(new KeyValuePair <string, object?>(vKey, qpExpr.PropertyValue));
// in oracle, we should pass a null value instead of an empty list in case of query something like "select * from sometable where id in :id " and :id is empty.
// make sure firsty the value in params is a type of generic list.
// i dont like this solution. if anyone has a better way plz commit.
if (Provider == SqlProvider.Oracle &&
conditions[0].Value is IList {
Count: 0
} list&&
list.GetType() is { IsGenericType: true } type&&
type.GetGenericTypeDefinition() == typeof(List <>))
{
conditions[0] = new KeyValuePair <string, object?>(vKey, null);
}
}
qLevel++;
break;
case QueryBinaryExpression qbExpr:
var nSqlBuilder = new StringBuilder();
var nConditions = new List <KeyValuePair <string, object?> >();
BuildQuerySql(qbExpr.Nodes, ref nSqlBuilder, ref nConditions, ref qLevel);
if (qbExpr.Nodes.Count == 1) //Handle `grouping brackets`
{
sqlBuilder.Append(nSqlBuilder);
}
else
{
sqlBuilder.AppendFormat("({0})", nSqlBuilder);
}
conditions.AddRange(nConditions);
break;
}
}
}