public Sql BuildJoin(IDatabase database, SqlExpression <T> sqlExpression, List <JoinData> joinSqlExpressions, List <SelectMember> newMembers, bool count, bool distinct)
{
var modelDef = _pocoData;
var sqlTemplate = count
? "SELECT COUNT(*) FROM {1} {2} {3} {4}"
: "SELECT {0} FROM {1} {2} {3} {4}";
// build cols
var cols = modelDef.QueryColumns
.Select(x => x.Value)
.Select((x, j) =>
{
var col = new StringPocoCol();
col.StringCol = database.DatabaseType.EscapeTableName(x.TableInfo.AutoAlias) + "." +
database.DatabaseType.EscapeSqlIdentifier(x.ColumnName) + " as " + database.DatabaseType.EscapeSqlIdentifier(x.MemberInfoKey);
col.PocoColumn = new[] { x };
return(col);
}).ToList();
// build wheres
var where = sqlExpression.Context.ToWhereStatement();
where = (string.IsNullOrEmpty(where) ? string.Empty : "\n" + where);
// build joins and add cols
var joins = BuildJoinSql(database, joinSqlExpressions, ref cols);
// build orderbys
ISqlExpression exp = sqlExpression;
var orderbys = string.Empty;
if (!count && exp.OrderByMembers.Any())
{
var orderMembers = exp.OrderByMembers.Select(x =>
{
return(new
{
Column = x.PocoColumns.Last().MemberInfoKey,
x.AscDesc
});
}).ToList();
orderbys = "\nORDER BY " + string.Join(", ", orderMembers.Select(x => database.DatabaseType.EscapeSqlIdentifier(x.Column) + " " + x.AscDesc).ToArray());
}
// Override select columns with projected ones
if (newMembers != null)
{
var selectMembers = exp.OrderByMembers
.Select(x => new SelectMember()
{
PocoColumn = x.PocoColumn, EntityType = x.EntityType, PocoColumns = x.PocoColumns
})
.Where(x => !newMembers.Any(y => y.EntityType == x.EntityType && y.PocoColumns.SequenceEqual(x.PocoColumns)));
cols = newMembers.Concat(selectMembers).Select(x =>
{
return(new StringPocoCol
{
StringCol = database.DatabaseType.EscapeTableName(x.PocoColumn.TableInfo.AutoAlias) + "." +
database.DatabaseType.EscapeSqlIdentifier(x.PocoColumn.ColumnName) + " as " + database.DatabaseType.EscapeSqlIdentifier(x.PocoColumns.Last().MemberInfoKey),
PocoColumn = x.PocoColumns
});
}).ToList();
}
// replace templates
var resultantSql = string.Format(sqlTemplate,
(distinct ? "DISTINCT " : "") + string.Join(", ", cols.Select(x => x.StringCol).ToArray()),
database.DatabaseType.EscapeTableName(modelDef.TableInfo.TableName) + " " + database.DatabaseType.EscapeTableName(modelDef.TableInfo.AutoAlias) + exp.TableHint,
joins,
where,
orderbys);
var newsql = count ? resultantSql : exp.ApplyPaging(resultantSql, cols.Select(x => x.PocoColumn), _joinSqlExpressions);
return(new Sql(newsql, _sqlExpression.Context.Params));
}