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