public void TestFluentJoinMultipleFilterCollectionsWithOrLogicIndexed() { var expectSql = "SELECT * FROM [dbo].[Product] AS [t0] WHERE ([t0].[IsActive]=@p0) AND ((([t0].[ProductCategoryID]=@p1) AND ([t0].[SupplierID]=@p2) AND ([t0].[ThingName] IN (@p3,@p4,@p5))) OR (([t0].[ProductCategoryID]=@p6) AND ([t0].[SupplierID]=@p7) AND ([t0].[ThingName] IN (@p8,@p9,@p10))));"; IDbQueryBuilder builder = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection()); var listOfThings1 = new string[] { "a", "b", "c" }; var listOfThings2 = new string[] { "d", "e", "f" }; var listOfCriteria = new Tuple <int, int, string[]>[] { new Tuple <int, int, string[]>(123, 456, listOfThings1), new Tuple <int, int, string[]>(123, 456, listOfThings2) }; builder = builder.Where(x => x.FieldEquals <bool>("IsActive", true)); builder = builder.WhereAny( listOfCriteria, (x, f, i) => x.FieldEquals("ProductCategoryID", f.Item1) .FieldEquals("SupplierID", f.Item2) .FieldIn("ThingName", f.Item3) ); var stmt = builder.Render(); Assert.IsNotNull(stmt.CommandText); Assert.AreEqual(expectSql, stmt.CommandText); }
public void TestFluentSubQueryCount() { var expectSql = "SELECT * FROM [dbo].[Product] AS [t0] WHERE ((SELECT COUNT([t1].[ProductID]) AS [Cnt] FROM [dbo].[ProductAttribute] AS [t1] WHERE ([t1].[AttributeName]=@p0)) > @p1);"; // SELECT * FROM [dbo].[Product] AS [t0] WHERE ((SELECT COUNT([t1].[ProductID]) AS [Cnt] FROM [dbo].[ProductAttribute] AS [t1] WHERE ([t1].[AttributeName]=@p0)) > @p1); IDbQueryBuilder builder = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection()); builder = builder.Where(where => where.SubQueryCount("dbo.ProductAttribute", "ProductID", SqlOperator.GreaterThan, 1, subWhere => subWhere.FieldMatch("AttributeName", SqlOperator.Equal, "Color", isAnsi: true) )); var stmt = builder.Render(); Assert.IsNotNull(stmt.CommandText); Assert.AreEqual(expectSql, stmt.CommandText); Assert.AreEqual(2, stmt.Parameters.Count); }
public void TestFluentFieldInSubQuery() { var expectSql = "SELECT * FROM [dbo].[Product] AS [t0] WHERE ([t0].[ProductID] IN (SELECT [t1].[ProductID] FROM [dbo].[ProductAttribute] AS [t1] WHERE (([t1].[AttributeName]=@p0) AND ([t1].[AttributeValue] IN (@p1,@p2)))));"; // SELECT * FROM [dbo].[Product] AS [t0] WHERE ([t0].[ProductID] IN (SELECT [t1].[ProductID] FROM [dbo].[ProductAttribute] AS [t1] WHERE (([t0].[AttributeName]=@p0) AND ([t0].[AttributeValue] IN (@p1,@p2))))); IDbQueryBuilder builder = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection()); builder = builder.Where(where => where.FieldInSubQuery("ProductID", "dbo.ProductAttribute", "ProductID", subWhere => subWhere.FieldMatch("AttributeName", SqlOperator.Equal, "Color", isAnsi: true) .FieldIn("AttributeValue", new string[] { "Red", "Green" }) )); var stmt = builder.Render(); Assert.IsNotNull(stmt.CommandText); Assert.AreEqual(expectSql, stmt.CommandText); Assert.AreEqual(3, stmt.Parameters.Count); }