private EntityHierarchy GetEntityHierarchy(
EntityHierarchy parent,
Entity entity,
ref int index)
{
var hierarchy = new EntityHierarchy
{
Entity = entity,
Alias = "[t" + index + "]",
SubHierarchies = new List <EntityHierarchy>(),
ParentHierarchy = parent
};
foreach (var property in entity.CreateProperties()
.Where(x => x.IsForeignKey && x.TypeInfo.IsCollection)
.Where(property =>
parent == null ||
parent.Entity != property.ForeignEntity))
{
index++;
var subHierarchy =
GetEntityHierarchy(hierarchy, property.ForeignEntity, ref index);
hierarchy.SubHierarchies.Add(subHierarchy);
}
return(hierarchy);
}
private string GenerateHierarchySql(EntityHierarchy hierarchy)
{
var flatHierarchy = FlatHierarchy(hierarchy);
var columnsList = flatHierarchy.SelectMany(x => x.Entity.Properties.SkipOneToMany().Select(y => y.Column).Distinct()
.Select(y => x.Alias + "." + y + " AS " + x.Alias.Undecorate() + "_" + y.Undecorate())).ToList();
var commaSeparator = "," + Environment.NewLine + " ";
var columns = string.Join(commaSeparator, columnsList);
var ordersList = flatHierarchy.SelectMany(x => x.Entity.Keys.Select(y => x.Alias + "." + y.Column)).ToList();
var orders = string.Join(commaSeparator, ordersList);
var joins = GetJoins(flatHierarchy);
var constraintsList = new List <string>();
var counter = 0;
foreach (var key in hierarchy.Entity.Keys)
{
constraintsList.Add($"{hierarchy.Alias}.{key.Column} = @{counter++}");
}
var constraintSeparator = Environment.NewLine + " AND ";
var constraints = string.Join(constraintSeparator, constraintsList);
var sql =
$@" SELECT {columns}
FROM {hierarchy.Entity.Table} AS {hierarchy.Alias}
{joins}
WHERE {constraints}
ORDER BY {orders};";
return(sql);
}
private string GetJoin(EntityHierarchy hierarchy, EntityHierarchy parentHierarchy)
{
var parentTable = parentHierarchy.Entity.Table;
var parentAlias = parentHierarchy.Alias;
string parentKey, key;
var alias = hierarchy.Alias;
var foreignProperty = parentHierarchy.Entity.ForeignKeys.FirstOrDefault(x => x.ForeignEntity == hierarchy.Entity);
if (foreignProperty == null || foreignProperty.TypeInfo.IsCollection)
{
parentKey = parentHierarchy.Entity.Keys.FirstOrDefault().Column;
key = hierarchy.Entity.ForeignKeys
.FirstOrDefault(x => x.ForeignEntity == parentHierarchy.Entity).Column;
}
else
{
parentKey = foreignProperty.Column;
key = hierarchy.Entity.Keys.FirstOrDefault().Column;
}
var join =
$@"
LEFT OUTER JOIN {parentTable} AS {parentAlias} ON {parentAlias}.{parentKey} = {alias}.{key}";
return(parentHierarchy.ParentHierarchy != null ?
join + GetJoin(parentHierarchy, parentHierarchy.ParentHierarchy) :
join);
}
private string GetSetNullUpdateSql(
EntityHierarchy hierarchy,
string sqlNullParameterName,
string keyConstraint)
{
var table = hierarchy.Entity.Table;
var alias = hierarchy.Alias;
var sets = new List <string>();
foreach (var foreignKey in hierarchy.Entity.Properties
.Where(x => x.ForeignEntity == hierarchy.ParentHierarchy.Entity))
{
var key = hierarchy.ParentHierarchy.Entity.Keys
.FirstOrDefault(x => x.Name == foreignKey.ReferencePropertyName);
if (key != null)
{
sets.Add($"{alias}.{foreignKey.Column} = @{sqlNullParameterName}");
}
}
var setSeparator = "," + Environment.NewLine + " ";
var constraintSeparator = Environment.NewLine + " AND ";
var set = string.Join(setSeparator, sets);
var join = GetJoin(hierarchy, hierarchy.ParentHierarchy);
var updateSql =
$@"UPDATE {alias}
SET {set}
FROM {table} {alias}{join}
WHERE {keyConstraint};
";
return(updateSql);
}
private string GenerateHierarchySQL(EntityHierarchy hierarchy)
{
var flatHierarchy = FlatHierarchy(hierarchy);
// {0} - Columns
// {1} - Base table
// {2} - Base table alias
// {3} - Joins
// {4} - Where
// {5} - Order by
var selectFormat = @"SELECT {0}
FROM {1} AS {2}
{3}
WHERE {4}
ORDER BY {5}";
// {0} - Foreign table
// {1} - Foreign alias
// {2} - Foreign key
// {3} - Base table alias
// {4} - Base table primary key
var joinFormat = @"LEFT OUTER JOIN {0} AS {1} ON {1}.{2} = {3}.{4}";
var columns = flatHierarchy.SelectMany(x => GetPropertiesForColumns(x.Entity)
.Select(y => x.Alias + "." + y.ColumnName + " AS " + x.Alias.Trim("[]".ToCharArray()) + "_" + y.ColumnName)).ToList();
var joins = new List <string>();
foreach (var item in flatHierarchy.Where(x => x.ParentHierarchy != null))
{
var foreignTable = item.Entity.TableName;
var foreignAlias = item.Alias;
var foreignKey = String.Empty;
var baseTableAlias = item.ParentHierarchy.Alias;
var baseTablePrimaryKey = String.Empty;
var foreignProperty = item.Entity.Properties.FirstOrDefault(x => x.ForeignEntity == item.ParentHierarchy.Entity);
if (foreignProperty == null || foreignProperty.IsCollection)
{
foreignKey = item.Entity.Key.ColumnName;
baseTablePrimaryKey = item.ParentHierarchy.Entity.Properties.FirstOrDefault(x => x.ForeignEntity == item.Entity).ColumnName;
}
else
{
foreignKey = foreignProperty.ColumnName;
baseTablePrimaryKey = item.ParentHierarchy.Entity.Key.ColumnName;
}
joins.Add(string.Format(joinFormat, foreignTable, foreignAlias, foreignKey, baseTableAlias, baseTablePrimaryKey));
}
var orders = flatHierarchy.Select(x => x.Alias + "." + x.Entity.Key.ColumnName).ToList();
var where = hierarchy.Alias + "." + hierarchy.Entity.Key.ColumnName + " = @0";
var sql = string.Format(selectFormat, string.Join(", ", columns), hierarchy.Entity.TableName, hierarchy.Alias, string.Join(Environment.NewLine, joins), where, string.Join(", ", orders));
return(sql);
}
private void GetForeignSql(
EntityHierarchy entityHierarchy,
IDictionary <string, PropertyDeleteOption> options,
StringBuilder sqlsBuilder,
string keyConstraint,
string sqlNullParameterName,
string hierarchyPrefix = null,
int level = 0)
{
if (options.Where(x => x.Value.Level == level)
.All(x => x.Value.DeleteOption == CascadeOption.Nothing ||
x.Value.DeleteOption == CascadeOption.AskUser))
{
return;
}
if (hierarchyPrefix.HasValue())
{
hierarchyPrefix += "-";
}
foreach (var subHierarchy in entityHierarchy.SubHierarchies)
{
var deleteOption = CascadeOption.Nothing;
if (options.ContainsKey(hierarchyPrefix + subHierarchy.Entity.Name))
{
deleteOption = options[hierarchyPrefix + subHierarchy.Entity.Name].DeleteOption;
}
switch (deleteOption)
{
case CascadeOption.Detach:
sqlsBuilder.AppendLine(GetSetNullUpdateSql(
subHierarchy,
sqlNullParameterName,
keyConstraint));
break;
case CascadeOption.Delete:
GetForeignSql(
subHierarchy,
options,
sqlsBuilder,
keyConstraint,
sqlNullParameterName,
hierarchyPrefix + subHierarchy.Entity.Name,
++level);
sqlsBuilder.AppendLine(GetRelatedEntityDeleteSql(
subHierarchy,
keyConstraint));
break;
}
}
}
private IList <EntityHierarchy> FlatHierarchy(EntityHierarchy hierarchy)
{
var flatHierarchy = new List <EntityHierarchy> {
hierarchy
};
foreach (var subHierarchy in hierarchy.SubHierarchies)
{
flatHierarchy.AddRange(FlatHierarchy(subHierarchy));
}
return(flatHierarchy);
}
private string GetRelatedEntityDeleteSql(
EntityHierarchy hierarchy,
string keyConstraint)
{
var join = GetJoin(hierarchy, hierarchy.ParentHierarchy);
var table = hierarchy.Entity.Table;
var alias = hierarchy.Alias;
var delete =
$@"DELETE {alias}
FROM {table} {alias}{join}
WHERE {keyConstraint};
";
return(delete);
}
private RecordHierarchy GetHierarchyRecords(IEnumerable <dynamic> records, EntityHierarchy hierarchy)
{
var baseRecord = records.FirstOrDefault();
var prefix = hierarchy.Alias.Trim("[]".ToCharArray()) + "_";
var rowData = ExpandoToDataRow(baseRecord, hierarchy.Entity, prefix);
var recordHierarchy = new RecordHierarchy
{
Entity = hierarchy.Entity,
KeyValue = rowData.KeyValue,
DisplayName = GetDisplayName(hierarchy.Entity, rowData),
SubRecordsHierarchies = new List <RecordHierarchy>()
};
GetHierarchyRecords(recordHierarchy, records, hierarchy.SubHierarchies);
return(recordHierarchy);
}
private RecordHierarchy GetHierarchyRecords(
IList <IDictionary <string, object> > records,
EntityHierarchy hierarchy)
{
var baseRecord = records.FirstOrDefault();
var prefix = hierarchy.Alias.Undecorate() + "_";
var rowData = new DataRow(baseRecord, hierarchy.Entity, prefix);
var recordHierarchy = new RecordHierarchy
{
Entity = hierarchy.Entity,
KeyValue = rowData.KeyValue,
DisplayName = hierarchy.Entity.ToString(rowData),
SubRecordsHierarchies = new List <RecordHierarchy>()
};
GetHierarchyRecords(recordHierarchy, records, hierarchy.SubHierarchies);
return(recordHierarchy);
}
private RecordHierarchy GetHierarchyRecords(
IList<IDictionary<string, object>> records,
EntityHierarchy hierarchy)
{
var baseRecord = records.FirstOrDefault();
var prefix = hierarchy.Alias.Undecorate() + "_";
var record = hierarchy.Entity.CreateRecord(baseRecord, prefix);
var recordHierarchy = new RecordHierarchy
{
Entity = hierarchy.Entity,
JoinedKeysValues = record.JoinedKeysValues,
DisplayName = record.ToString(),
SubRecordsHierarchies = new List<RecordHierarchy>()
};
GetHierarchyRecords(recordHierarchy, records, hierarchy.SubHierarchies);
return recordHierarchy;
}
private RecordHierarchy GetHierarchyRecords(
IList <IDictionary <string, object> > records,
EntityHierarchy hierarchy)
{
var baseRecord = records.FirstOrDefault();
var prefix = hierarchy.Alias.Undecorate() + "_";
var record = hierarchy.Entity.CreateRecord(baseRecord, prefix);
var recordHierarchy = new RecordHierarchy
{
Entity = hierarchy.Entity,
JoinedKeysValues = record.JoinedKeysValues,
DisplayName = record.ToString(),
SubRecordsHierarchies = new List <RecordHierarchy>()
};
GetHierarchyRecords(recordHierarchy, records, hierarchy.SubHierarchies);
return(recordHierarchy);
}
private RecordHierarchy GetHierarchyRecords(
IList<dynamic> records,
EntityHierarchy hierarchy)
{
var baseRecord = records.FirstOrDefault();
var prefix = hierarchy.Alias.Trim("[]".ToCharArray()) + "_";
var rowData = new DataRow(baseRecord, hierarchy.Entity, prefix);
var recordHierarchy = new RecordHierarchy
{
Entity = hierarchy.Entity,
KeyValue = rowData.KeyValue,
DisplayName = hierarchy.Entity.ToString(rowData),
SubRecordsHierarchies = new List<RecordHierarchy>()
};
GetHierarchyRecords(recordHierarchy, records, hierarchy.SubHierarchies);
return recordHierarchy;
}
private RecordHierarchy GetHierarchyRecords(
IList<IDictionary<string, object>> records,
EntityHierarchy hierarchy)
{
var baseRecord = records.FirstOrDefault();
var prefix = hierarchy.Alias.Undecorate() + "_";
var rowData = new DataRow(baseRecord, hierarchy.Entity, prefix);
var recordHierarchy = new RecordHierarchy
{
Entity = hierarchy.Entity,
KeyValue = rowData.KeyValue,
DisplayName = hierarchy.Entity.ToString(rowData),
SubRecordsHierarchies = new List<RecordHierarchy>()
};
GetHierarchyRecords(recordHierarchy, records, hierarchy.SubHierarchies);
return recordHierarchy;
}
private EntityHierarchy GetEntityHierarchy(
EntityHierarchy parent,
Entity entity,
IDictionary <string, PropertyDeleteOption> deleteOptions,
string hierarchyName,
ref int index)
{
var hierarchy = new EntityHierarchy
{
Entity = entity,
Alias = "[t" + index + "]",
SubHierarchies = new List <EntityHierarchy>(),
ParentHierarchy = parent
};
foreach (var property in entity.Properties
.WhereOneToMany()
.Where(property =>
parent == null ||
parent.Entity != property.ForeignEntity))
{
if (hierarchyName.HasValue())
{
hierarchyName += "-";
}
hierarchyName += property.ForeignEntity.Name;
var deleteOption = GetDeleteOption(hierarchyName, deleteOptions);
if (deleteOption == CascadeOption.Delete ||
deleteOption == CascadeOption.AskUser)
{
index++;
var subHierarchy =
GetEntityHierarchy(hierarchy, property.ForeignEntity, deleteOptions, hierarchyName, ref index);
hierarchy.SubHierarchies.Add(subHierarchy);
}
}
return(hierarchy);
}
private string GenerateHierarchySql(EntityHierarchy hierarchy)
{
var flatHierarchy = FlatHierarchy(hierarchy);
// {0} - Columns
// {1} - Base table
// {2} - Base table alias
// {3} - Joins
// {4} - Where
// {5} - Order by
const string selectFormat =
@"SELECT {0}
FROM {1} AS {2}
{3}
WHERE {4}
ORDER BY {5};";
// {0} - Foreign table
// {1} - Foreign alias
// {2} - Foreign key
// {3} - Base table alias
// {4} - Base table primary key
const string joinFormat = @"LEFT OUTER JOIN {0} AS {1} ON {1}.{2} = {3}.{4}";
var columns = flatHierarchy.SelectMany(x => x.Entity.GetColumns()
.Select(y => x.Alias + "." + y + " AS " + x.Alias.Undecorate() + "_" + y.Undecorate())).ToList();
var joins = new List <string>();
foreach (var item in flatHierarchy.Where(x => x.ParentHierarchy != null))
{
var foreignTable = item.Entity.TableName;
var foreignAlias = item.Alias;
string foreignKey, baseTablePrimaryKey;
var baseTableAlias = item.ParentHierarchy.Alias;
var foreignProperty = item.Entity.Properties.FirstOrDefault(x => x.ForeignEntity == item.ParentHierarchy.Entity);
if (foreignProperty == null || foreignProperty.TypeInfo.IsCollection)
{
foreignKey = item.Entity.Key.FirstOrDefault().ColumnName;
baseTablePrimaryKey = item.ParentHierarchy.Entity.Properties
.FirstOrDefault(x => x.ForeignEntity == item.Entity).ColumnName;
}
else
{
foreignKey = foreignProperty.ColumnName;
baseTablePrimaryKey = item.ParentHierarchy.Entity.Key.FirstOrDefault().ColumnName;
}
joins.Add(joinFormat.Fill(
foreignTable,
foreignAlias,
foreignKey,
baseTableAlias,
baseTablePrimaryKey));
}
var orders = flatHierarchy.SelectMany(x => x.Entity.Key.Select(y => x.Alias + "." + y.ColumnName)).ToList();
var whereParts = new List <string>();
var counter = 0;
foreach (var key in hierarchy.Entity.Key)
{
whereParts.Add("{0}.{1} = @{2}".Fill(hierarchy.Alias, key.ColumnName, counter++));
}
var where = string.Join(" AND ", whereParts);
var sql = selectFormat.Fill(
string.Join(", ", columns),
hierarchy.Entity.TableName,
hierarchy.Alias,
string.Join(Environment.NewLine, joins),
where,
string.Join(", ", orders));
return(sql);
}
private EntityHierarchy GetEntityHierarchy(
EntityHierarchy parent,
Entity entity,
ref int index)
{
var hierarchy = new EntityHierarchy
{
Entity = entity,
Alias = "[t" + index + "]",
SubHierarchies = new List<EntityHierarchy>(),
ParentHierarchy = parent
};
foreach (var property in entity.CreateProperties()
.Where(x => x.IsForeignKey && x.TypeInfo.IsCollection)
.Where(property =>
parent == null ||
parent.Entity != property.ForeignEntity))
{
index++;
var subHierarchy =
GetEntityHierarchy(hierarchy, property.ForeignEntity, ref index);
hierarchy.SubHierarchies.Add(subHierarchy);
}
return hierarchy;
}
private IList<EntityHierarchy> FlatHierarchy(EntityHierarchy hierarchy)
{
var flatHierarchy = new List<EntityHierarchy> { hierarchy };
foreach (var subHierarchy in hierarchy.SubHierarchies)
{
flatHierarchy.AddRange(FlatHierarchy(subHierarchy));
}
return flatHierarchy;
}
private string GenerateHierarchySql(EntityHierarchy hierarchy)
{
var flatHierarchy = FlatHierarchy(hierarchy);
// {0} - Columns
// {1} - Base table
// {2} - Base table alias
// {3} - Joins
// {4} - Where
// {5} - Order by
const string selectFormat =
@"SELECT {0}
FROM {1} AS {2}
{3}
WHERE {4}
ORDER BY {5};";
// {0} - Foreign table
// {1} - Foreign alias
// {2} - Foreign key
// {3} - Base table alias
// {4} - Base table primary key
const string joinFormat = @"LEFT OUTER JOIN {0} AS {1} ON {1}.{2} = {3}.{4}";
var columns = flatHierarchy.SelectMany(x => x.Entity.GetColumns()
.Select(y => x.Alias + "." + y + " AS " + x.Alias.Undecorate() + "_" + y.Undecorate())).ToList();
var joins = new List<string>();
foreach (var item in flatHierarchy.Where(x => x.ParentHierarchy != null))
{
var foreignTable = item.Entity.TableName;
var foreignAlias = item.Alias;
string foreignKey, baseTablePrimaryKey;
var baseTableAlias = item.ParentHierarchy.Alias;
var foreignProperty = item.Entity.Properties.FirstOrDefault(x => x.ForeignEntity == item.ParentHierarchy.Entity);
if (foreignProperty == null || foreignProperty.TypeInfo.IsCollection)
{
foreignKey = item.Entity.Key.FirstOrDefault().ColumnName;
baseTablePrimaryKey = item.ParentHierarchy.Entity.Properties
.FirstOrDefault(x => x.ForeignEntity == item.Entity).ColumnName;
}
else
{
foreignKey = foreignProperty.ColumnName;
baseTablePrimaryKey = item.ParentHierarchy.Entity.Key.FirstOrDefault().ColumnName;
}
joins.Add(joinFormat.Fill(
foreignTable,
foreignAlias,
foreignKey,
baseTableAlias,
baseTablePrimaryKey));
}
var orders = flatHierarchy.SelectMany(x => x.Entity.Key.Select(y => x.Alias + "." + y.ColumnName)).ToList();
var whereParts = new List<string>();
var counter = 0;
foreach (var key in hierarchy.Entity.Key)
{
whereParts.Add("{0}.{1} = @{2}".Fill(hierarchy.Alias, key.ColumnName, counter++));
}
var where = string.Join(" AND ", whereParts);
var sql = selectFormat.Fill(
string.Join(", ", columns),
hierarchy.Entity.TableName,
hierarchy.Alias,
string.Join(Environment.NewLine, joins),
where,
string.Join(", ", orders));
return sql;
}
private void GetForeignSql(
EntityHierarchy entityHierarchy,
IDictionary<string, PropertyDeleteOption> options,
StringBuilder sqlsBuilder,
string keyConstraint,
string sqlNullParameterName,
string hierarchyPrefix = null,
int level = 0)
{
if (options.Where(x => x.Value.Level == level)
.All(x => x.Value.DeleteOption == CascadeOption.Nothing ||
x.Value.DeleteOption == CascadeOption.AskUser))
return;
if (hierarchyPrefix.HasValue())
hierarchyPrefix += "-";
foreach (var subHierarchy in entityHierarchy.SubHierarchies)
{
var deleteOption = CascadeOption.Nothing;
if (options.ContainsKey(hierarchyPrefix + subHierarchy.Entity.Name))
{
deleteOption = options[hierarchyPrefix + subHierarchy.Entity.Name].DeleteOption;
}
switch (deleteOption)
{
case CascadeOption.Detach:
sqlsBuilder.AppendLine(GetSetNullUpdateSql(
subHierarchy,
sqlNullParameterName,
keyConstraint));
break;
case CascadeOption.Delete:
GetForeignSql(
subHierarchy,
options,
sqlsBuilder,
keyConstraint,
sqlNullParameterName,
hierarchyPrefix + subHierarchy.Entity.Name,
++level);
sqlsBuilder.AppendLine(GetRelatedEntityDeleteSql(
subHierarchy,
keyConstraint));
break;
}
}
}
private string GetRelatedEntityDeleteSql(
EntityHierarchy hierarchy,
string keyConstraint)
{
var join = GetJoin(hierarchy, hierarchy.ParentHierarchy);
var table = hierarchy.Entity.Table;
var alias = hierarchy.Alias;
var delete =
$@"DELETE {alias}
FROM {table} {alias}{join}
WHERE {keyConstraint};
";
return delete;
}
private string GetJoin(EntityHierarchy hierarchy, EntityHierarchy parentHierarchy)
{
var parentTable = parentHierarchy.Entity.Table;
var parentAlias = parentHierarchy.Alias;
string parentKey, key;
var alias = hierarchy.Alias;
var foreignProperty = parentHierarchy.Entity.ForeignKeys.FirstOrDefault(x => x.ForeignEntity == hierarchy.Entity);
if (foreignProperty == null || foreignProperty.TypeInfo.IsCollection)
{
parentKey = parentHierarchy.Entity.Keys.FirstOrDefault().Column;
key = hierarchy.Entity.ForeignKeys
.FirstOrDefault(x => x.ForeignEntity == parentHierarchy.Entity).Column;
}
else
{
parentKey = foreignProperty.Column;
key = hierarchy.Entity.Keys.FirstOrDefault().Column;
}
var join =
$@"
LEFT OUTER JOIN {parentTable} AS {parentAlias} ON {parentAlias}.{parentKey} = {alias}.{key}";
return parentHierarchy.ParentHierarchy != null ?
join + GetJoin(parentHierarchy, parentHierarchy.ParentHierarchy) :
join;
}
private EntityHierarchy GetEntityHierarchy(
EntityHierarchy parent,
Entity entity,
IDictionary<string, PropertyDeleteOption> deleteOptions = null,
string hierarchyName = "",
int index = 0)
{
var hierarchy = new EntityHierarchy
{
Entity = entity,
Alias = "[t" + index + "]",
SubHierarchies = new List<EntityHierarchy>(),
ParentHierarchy = parent
};
foreach (var property in entity.GetDefaultCreateProperties()
.WhereOneToMany()
.Where(property =>
parent == null ||
parent.Entity != property.ForeignEntity))
{
if (hierarchyName.HasValue())
hierarchyName += "-";
hierarchyName += property.ForeignEntity.Name;
var deleteOption = GetDeleteOption(hierarchyName, deleteOptions);
if (deleteOption == CascadeOption.Delete ||
deleteOption == CascadeOption.AskUser)
{
index++;
var subHierarchy =
GetEntityHierarchy(hierarchy, property.ForeignEntity, deleteOptions, hierarchyName, index);
hierarchy.SubHierarchies.Add(subHierarchy);
}
}
return hierarchy;
}
private string GenerateHierarchySql(EntityHierarchy hierarchy)
{
var flatHierarchy = FlatHierarchy(hierarchy);
var columnsList = flatHierarchy.SelectMany(x => x.Entity.DisplayProperties.Select(y => y.Column).Distinct()
.Select(y => x.Alias + "." + y + " AS " + x.Alias.Undecorate() + "_" + y.Undecorate())).ToList();
var commaSeparator = "," + Environment.NewLine + " ";
var columns = string.Join(commaSeparator, columnsList);
var ordersList = flatHierarchy.SelectMany(x => x.Entity.Key.Select(y => x.Alias + "." + y.Column)).ToList();
var orders = string.Join(commaSeparator, ordersList);
var joins = GetJoins(flatHierarchy);
var constraintsList = new List<string>();
var counter = 0;
foreach (var key in hierarchy.Entity.Key)
{
constraintsList.Add($"{hierarchy.Alias}.{key.Column} = @{counter++}");
}
var constraintSeparator = Environment.NewLine + " AND ";
var constraints = string.Join(constraintSeparator, constraintsList);
var sql =
$@" SELECT {columns}
FROM {hierarchy.Entity.Table} AS {hierarchy.Alias}
{joins}
WHERE {constraints}
ORDER BY {orders};";
return sql;
}
private string GetSetNullUpdateSql(
EntityHierarchy hierarchy,
string sqlNullParameterName,
string keyConstraint)
{
var table = hierarchy.Entity.Table;
var alias = hierarchy.Alias;
var sets = new List<string>();
foreach (var foreignKey in hierarchy.Entity.Properties
.Where(x => x.ForeignEntity == hierarchy.ParentHierarchy.Entity))
{
var key = hierarchy.ParentHierarchy.Entity.Keys
.FirstOrDefault(x => x.Name == foreignKey.ReferencePropertyName);
if (key != null)
{
sets.Add($"{alias}.{foreignKey.Column} = @{sqlNullParameterName}");
}
}
var setSeparator = "," + Environment.NewLine + " ";
var constraintSeparator = Environment.NewLine + " AND ";
var set = string.Join(setSeparator, sets);
var join = GetJoin(hierarchy, hierarchy.ParentHierarchy);
var updateSql =
$@"UPDATE {alias}
SET {set}
FROM {table} {alias}{join}
WHERE {keyConstraint};
";
return updateSql;
}
