private StringBuilder AppendJoinToSelect(StringBuilder originalBuilder,
params Expression <Func <TEntity, object> >[] includes)
{
var joinsBuilder = new StringBuilder();
foreach (var include in includes)
{
var propertyName = ExpressionHelper.GetPropertyName(include);
var joinProperty = AllProperties.First(x => x.Name == propertyName);
var attrJoin = joinProperty.GetCustomAttribute <JoinAttributeBase>();
if (attrJoin != null)
{
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.GetProperties().Where(ExpressionHelper.GetPrimitivePropertiesPredicate());
var props =
properties.Where(p => !p.GetCustomAttributes <NotMappedAttribute>().Any())
.Select(p => new PropertyMetadata(p));
originalBuilder.Append(", " + GetFieldsSelect(attrJoin.TableName, props));
//joinsBuilder.Append("{joinString} {attrJoin.TableName} ON {TableName}.{attrJoin.Key} = {attrJoin.TableName}.{attrJoin.ExternalKey} ");
if (SqlConnector == ESqlConnector.Mssql)
{
joinsBuilder.AppendFormat("{0} {1} ON {2}.{3} = {4}.{5} ", joinString, attrJoin.TableName + " (NOLOCK) ", TableName,
attrJoin.Key, attrJoin.TableName, attrJoin.ExternalKey);
}
else
{
joinsBuilder.AppendFormat("{0} {1} ON {2}.{3} = {4}.{5} ", joinString, attrJoin.TableName, TableName,
attrJoin.Key, attrJoin.TableName, attrJoin.ExternalKey);
}
}
}
return(joinsBuilder);
}
private string AppendJoinToUpdate <TBase>(TBase entity, SqlQuery originalBuilder, params Expression <Func <TEntity, object> >[] includes) where TBase : notnull
{
var joinBuilder = new StringBuilder();
foreach (var include in includes)
{
var joinProperty = AllProperties.First(q => q.Name == ExpressionHelper.GetPropertyName(include));
var attrJoin = joinProperty.GetCustomAttribute <JoinAttributeBase>();
if (attrJoin == null)
{
continue;
}
var declaringType = joinProperty.ReflectedType?.GetTypeInfo();
var tableAttribute = declaringType?.GetCustomAttribute <TableAttribute>();
var tableName = MicroOrmConfig.TablePrefix + (tableAttribute != null ? tableAttribute.Name : declaringType?.Name);
var joinType = joinProperty.PropertyType.IsGenericType ? joinProperty.PropertyType.GenericTypeArguments[0] : joinProperty.PropertyType;
var properties = joinType.FindClassMetaDataProperties().Where(p => !p.IgnoreUpdate).ToArray();
var joinEntity = entity.GetType().GetProperty(joinProperty.Name)?.GetValue(entity, null);
if (joinEntity == null)
{
return(string.Empty);
}
var dict = properties.ToDictionary(prop => $"{prop.PropertyInfo.ReflectedType?.Name}{prop.PropertyName}",
prop => joinType.GetProperty(prop.PropertyName)?.GetValue(joinEntity, null));
originalBuilder.SetParam(dict);
if (UseQuotationMarks == true)
{
tableName = GetTableNameWithQuotes(attrJoin, properties, tableName);
}
else
{
attrJoin.TableName = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema);
}
joinBuilder.Append(
$", {GetFieldsUpdate(string.IsNullOrEmpty(attrJoin.TableAlias) ? attrJoin.TableName : attrJoin.TableAlias, properties, UseQuotationMarks == true)}");
AppendJoinQuery(attrJoin, originalBuilder.SqlBuilder, tableName);
}
return(joinBuilder.ToString());
}
private string AppendJoinToSelect(SqlQuery originalBuilder, bool hasSelectFilter, 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 attrJoin = joinProperty.GetCustomAttribute <JoinAttributeBase>();
if (attrJoin == null)
{
continue;
}
var declaringType = joinProperty.ReflectedType?.GetTypeInfo();
var tableAttribute = declaringType?.GetCustomAttribute <TableAttribute>();
var tableName = MicroOrmConfig.TablePrefix + (tableAttribute != null ? tableAttribute.Name : declaringType?.Name);
var joinType = joinProperty.PropertyType.IsGenericType ? joinProperty.PropertyType.GenericTypeArguments[0] : joinProperty.PropertyType;
var properties = joinType.FindClassMetaDataProperties();
if (UseQuotationMarks == true)
{
tableName = GetTableNameWithQuotes(attrJoin, properties, tableName);
}
else
{
attrJoin.TableName = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema);
}
if (!hasSelectFilter)
{
originalBuilder.SqlBuilder.Append(
$", {GetFieldsSelect(string.IsNullOrEmpty(attrJoin.TableAlias) ? attrJoin.TableName : attrJoin.TableAlias, properties, UseQuotationMarks == true)}");
}
AppendJoinQuery(attrJoin, joinBuilder, tableName);
}
return(joinBuilder.ToString());
}
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} ");
if (LogicalDelete && props.Any(s => s.PropertyName == LogicalDeleteProperty.Name))
{
bool isDateTime = LogicalDeleteProperty.PropertyType.IsDateTime();
joinBuilder.AppendFormat(" AND {0}.{1} {2} {3} ", attrJoin.TableAlias, LogicalDeletePropertyMetadata.ColumnName, isDateTime ? "IS" : "!=", isDateTime ? "NULL" : GetLogicalDeleteValue());
}
}
return(joinBuilder.ToString());
}
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.SqlConnector)
{
case ESqlConnector.MSSQL:
tableName = "[" + tableName + "]";
attrJoin.TableName = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema, "[", "]");
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 = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema, "`", "`");
attrJoin.Key = "`" + attrJoin.Key + "`";
attrJoin.ExternalKey = "`" + attrJoin.ExternalKey + "`";
foreach (var prop in props)
{
prop.ColumnName = "`" + prop.ColumnName + "`";
}
break;
case ESqlConnector.PostgreSQL:
tableName = "\"" + tableName + "\"";
attrJoin.TableName = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema, "\"", "\"");
attrJoin.Key = "\"" + attrJoin.Key + "\"";
attrJoin.ExternalKey = "\"" + attrJoin.ExternalKey + "\"";
foreach (var prop in props)
{
prop.ColumnName = "\"" + prop.ColumnName + "\"";
}
break;
default:
throw new ArgumentOutOfRangeException(nameof(Config.SqlConnector));
}
}
else
{
attrJoin.TableName = GetTableNameWithSchemaPrefix(attrJoin.TableName, attrJoin.TableSchema);
}
originalBuilder.SqlBuilder.Append(", " + GetFieldsSelect(attrJoin.TableName, props));
joinBuilder.Append(joinString + " " + attrJoin.TableName + " ON " + tableName + "." + attrJoin.Key + " = " + attrJoin.TableName + "." + attrJoin.ExternalKey + " ");
}
return(joinBuilder.ToString());
}
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 attrJoin = joinProperty.GetCustomAttribute <JoinAttributeBase>();
if (attrJoin == null)
{
continue;
}
var declaringType = joinProperty.DeclaringType.GetTypeInfo();
var tableAttribute = declaringType.GetCustomAttribute <TableAttribute>();
var tableName = tableAttribute != null ? tableAttribute.Name : declaringType.Name;
var joinString = "";
switch (attrJoin)
{
case LeftJoinAttribute _:
joinString = "LEFT JOIN";
break;
case InnerJoinAttribute _:
joinString = "INNER JOIN";
break;
case RightJoinAttribute _ when Config.SqlProvider == SqlProvider.SQLite:
throw new NotSupportedException("SQLite doesn't support RIGHT JOIN");
case RightJoinAttribute _:
joinString = "RIGHT JOIN";
break;
case CrossJoinAttribute _:
joinString = "CROSS JOIN";
break;
}
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.SQLite:
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(
attrJoin is CrossJoinAttribute
? $"{joinString} {attrJoin.TableName} AS {attrJoin.TableAlias}"
: $"{joinString} {attrJoin.TableName} AS {attrJoin.TableAlias} ON {tableName}.{attrJoin.Key} = {attrJoin.TableAlias}.{attrJoin.ExternalKey} ");
}
return(joinBuilder.ToString());
}
