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