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
}
