private static SqlGenerationContext GenerateSQL(
IHaveSelectionSet context,
EntityMetadataContext metadata,
SqlGenerationContext sqlContext = null,
SQLOperation operation = SQLOperation.SELECT,
int level = 0,
string parentAlias = null,
EntityMetadataRelation relationMetadata = null)
{
if (sqlContext == null)
{
sqlContext = new SqlGenerationContext();
}
var key = metadata.Keys.Values.ToArray();
var schema =
metadata.CustomMetadata.TryGetValue(Globals.CUSTOM_METADATA_SCHEMA, out var customSchema) ?
customSchema : "dbo";
var table =
metadata.CustomMetadata.TryGetValue(Globals.CUSTOM_METADATA_TABLE, out var customTable) ?
customTable : metadata.Type.Name;
var queriedFields = context.GetSelectedFields();
var alias = GetAlias(level);
var entityFields =
metadata.Included.Keys
.Select(f => f.ToLower())
.Intersect(queriedFields.Keys)
.Select(f => new { field = $"{f}", exp = $"{alias}.[{f}]" })
.ToArray();
sqlContext.SelectFields.AddRange(entityFields);
sqlContext.SplitOnTypes.Add(metadata.Type);
if (level > 0)
{
sqlContext.SplitOnFields.Add(entityFields.First().field);
}
if (operation == SQLOperation.SELECT)
{
sqlContext.Sql = $"SELECT %fields% FROM [{schema}].[{table}] {alias}";
foreach (var field in queriedFields)
{
if (metadata.Relations.TryGetValue(field.Key.ToLower(), out var relation))
{
sqlContext.Relations.Add(relation);
GenerateSQL(
field.Value,
relation.EntityRight,
sqlContext,
operation: SQLOperation.JOIN,
level: level + 1,
parentAlias: alias,
relationMetadata: relation
);
}
}
}
else if (operation == SQLOperation.JOIN)
{
if (relationMetadata.IsCollection)
{
sqlContext.Sql += $" LEFT JOIN [{schema}].[{table}] {alias} ON {ManyForeignKeyCriteria(parentAlias, alias, relationMetadata)}";
}
else
{
sqlContext.Sql += $" LEFT JOIN [{schema}].[{table}] {alias} ON {SingleForeignKeyCriteria(parentAlias, alias, relationMetadata)}";
}
}
if (level == 0)
{
sqlContext.Sql = sqlContext.Sql.Replace("%fields%", string.Join(",", sqlContext.SelectFields.Select(x => x.exp)));
}
return(sqlContext);
}
private static string SingleForeignKeyCriteria(string leftAlias, string rightAlias, EntityMetadataRelation relation)
{
List <string> criteria = new List <string>();
foreach (var keyField in relation.EntityRightForeignKeys)
{
criteria.Add($"{leftAlias}.[{keyField.Key}] = {rightAlias}.[{keyField.Value}]");
}
return(string.Join(" AND ", criteria));
}