/// <summary>
/// Adds an array based where clause expression to the query.
/// </summary>
/// <typeparam name="TRecord">The record type of the query builder</typeparam>
/// <param name="queryBuilder">The query builder</param>
/// <param name="fieldName">The name of one of the columns in the query. The where condition will be evaluated against the value of this column.</param>
/// <param name="operand">The SQL operator to be used in the where clause</param>
/// <param name="values">The values to compare against the column values. Each value will be added to the query as a separate parameter.</param>
/// <returns>The query builder that can be used to further modify the query, or execute the query</returns>
[Pure] public static IQueryBuilder <TRecord> Where <TRecord>(this IQueryBuilder <TRecord> queryBuilder, string fieldName,
ArraySqlOperand operand, IEnumerable values) where TRecord : class
{
var valuesList = values.OfType <object>().ToList();
ListExtender.ExtendListRepeatingLastValue(valuesList);
var parameters = valuesList.Select((v, i) => new Parameter($"{fieldName}{i + 1}")).ToArray();
return(queryBuilder.WhereParameterized(fieldName, operand, parameters).ParameterValues(valuesList));
}
protected virtual void ContributeParameter(IDbCommand command, ITypeHandlerRegistry typeHandlers, string name, object value, DocumentMap mapping = null)
{
if (value == null)
{
command.Parameters.Add(new SqlParameter(name, DBNull.Value));
return;
}
var typeHandler = typeHandlers.Resolve(value.GetType());
if (typeHandler != null)
{
var p = new SqlParameter(name, SqlDbType.NVarChar);
typeHandler.WriteDatabase(p, value);
command.Parameters.Add(p);
return;
}
if (value is TableValuedParameter tvp && command is SqlCommand sqlCommand)
{
var p = sqlCommand.Parameters.Add(name, SqlDbType.Structured);
p.Value = tvp.DataRecords;
p.TypeName = tvp.TypeName;
return;
}
if (value is IEnumerable && (value is string) == false && (value is byte[]) == false)
{
var inClauseNames = new List <string>();
var i = 0;
var inClauseValues = ((IEnumerable)value).Cast <object>().ToList();
ListExtender.ExtendListRepeatingLastValue(inClauseValues);
foreach (var inClauseValue in inClauseValues)
{
i++;
var inClauseName = name + "_" + i;
inClauseNames.Add(inClauseName);
ContributeParameter(command, typeHandlers, inClauseName, inClauseValue);
}
if (i == 0)
{
var inClauseName = name + "_" + i;
inClauseNames.Add(inClauseName);
ContributeParameter(command, typeHandlers, inClauseName, null);
}
var originalParameter = Regex.Escape("@" + name.TrimStart('@')) + @"(?=[^\w\$@#_]|$)";
var replacementParameters = "(" + string.Join(", ", inClauseNames.Select(x => "@" + x)) + ")";
command.CommandText = Regex.Replace(command.CommandText, originalParameter, match => replacementParameters, RegexOptions.IgnoreCase);
return;
}
var columnType = DatabaseTypeConverter.AsDbType(value.GetType());
if (columnType == null)
{
throw new InvalidOperationException($"Cannot map type '{value.GetType().FullName}' to a DbType. Consider providing a custom ITypeHandler.");
}
var param = new SqlParameter();
param.ParameterName = name;
param.DbType = columnType.Value;
param.Value = value;
if (columnType == DbType.String && value is string text)
{
var size = GetBestSizeBucket(text);
if (size > 0)
{
param.Size = size;
}
}
// To assist SQL's query plan caching, assign a parameter size for our
// common id lookups where possible.
if (mapping != null &&
mapping.IdColumn != null &&
mapping.IdColumn.MaxLength > 0 &&
columnType == DbType.String &&
string.Equals(name, mapping.IdColumn.ColumnName, StringComparison.OrdinalIgnoreCase))
{
if (mapping.IdColumn.MaxLength != null)
{
param.Size = mapping.IdColumn.MaxLength.Value;
}
}
if (columnType == DbType.String && mapping != null)
{
var indexed = mapping.WritableIndexedColumns();
var columnMap = indexed.FirstOrDefault(i => string.Equals(i.ColumnName, param.ParameterName, StringComparison.OrdinalIgnoreCase));
if (columnMap != null && columnMap.MaxLength != null)
{
param.Size = columnMap.MaxLength.Value;
}
}
command.Parameters.Add(param);
}
