public void SubQueryCanBeUsedAsCriteriaUsingVar()
{
var query = new SqlQuery()
.From("ParentTable")
.Select("ParentColumn");
query.Where(new Criteria(query.SubQuery()
.From("SubTable")
.Take(1)
.Select("SubColumn")) >= 1);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT ParentColumn FROM ParentTable WHERE " +
"((SELECT TOP 1 SubColumn FROM SubTable) >= @p1)"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void SubQuerySharesParameters()
{
var query = new SqlQuery();
Assert.Equal(0, query.ParamCount);
var sub = query.SubQuery();
sub.AddParam("@px1", "value");
Assert.Equal(1, query.ParamCount);
Assert.Equal((string)query.Params["@px1"], "value");
}
public void OnPrepareQuery(IListRequestHandler handler, SqlQuery query)
{
if (ReferenceEquals(null, Target) ||
handler.Request.EqualityFilter == null ||
!attr.HandleEqualityFilter)
return;
object value;
if (handler.Request.EqualityFilter.TryGetValue(Target.PropertyName, out value) ||
handler.Request.EqualityFilter.TryGetValue(Target.Name, out value))
{
if (value == null || value as string == "")
return;
var values = new List<object>();
if (!(value is string) && value is IEnumerable)
{
foreach (var val in (IEnumerable)value)
values.Add(itemKeyField.ConvertValue(val, CultureInfo.InvariantCulture));
}
else
{
values.Add(itemKeyField.ConvertValue(value, CultureInfo.InvariantCulture));
}
if (values.Count > 0)
{
var ls = new Alias(itemKeyField.Fields.TableName, "__ls");
query.Where(Criteria.Exists(
query.SubQuery()
.From(ls)
.Select("1")
.Where(
new Criteria(ls[thisKeyField]) == new Criteria((Field)((IIdRow)handler.Row).IdField) &
new Criteria(ls[itemKeyField]).In(values))
.ToString()));
}
handler.IgnoreEqualityFilter(Target.PropertyName);
handler.IgnoreEqualityFilter(Target.Name);
}
}
