/// <summary>
/// Adds a (SELECT COUNT({countFieldName}) FROM {sqlText} WHERE {subQueryConditions}) {subQueryOperator} {countValue} condition to the query;
/// </summary>
/// <param name="where"></param>
/// <param name="sqlText"></param>
/// <param name="countColumnName"></param>
/// <param name="operator"></param>
/// <param name="countValue"></param>
/// <param name="subQueryWhere"></param>
/// <returns></returns>
public static IDbQueryWhereClause SubQueryCount(this IDbQueryWhereClause where, string sqlText, string countColumnName, SqlOperator @operator, int countValue, Action <IDbQueryWhereClause> subQueryWhere)
{
var sqf = new SqlCountFilter(where.Root, SqlTable.Create(sqlText), countColumnName, @operator, countValue);
var sqfWhere = new DbQueryWhereClause(sqf.SubQueryTable, SqlLogic.And);
subQueryWhere(sqfWhere);
sqfWhere.ApplyTo(sqf.SubQueryFilters);
where.AddFilter(sqf);
return(where);
}
public void TestSubQueryCountFilter()
{
string test = "SELECT * FROM [DimWidget] AS [t0] WHERE ((SELECT COUNT([t1].[WidgetCommentID]) AS [Cnt] FROM [dbo].[WidgetComment] AS [t1] WHERE ([t0].[WidgetID]=[t1].[WidgetID])) > @p0);";
// SELECT * FROM [DimWidget] AS [t0] WHERE ((SELECT COUNT([t1].[WidgetCommentID]) AS [Cnt] FROM [dbo].[WidgetComment] AS [t1] WHERE ([t0].[WidgetID]=[t1].[WidgetID])) > @p0);
SqlSelectBuilder q = new SqlSelectBuilder("DimWidget");
var widgetComment = SqlTable.Create("dbo.WidgetComment");
var sqf = new SqlCountFilter(q.Table, widgetComment, "WidgetCommentID", SqlOperator.GreaterThan, 3);
sqf.SubQueryFilters.Add(new SqlColumnCompareFilter(q.Table, "WidgetID", SqlOperator.Equal, widgetComment));
q.Filters.Add(sqf);
var s = q.Render();
Assert.IsTrue((s.Parameters.Count > 0), "Parameter count should be 1.");
Assert.AreEqual(test, s.CommandText, true);
}
