private string AppendJoinToSelect(SqlQuery originalBuilder, params Expression <Func <TEntity, object> >[] includes)
{
var joinBuilder = new StringBuilder();
foreach (var include in includes)
{
var joinProperty = AllProperties.First(q => q.Name == ExpressionHelper.GetPropertyName(include));
var declaringType = joinProperty.DeclaringType.GetTypeInfo();
var tableAttribute = declaringType.GetCustomAttribute <TableAttribute>();
var tableName = tableAttribute != null ? tableAttribute.Name : declaringType.Name;
var attrJoin = joinProperty.GetCustomAttribute <JoinAttributeBase>();
if (attrJoin == null)
{
continue;
}
var joinString = "";
if (attrJoin is LeftJoinAttribute)
{
joinString = "LEFT JOIN";
}
else if (attrJoin is InnerJoinAttribute)
{
joinString = "INNER JOIN";
}
else if (attrJoin is RightJoinAttribute)
{
joinString = "RIGHT JOIN";
}
var joinType = joinProperty.PropertyType.IsGenericType() ? joinProperty.PropertyType.GenericTypeArguments[0] : joinProperty.PropertyType;
var properties = joinType.FindClassProperties().Where(ExpressionHelper.GetPrimitivePropertiesPredicate());
var props = properties.Where(p => !p.GetCustomAttributes <NotMappedAttribute>().Any()).Select(p => new SqlPropertyMetadata(p)).ToArray();
if (Config.UseQuotationMarks)
{
switch (Config.SqlProvider)
{
case SqlProvider.MSSQL:
tableName = "[" + tableName + "]";
attrJoin.TableName = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema, "[", "]");
attrJoin.Key = "[" + attrJoin.Key + "]";
attrJoin.ExternalKey = "[" + attrJoin.ExternalKey + "]";
attrJoin.TableAlias = "[" + attrJoin.TableAlias + "]";
foreach (var prop in props)
{
prop.ColumnName = "[" + prop.ColumnName + "]";
}
break;
case SqlProvider.MySQL:
tableName = "`" + tableName + "`";
attrJoin.TableName = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema, "`", "`");
attrJoin.Key = "`" + attrJoin.Key + "`";
attrJoin.ExternalKey = "`" + attrJoin.ExternalKey + "`";
attrJoin.TableAlias = "`" + attrJoin.TableAlias + "`";
foreach (var prop in props)
{
prop.ColumnName = "`" + prop.ColumnName + "`";
}
break;
case SqlProvider.PostgreSQL:
tableName = "\"" + tableName + "\"";
attrJoin.TableName = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema, "\"", "\"");
attrJoin.Key = "\"" + attrJoin.Key + "\"";
attrJoin.ExternalKey = "\"" + attrJoin.ExternalKey + "\"";
attrJoin.TableAlias = "\"" + attrJoin.TableAlias + "\"";
foreach (var prop in props)
{
prop.ColumnName = "\"" + prop.ColumnName + "\"";
}
break;
default:
throw new ArgumentOutOfRangeException(nameof(Config.SqlProvider));
}
}
else
{
attrJoin.TableName = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema);
}
originalBuilder.SqlBuilder.Append($", {GetFieldsSelect(attrJoin.TableAlias, props)}");
joinBuilder.Append(
$"{joinString} {attrJoin.TableName} AS {attrJoin.TableAlias} ON {tableName}.{attrJoin.Key} = {attrJoin.TableAlias}.{attrJoin.ExternalKey} ");
}
return(joinBuilder.ToString());
}
private string AppendJoinToSelect(SqlQuery originalBuilder, params Expression <Func <TEntity, object> >[] includes)
{
var joinSql = "";
var joinedProperties = new List <PropertyInfo>();
foreach (var include in includes)
{
var propertyName = ExpressionHelper.GetFullPropertyName(include);
var joinProperty = AllProperties.Concat(joinedProperties).First(x =>
{
if (x.DeclaringType != null)
{
return(x.DeclaringType.FullName + "." + x.Name == propertyName);
}
return(false);
});
var tableName = GetTableNameOrAlias(joinProperty.DeclaringType);
var attrJoin = joinProperty.GetCustomAttribute <JoinAttributeBase>();
if (attrJoin == null)
{
continue;
}
var joinString = "";
if (attrJoin is LeftJoinAttribute)
{
joinString = "LEFT JOIN";
}
else if (attrJoin is InnerJoinAttribute)
{
joinString = "INNER JOIN";
}
else if (attrJoin is RightJoinAttribute)
{
joinString = "RIGHT JOIN";
}
var joinType = joinProperty.PropertyType.IsGenericType() ? joinProperty.PropertyType.GenericTypeArguments[0] : joinProperty.PropertyType;
joinedProperties.AddRange(joinType.GetProperties().Where(p => !p.GetCustomAttributes <NotMappedAttribute>().Any()));
var properties = joinType.GetProperties().Where(ExpressionHelper.GetPrimitivePropertiesPredicate());
var props = properties.Where(p => !p.GetCustomAttributes <NotMappedAttribute>().Any()).Select(p => new SqlPropertyMetadata(p)).ToArray();
switch (SqlConnector)
{
case ESqlConnector.MSSQL:
tableName = "[" + tableName + "]";
attrJoin.TableName = "[" + attrJoin.TableName + "]";
attrJoin.Key = "[" + attrJoin.Key + "]";
attrJoin.ExternalKey = "[" + attrJoin.ExternalKey + "]";
foreach (var prop in props)
{
prop.ColumnName = "[" + prop.ColumnName + "]";
}
break;
case ESqlConnector.MySQL:
tableName = "`" + tableName + "`";
attrJoin.TableName = "`" + attrJoin.TableName + "`";
attrJoin.Key = "`" + attrJoin.Key + "`";
attrJoin.ExternalKey = "`" + attrJoin.ExternalKey + "`";
foreach (var prop in props)
{
prop.ColumnName = "`" + prop.ColumnName + "`";
}
break;
case ESqlConnector.PostgreSQL:
break;
default:
throw new ArgumentOutOfRangeException(nameof(SqlConnector));
}
originalBuilder.SqlBuilder.Append(", " + GetFieldsSelect(attrJoin.TableName, props));
joinSql += joinString + " " + attrJoin.TableName + " ON " + tableName + "." + attrJoin.Key + " = " + attrJoin.TableName + "." + attrJoin.ExternalKey + " ";
}
return(joinSql);
}
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.Name == item.PropertyName).ColumnName;
if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
{
sqlQuery.SqlBuilder.Append(item.LinkingOperator + " ");
}
if (item.PropertyValue == null)
{
var qOperator = item.QueryOperator == "=" ? "IS" : "IS NOT";
sqlQuery.SqlBuilder.Append(TableName + "." + columnName + " " + qOperator + " 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 && (SqlConnector == ESqlConnector.MySQL || SqlConnector == ESqlConnector.PostgreSQL))
{
sqlQuery.SqlBuilder.Append("LIMIT 1");
}
sqlQuery.SetParam(dictionary);
return(sqlQuery);
}
/// <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;
}
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));
}
