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)); }