/// <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);
}
}
private SqlQuery GetSelect(Expression <Func <TEntity, bool> > predicate, bool firstOnly, params Expression <Func <TEntity, object> >[] includes)
{
var builder = InitBuilderSelect(firstOnly);
if (includes.Any())
{
var joinsBuilder = AppendJoinToSelect(builder, includes);
builder.Append($" FROM {TableName} ");
builder.Append(joinsBuilder);
}
else
{
builder.Append($" FROM {TableName} ");
}
IDictionary <string, object> expando = new ExpandoObject();
if (predicate != null)
{
// WHERE
var queryProperties = new List <QueryParameter>();
FillQueryProperties(ExpressionHelper.GetBinaryExpression(predicate.Body), ExpressionType.Default, ref queryProperties);
builder.Append(" WHERE ");
for (int i = 0; i < queryProperties.Count; i++)
{
var item = queryProperties[i];
if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
{
builder.Append(string.Format("{0} {1}.{2} {3} @{2} ", item.LinkingOperator, TableName, item.PropertyName, item.QueryOperator));
}
else
{
builder.Append(string.Format("{0}.{1} {2} @{1} ", TableName, item.PropertyName, item.QueryOperator));
}
expando[item.PropertyName] = item.PropertyValue;
}
}
if (firstOnly && (SqlConnector == ESqlConnector.MySQL || SqlConnector == ESqlConnector.PostgreSQL))
{
builder.Append("LIMIT 1");
}
return(new SqlQuery(builder.ToString().TrimEnd(), expando));
}
/// <summary>
/// Fill query properties
/// </summary>
/// <param name="body">The body.</param>
/// <param name="linkingType">Type of the linking.</param>
/// <param name="queryProperties">The query properties.</param>
private static void FillQueryProperties(BinaryExpression body, ExpressionType linkingType, ref List <QueryParameter> queryProperties)
{
if (body.NodeType != ExpressionType.AndAlso && body.NodeType != ExpressionType.OrElse)
{
string propertyName = ExpressionHelper.GetPropertyName(body);
object propertyValue = ExpressionHelper.GetValue(body.Right);
string opr = ExpressionHelper.GetSqlOperator(body.NodeType);
string link = ExpressionHelper.GetSqlOperator(linkingType);
queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr));
}
else
{
FillQueryProperties(ExpressionHelper.GetBinaryExpression(body.Left), body.NodeType, ref queryProperties);
FillQueryProperties(ExpressionHelper.GetBinaryExpression(body.Right), body.NodeType, ref queryProperties);
}
}
/// <summary>
/// Fill query properties
/// </summary>
/// <param name="body">The body.</param>
/// <param name="linkingType">Type of the linking.</param>
/// <param name="queryProperties">The query properties.</param>
private void FillQueryProperties(BinaryExpression body, ExpressionType linkingType, ref List <QueryParameter> queryProperties)
{
if (body.NodeType != ExpressionType.AndAlso && body.NodeType != ExpressionType.OrElse)
{
var propertyName = ExpressionHelper.GetPropertyName(body);
if (!SqlProperties.Select(x => x.PropertyName).Contains(propertyName))
{
throw new NotImplementedException("predicate can't parse");
}
var propertyValue = ExpressionHelper.GetValue(body.Right);
var opr = ExpressionHelper.GetSqlOperator(body.NodeType);
var link = ExpressionHelper.GetSqlOperator(linkingType);
queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr));
}
else
{
FillQueryProperties(ExpressionHelper.GetBinaryExpression(body.Left), body.NodeType, ref queryProperties);
FillQueryProperties(ExpressionHelper.GetBinaryExpression(body.Right), body.NodeType, ref queryProperties);
}
}
/// <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 "CompareString":
case "Equals":
case "StartsWith":
case "EndsWith":
{
if (exprObj == null ||
exprObj.NodeType != ExpressionType.MemberAccess)
{
goto default;
}
var propertyName = ExpressionHelper.GetPropertyNamePath(exprObj, out bool 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);
bool checkNullable = isNested && propertyName.EndsWith("HasValue");
if (!checkNullable)
{
if (!SqlProperties.Select(x => x.PropertyName).Contains(propertyName) &&
!SqlJoinProperties.Select(x => x.PropertyName).Contains(propertyName))
{
throw new NotSupportedException("predicate can't parse");
}
}
else
{
var prop = SqlProperties.FirstOrDefault(x => x.IsNullable && x.PropertyName + "HasValue" == propertyName);
if (prop == null)
{
prop = SqlJoinProperties.FirstOrDefault(x => x.IsNullable && x.PropertyName + "HasValue" == propertyName);
if (prop == null)
{
throw new NotSupportedException("predicate can't parse");
}
}
else
{
isNested = false;
}
propertyName = prop.PropertyName;
}
var propertyValue = ExpressionHelper.GetValue(binaryExpression.Right);
var nodeType = checkNullable ? ((bool)propertyValue == false ? ExpressionType.Equal : ExpressionType.NotEqual) : binaryExpression.NodeType;
if (checkNullable)
{
propertyValue = null;
}
var opr = ExpressionHelper.GetSqlOperator(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>
/// 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);
// Handle empty list passed scenario
// https://github.com/StackExchange/Dapper/issues/565
if (opr.ToUpper().Contains("IN") && propertyValue is System.Collections.ICollection collection && collection.Count == 0)
{
propertyValue = new[] { "" }
}
;
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;
}
var propertyName = ExpressionHelper.GetPropertyNamePath(exprObj, out bool 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));
}
private SqlQuery GetSelect(Expression <Func <TEntity, bool> > predicate, bool firstOnly, params Expression <Func <TEntity, object> >[] includes)
{
var sqlQuery = InitBuilderSelect(firstOnly);
if (includes.Any())
{
var joinsBuilder = AppendJoinToSelect(sqlQuery, includes);
sqlQuery.SqlBuilder.Append(" FROM " + TableName + " ");
sqlQuery.SqlBuilder.Append(joinsBuilder);
}
else
{
sqlQuery.SqlBuilder.Append(" FROM " + TableName + " ");
}
IDictionary <string, object> dictionary = new Dictionary <string, object>();
if (predicate != null)
{
// WHERE
var queryProperties = new List <QueryParameter>();
FillQueryProperties(ExpressionHelper.GetBinaryExpression(predicate.Body), ExpressionType.Default, ref queryProperties);
sqlQuery.SqlBuilder.Append("WHERE ");
for (var i = 0; i < queryProperties.Count; i++)
{
var item = queryProperties[i];
var 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 + " ");
}
dictionary[item.PropertyName] = item.PropertyValue;
}
if (LogicalDelete)
{
sqlQuery.SqlBuilder.Append("AND " + TableName + "." + StatusPropertyName + " != " + LogicalDeleteValue + " ");
}
}
else
{
if (LogicalDelete)
{
sqlQuery.SqlBuilder.Append("WHERE " + TableName + "." + StatusPropertyName + " != " + LogicalDeleteValue + " ");
}
}
if (firstOnly && (Config.SqlConnector == ESqlConnector.MySQL || Config.SqlConnector == ESqlConnector.PostgreSQL))
{
sqlQuery.SqlBuilder.Append("LIMIT 1");
}
sqlQuery.SetParam(dictionary);
return(sqlQuery);
}