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