private static void AppendCharacteristicIn(this IFacetsBuilder <Entities.Product> facetsBuilder, ICollection <string> values) { facetsBuilder.AppendText(" and $char/value = ("); int count = 0; int lastIndex = values.Count - 1; foreach (string value in values) { string paramName = facetsBuilder.GetParamName(); facetsBuilder.AppendText("sql:variable(\""); facetsBuilder.AppendText(paramName); facetsBuilder.AppendText("\")"); facetsBuilder.AddParameter(paramName, value); if (count >= lastIndex) { break; } facetsBuilder.AppendText(","); count++; } facetsBuilder.AppendText(")"); }
private static void AppendCharacteristicName(this IFacetsBuilder <Entities.Product> facetsBuilder, string name) { string paramName = facetsBuilder.GetParamName(); facetsBuilder.AppendText("$char/name = sql:variable(\""); facetsBuilder.AppendText(paramName); facetsBuilder.AppendText("\")"); facetsBuilder.AddParameter(paramName, name); }
public static IFacetsBuilder <Entities.Product> AndCharacteristics(this IFacetsBuilder <Entities.Product> facetsBuilder, IDictionary <string, HashSet <string> > characteristics) { if (characteristics == null) { return(facetsBuilder); } characteristics = characteristics .Where(c => !string.IsNullOrWhiteSpace(c.Key) && c.Value.Any(v => !string.IsNullOrWhiteSpace(v)) ) .Select(c => new KeyValuePair <string, HashSet <string> > ( c.Key, c.Value.Where(v => !string.IsNullOrWhiteSpace(v)).ToHashSet() ) ) .ToDictionary(kvp => kvp.Key, kvp => kvp.Value); if (!characteristics.Any()) { return(facetsBuilder); } string propertyId = facetsBuilder.EntityDescription .EntityProperties[typeof(Entities.Product).GetProperty(nameof(Entities.Product.Character))]; facetsBuilder.AppendText(" AND ") .AppendText(propertyId) .AppendText(".value('count(for $char in //characteristic where "); int count = 0; int lastIndex = characteristics.Count - 1; foreach (var(name, values) in characteristics) { facetsBuilder.AppendCharacteristicName(name); facetsBuilder.AppendCharacteristicIn(values); if (count >= lastIndex) { break; } facetsBuilder.AppendText(" and "); count++; } facetsBuilder.AppendText(" return 0)', 'int') > 0"); return(facetsBuilder); }
public CatalogManager(IUnitOfWork database, IFacetsBuilder <Product> facetsBuilder) : base(database) { _facetsBuilder = facetsBuilder; }
/// <summary> /// Асинхронно возвращает выборку продуктов, используя SQL. /// </summary> /// <param name="facetsBuilder">Строитель запроса фасетного поиска.</param> /// <returns>Перечисление продуктов.</returns> /// <exception cref="ArgumentNullException">SQL код сгеренированный /// аргументом "<paramref name="facetsBuilder"/>" является null, empty/whitespace.</exception> /// <exception cref="ArgumentException">Свойство Parameters аргумента "<paramref name="facetsBuilder"/>": /// не является null и не содержит элементов; /// имя одного из SQL параметров является null/empty/whitespace, содержит только префикс "@" /// или не найдено в тексте запроса.</exception> public IQueryable<Product> FacetsSearch(IFacetsBuilder<Product> facetsBuilder) { string sql = facetsBuilder.ToString(); IReadOnlyDictionary<string, object> parameters = facetsBuilder.Parameters; if (string.IsNullOrWhiteSpace(sql)) { throw new ArgumentNullException(nameof(facetsBuilder)); } if (parameters == null) { return _dbContext.Products.FromSqlRaw(sql); } if (!parameters.Any()) { throw new ArgumentException ( $"The passed argument \"{nameof(facetsBuilder)}.{nameof(facetsBuilder.Parameters)}\" " + "does not contain any sql parameters.", nameof(parameters) ); } var sqlParameters = new SqlParameter[parameters.Count]; int index = 0; foreach (var (name, value) in parameters) { if (string.IsNullOrWhiteSpace(name)) { throw new ArgumentException ( "At least one sql parameter name is null, empty or whitespace.", nameof(parameters) ); } if (ContainsOnlyPrefix(name)) { throw new ArgumentException ( "At least one sql parameter name consists only of the \"@\" prefix.", nameof(parameters) ); } string tempName = name.StartsWith('@') ? name : '@' + name; if (!sql.Contains(tempName)) { throw new ArgumentException ( $"The sql parameter with the name \"{tempName}\" was " + "not found in the text of the sql command.", nameof(sql) ); } sqlParameters[index] = new SqlParameter(tempName, value); index++; } return _dbContext.Products.FromSqlRaw(sql, sqlParameters); #region Local Functions bool ContainsOnlyPrefix(string name) => name == "@" || name.StartsWith("@") && string.IsNullOrWhiteSpace(name.Substring(1)); #endregion }