// TODO: You are DEFINITELY going to want to cache these bad boys!
// TODO: split on columns - make sure these are actually the PK columns otherwise it'll all go t**s up (really they need to be unique to the object concerned or, due to Dapper limitations, it'll go wrong, but there's nothing you can do about that in SimpleLoad)
public IQuery BuildQuery(
TypePropertyMap map,
string[] aliases,
string whereClauseExpression,
object parameters,
int desiredNumberOfResults)
{
return(BuildQuery(map, aliases, whereClauseExpression, parameters, desiredNumberOfResults, null, null));
}
private void AppendJoin(
TypePropertyMap map,
int index,
TypePropertyMapEntry entry,
string aliasForCurrentTable,
StringBuilder fromAndJoinsBuff,
DtoMetadata metadata,
string [] aliases)
{
var target = map.GetEntryWithMatchingPropertyPreceding(index, entry.Type);
if (target == null)
{
throw new InvalidOperationException(
string.Format(
"Unable to find any property "
+ "that fits type '{0}'. Please ensure you have included all the necessary "
+ "types in your query, and that any type to which '{0}' should be added "
+ "precedes it in the list of types otherwise, like soldiers killed in "
+ "Stanley Kubrick's Full Metal Jacket, you will be in a world of s***.",
entry.Type));
}
fromAndJoinsBuff.Append("LEFT OUTER JOIN ");
var targetProperty = target.GetPropertyMetadataFor(entry.Type);
if (targetProperty.HasAttribute <ManyToManyAttribute>())
{
var manyToMany = targetProperty.GetAttribute <ManyToManyAttribute>();
var linkAlias = AppendTableNameAndAlias(fromAndJoinsBuff, manyToMany.SchemaQualifiedLinkTableName);
fromAndJoinsBuff.Append(" ON ");
AppendJoinConditionArgument(target, fromAndJoinsBuff, target.Metadata.PrimaryKey, aliases);
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(fromAndJoinsBuff, target.Metadata.PrimaryKey, linkAlias);
fromAndJoinsBuff.Append(Environment.NewLine);
fromAndJoinsBuff.Append("LEFT OUTER JOIN ");
var table = metadata.GetAttribute <TableAttribute>();
AppendTableNameAndAlias(fromAndJoinsBuff, table, aliasForCurrentTable);
fromAndJoinsBuff.Append(" ON ");
AppendJoinConditionArgument(entry, fromAndJoinsBuff, metadata.PrimaryKey, aliases);
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(fromAndJoinsBuff, metadata.PrimaryKey, linkAlias);
fromAndJoinsBuff.Append(Environment.NewLine);
}
else
{
var table = metadata.GetAttribute <TableAttribute>();
AppendTableNameAndAlias(fromAndJoinsBuff, table, aliasForCurrentTable);
fromAndJoinsBuff.Append(" ON ");
if (targetProperty.HasAttribute <OneToOneAttribute>() && string.IsNullOrEmpty(targetProperty.GetAttribute <OneToOneAttribute>().ChildForeignKeyColumn))
{
// Covers situation where foreign key column is on the target table
AppendJoinConditionArgument(entry, fromAndJoinsBuff, metadata.PrimaryKey, aliases);
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(target, fromAndJoinsBuff, targetProperty, aliases);
}
else if (targetProperty.HasAttribute <ManyToOneAttribute>())
{
var manyToOne = targetProperty.GetAttribute <ManyToOneAttribute>();
var targetColumn = manyToOne.ForeignKeyTargetColumnName;
if (string.IsNullOrEmpty(targetColumn))
{
AppendJoinConditionArgument(entry, fromAndJoinsBuff, metadata.PrimaryKey, aliases);
}
else
{
AppendJoinConditionArgument(entry, fromAndJoinsBuff, targetColumn, aliases);
}
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(target, fromAndJoinsBuff, targetProperty, aliases);
}
else if (targetProperty.HasAttribute <OneToOneAttribute>() || targetProperty.HasAttribute <OneToManyAttribute>())
{
// Covers situation where foreign key column is on the source table
AppendJoinConditionArgument(entry, fromAndJoinsBuff, entry.GetPropertyMetadataFor(target.Type), aliases);
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(target, fromAndJoinsBuff, target.Metadata.PrimaryKey, aliases);
}
else
{
throw new InvalidOperationException(
string.Format(
"Unable to generate JOIN condition between types '{0}' and '{1}' because the property '{2}' on '{1}' "
+ "is not decorated with an attribute indicating its cardinality. Please add a [OneToOne], [OneToMany] "
+ "[ManyToOne], or [ManyToMany] decoration, as appropriate.",
metadata.DtoType,
target.Type,
targetProperty.Prop.Name));
}
fromAndJoinsBuff.Append(Environment.NewLine);
}
}
// TODO: You are DEFINITELY going to want to cache these bad boys!
// TODO: split on columns - make sure these are actually the PK columns otherwise it'll all go t**s up (really they need to be unique to the object concerned or, due to Dapper limitations, it'll go wrong, but there's nothing you can do about that in SimpleLoad)
public IQuery BuildQuery(
TypePropertyMap map,
string [] aliases,
string whereClauseExpression,
object parameters,
int desiredNumberOfResults)
{
var query = new Query();
var selectListBuff = new StringBuilder();
var countBuff = new StringBuilder();
var fromAndJoinsBuff = new StringBuilder();
var whereConditionBuff = new StringBuilder();
var splitOn = new StringBuilder();
if (desiredNumberOfResults > 0)
{
// I wouldn't normally do this with a non-parameterised value but you can't
// do SQL injection just using a positive integer.
countBuff.Append("TOP (");
countBuff.Append(desiredNumberOfResults);
countBuff.Append(") ");
}
for (int index = 0, size = map.Count; index < size; ++index)
{
var entry = map[index];
var metadata = entry.Metadata;
var alias = string.IsNullOrEmpty(whereClauseExpression) ? entry.Alias : aliases[index];
var firstColumn = SelectListBuilder.AppendSelectListAndGetFirstColumnFor(
selectListBuff,
metadata,
index > 0,
alias);
if (index > 0)
{
if (splitOn.Length > 0)
{
splitOn.Append(", ");
}
splitOn.Append(firstColumn);
}
if (index == 0)
{
var table = metadata.GetAttribute <TableAttribute>();
fromAndJoinsBuff.Append("FROM ");
AppendTableNameAndAlias(fromAndJoinsBuff, table, alias);
if (string.IsNullOrEmpty(whereClauseExpression))
{
BuildWhereCondition(parameters, whereConditionBuff, entry, aliases);
}
else
{
whereConditionBuff.Append("WHERE ");
whereConditionBuff.Append(whereClauseExpression);
}
}
else
{
AppendJoin(map, index, entry, alias, fromAndJoinsBuff, metadata, aliases);
}
}
query.Sql = string.Format(@"SELECT {0} {1}
{2}{3};", countBuff, selectListBuff, fromAndJoinsBuff, whereConditionBuff);
query.SplitOn = splitOn.ToString();
return(query);
}
public IQuery BuildQuery(
TypePropertyMap map,
string [] aliases,
string whereClauseExpression,
object parameters,
int desiredNumberOfResults,
int?offsetInResults,
string orderByClauseExpression)
{
var query = new Query();
var queryBuff = new StringBuilder();
var selectListBuff = new StringBuilder();
var countBuff = new StringBuilder();
var fromAndJoinsBuff = new StringBuilder();
var whereConditionBuff = new StringBuilder();
var splitOn = new StringBuilder();
var paginating = offsetInResults.HasValue;
if (offsetInResults < 0)
{
throw new ArgumentOutOfRangeException(nameof(offsetInResults), "Offset must be zero or positive");
}
if (paginating && string.IsNullOrEmpty(orderByClauseExpression))
{
throw new ArgumentException("Order by column must be specified if paginating", nameof(orderByClauseExpression));
}
if (desiredNumberOfResults > 0 && !paginating)
{
// I wouldn't normally do this with a non-parameterised value but you can't
// do SQL injection just using a positive integer.
countBuff.Append("TOP (");
countBuff.Append(desiredNumberOfResults);
countBuff.Append(") ");
}
for (int index = 0, size = map.Count; index < size; ++index)
{
var entry = map[index];
var metadata = entry.Metadata;
var alias = string.IsNullOrEmpty(whereClauseExpression) && string.IsNullOrEmpty(orderByClauseExpression) ? entry.Alias : aliases[index];
var firstColumn = SelectListBuilder.AppendSelectListAndGetFirstColumnFor(
selectListBuff,
metadata,
index > 0,
alias);
if (index > 0)
{
if (splitOn.Length > 0)
{
splitOn.Append(", ");
}
splitOn.Append(firstColumn);
}
if (index == 0)
{
var table = metadata.GetAttribute <TableAttribute>();
if (string.IsNullOrEmpty(whereClauseExpression))
{
BuildWhereCondition(parameters, whereConditionBuff, entry, aliases);
}
else
{
whereConditionBuff.Append("WHERE ");
whereConditionBuff.Append(whereClauseExpression);
}
fromAndJoinsBuff.Append(" FROM ");
if (paginating)
{
fromAndJoinsBuff.Append("(SELECT ");
fromAndJoinsBuff.Append("* FROM ");
AppendTableNameAndAlias(fromAndJoinsBuff, table, alias);
fromAndJoinsBuff
.Append(whereConditionBuff)
.Append(" ORDER BY ")
.Append(orderByClauseExpression)
.Append(" OFFSET ")
.Append(offsetInResults)
.Append(" ROWS FETCH NEXT ")
.Append(desiredNumberOfResults)
.Append(" ROWS ONLY")
.Append(") AS ")
.AppendLine(alias);
}
else
{
AppendTableNameAndAlias(fromAndJoinsBuff, table, alias);
}
}
else
{
AppendJoin(map, index, entry, alias, fromAndJoinsBuff, metadata, aliases);
}
}
queryBuff.Append("SELECT ")
.Append(countBuff)
.Append(selectListBuff)
.Append(fromAndJoinsBuff);
if (!paginating)
{
queryBuff.Append(whereConditionBuff);
if (!string.IsNullOrEmpty(orderByClauseExpression))
{
queryBuff.Append(" ORDER BY ").Append(orderByClauseExpression);
}
}
queryBuff.Append(";");
query.Sql = queryBuff.ToString();
query.SplitOn = splitOn.ToString();
return(query);
}
private void AppendJoin(
TypePropertyMap map,
int index,
TypePropertyMapEntry entry,
string aliasForCurrentTable,
StringBuilder fromAndJoinsBuff,
DtoMetadata metadata,
string [] aliases)
{
var target = map.GetEntryWithMatchingPropertyPreceding(index, entry.Type);
if (target == null)
{
throw new InvalidOperationException(
string.Format(
"Unable to find any property "
+ "that fits type '{0}'. Please ensure you have included all the necessary "
+ "types in your query, and that any type to which '{0}' should be added "
+ "precedes it in the list of types otherwise, like soldiers killed in "
+ "Stanley Kubrick's Full Metal Jacket, you will be in a world of s***.",
entry.Type));
}
fromAndJoinsBuff.Append("LEFT OUTER JOIN ");
var targetProperty = target.GetPropertyMetadataFor(entry.Type);
if (targetProperty.HasAttribute<ManyToManyAttribute>())
{
var manyToMany = targetProperty.GetAttribute<ManyToManyAttribute>();
var linkAlias = AppendTableNameAndAlias(fromAndJoinsBuff, manyToMany.SchemaQualifiedLinkTableName);
fromAndJoinsBuff.Append(" ON ");
AppendJoinConditionArgument(target, fromAndJoinsBuff, target.Metadata.PrimaryKey, aliases);
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(fromAndJoinsBuff, target.Metadata.PrimaryKey, linkAlias);
fromAndJoinsBuff.Append(Environment.NewLine);
fromAndJoinsBuff.Append("LEFT OUTER JOIN ");
var table = metadata.GetAttribute<TableAttribute>();
AppendTableNameAndAlias(fromAndJoinsBuff, table, aliasForCurrentTable);
fromAndJoinsBuff.Append(" ON ");
AppendJoinConditionArgument(entry, fromAndJoinsBuff, metadata.PrimaryKey, aliases);
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(fromAndJoinsBuff, metadata.PrimaryKey, linkAlias);
fromAndJoinsBuff.Append(Environment.NewLine);
}
else
{
var table = metadata.GetAttribute<TableAttribute>();
AppendTableNameAndAlias(fromAndJoinsBuff, table, aliasForCurrentTable);
fromAndJoinsBuff.Append(" ON ");
if (targetProperty.HasAttribute<OneToOneAttribute>() && string.IsNullOrEmpty(targetProperty.GetAttribute<OneToOneAttribute>().ChildForeignKeyColumn))
{
// Covers situation where foreign key column is on the target table
AppendJoinConditionArgument(entry, fromAndJoinsBuff, metadata.PrimaryKey, aliases);
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(target, fromAndJoinsBuff, targetProperty, aliases);
}
else if (targetProperty.HasAttribute<ManyToOneAttribute>())
{
var manyToOne = targetProperty.GetAttribute<ManyToOneAttribute>();
var targetColumn = manyToOne.ForeignKeyTargetColumnName;
if (string.IsNullOrEmpty(targetColumn))
{
AppendJoinConditionArgument(entry, fromAndJoinsBuff, metadata.PrimaryKey, aliases);
}
else
{
AppendJoinConditionArgument(entry, fromAndJoinsBuff, targetColumn, aliases);
}
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(target, fromAndJoinsBuff, targetProperty, aliases);
}
else if (targetProperty.HasAttribute<OneToOneAttribute>() || targetProperty.HasAttribute<OneToManyAttribute>())
{
// Covers situation where foreign key column is on the source table
AppendJoinConditionArgument(entry, fromAndJoinsBuff, entry.GetPropertyMetadataFor(target.Type), aliases);
fromAndJoinsBuff.Append(" = ");
AppendJoinConditionArgument(target, fromAndJoinsBuff, target.Metadata.PrimaryKey, aliases);
}
else
{
throw new InvalidOperationException(
string.Format(
"Unable to generate JOIN condition between types '{0}' and '{1}' because the property '{2}' on '{1}' "
+ "is not decorated with an attribute indicating its cardinality. Please add a [OneToOne], [OneToMany] "
+ "[ManyToOne], or [ManyToMany] decoration, as appropriate.",
metadata.DtoType,
target.Type,
targetProperty.Prop.Name));
}
fromAndJoinsBuff.Append(Environment.NewLine);
}
}
// TODO: You are DEFINITELY going to want to cache these bad boys!
// TODO: split on columns - make sure these are actually the PK columns otherwise it'll all go t**s up (really they need to be unique to the object concerned or, due to Dapper limitations, it'll go wrong, but there's nothing you can do about that in SimpleLoad)
public IQuery BuildQuery(
TypePropertyMap map,
string [] aliases,
string whereClauseExpression,
object parameters,
int desiredNumberOfResults)
{
var query = new Query();
var selectListBuff = new StringBuilder();
var countBuff = new StringBuilder();
var fromAndJoinsBuff = new StringBuilder();
var whereConditionBuff = new StringBuilder();
var splitOn = new StringBuilder();
if (desiredNumberOfResults > 0)
{
// I wouldn't normally do this with a non-parameterised value but you can't
// do SQL injection just using a positive integer.
countBuff.Append("TOP (");
countBuff.Append(desiredNumberOfResults);
countBuff.Append(") ");
}
for (int index = 0, size = map.Count; index < size; ++index)
{
var entry = map[index];
var metadata = entry.Metadata;
var alias = string.IsNullOrEmpty(whereClauseExpression) ? entry.Alias : aliases[index];
var firstColumn = SelectListBuilder.AppendSelectListAndGetFirstColumnFor(
selectListBuff,
metadata,
index > 0,
alias);
if (index > 0)
{
if (splitOn.Length > 0)
{
splitOn.Append(", ");
}
splitOn.Append(firstColumn);
}
if (index == 0)
{
var table = metadata.GetAttribute<TableAttribute>();
fromAndJoinsBuff.Append("FROM ");
AppendTableNameAndAlias(fromAndJoinsBuff, table, alias);
if (string.IsNullOrEmpty(whereClauseExpression))
{
BuildWhereCondition(parameters, whereConditionBuff, entry, aliases);
}
else
{
whereConditionBuff.Append("WHERE ");
whereConditionBuff.Append(whereClauseExpression);
}
}
else
{
AppendJoin(map, index, entry, alias, fromAndJoinsBuff, metadata, aliases);
}
}
query.Sql = string.Format(@"SELECT {0} {1}
{2}{3};", countBuff, selectListBuff, fromAndJoinsBuff, whereConditionBuff);
query.SplitOn = splitOn.ToString();
return query;
}
