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 Test_ListQuery_Streamed_Apply()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
queryDef.Order = new List <SortField>()
{
new SortField()
{
Name = "FirstName"
}
};
queryDef.Offset = 0;
queryDef.Limit = 50;
var qb = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.UseStreamingResult()
.Apply();
Assert.AreEqual(0, qb.PagingOptions.Value.Offset);
Assert.AreEqual(50, qb.PagingOptions.Value.Limit);
}
public void Test_ListQuery_Paging()
{
var expectedSql = "SELECT COUNT(1) FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p0);\r\nSELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p1) ORDER BY [FirstName] ASC OFFSET 0 ROWS FETCH NEXT 26 ROWS ONLY;";
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
queryDef.Order = new List <SortField>()
{
new SortField()
{
Name = "FirstName"
}
};
queryDef.Filters = new List <ListFilter>()
{
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
}
};
queryDef.Limit = 25;
queryDef.Offset = 0;
var stmt = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText.Trim(), true);
}
public void Test_ListQuery_DefaultHandler()
{
var expectedSql = "SELECT [t0].[FirstName],[t0].[LastName] FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p0);";
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
queryDef.Fields = new string[] { "FirstName", "LastName" };
queryDef.Filters = new List <ListFilter>(new ListFilter[] {
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
}
});
var conn = new Mock.MockConnection();
var stmt = conn
.QueryBuilder("dbo.Person")
.ListQuery(queryDef)
.ValidateWith <Mock.Person>()
.Apply()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText);
Assert.AreEqual(1, stmt.Parameters.Count);
}
public void TestFluentSuffix()
{
string test = "SELECT * FROM [dbo].[Product] AS [t0];Suffix Value;";
var stmt = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection())
.Suffix("Suffix Value")
.Render();
Assert.AreEqual(test, stmt.CommandText, true);
}
public void TestFluentLimit_WithoutAnyOrderBy_Throws()
{
var ex = Assert.ThrowsException <InvalidOperationException>(() =>
{
var stmt = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection())
.Limit(10, 50);
});
Assert.AreEqual("A query cannot have a limit or offset without an order by expression.", ex.Message);
}
public void TestFluentExists()
{
string test = "SELECT * FROM [dbo].[Product] AS [t0] WHERE (EXISTS (SELECT 1 FROM [dbo].[ProductColor] AS [t1] WHERE (([t1].[ProductID]=@p0) AND ([t1].[Color]=@p1))));";
//SELECT * FROM [dbo].[Product] AS [t0] WHERE (EXISTS (SELECT 1 FROM [dbo].[ProductColor] AS [t1] WHERE (([t1].[ProductID]=@p0) AND ([t1].[Color]=@p1))));
var stmt = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection())
.Where(x => x.Exists("dbo.ProductColor", sub => sub.FieldEquals("ProductID", 1).FieldEquals("Color", "Red")))
.Render();
Assert.AreEqual(test, stmt.CommandText, true);
}
public void TestFluentPagingOptions()
{
string test = "SELECT * FROM [dbo].[Product] AS [t0] ORDER BY [PersonID] ASC OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY;";
var stmt = new Csg.Data.DbQueryBuilder("dbo.Product", new MockConnection())
.OrderBy("PersonID")
.Limit(10, 50)
.Render();
Assert.AreEqual(test, stmt.CommandText, true);
}
public void Test_ListQuery_DefaultLimit()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
var qb = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.BeforeApply((config) => config.UseLimitOracle = false)
.DefaultLimit(150)
.Apply();
Assert.AreEqual(150, qb.PagingOptions.Value.Limit);
}
public void Test_ListQuery_DefaultSort()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
var qb = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.DefaultSort("FirstName")
.Apply();
Assert.AreEqual(1, qb.OrderBy.Count);
Assert.AreEqual("FirstName", qb.OrderBy.First().ColumnName);
}
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);
}
/// <summary>
/// Creates a new instance of <see cref="DbQueryBuilder"/> configured in the same manner as the existing one.
/// </summary>
/// <returns></returns>
public IDbQueryBuilder Fork()
{
var builder = new DbQueryBuilder(this.Connection, this.Transaction);
builder.CommandTimeout = this.CommandTimeout;
builder.Root = this.Root;
builder.Joins = new List <ISqlJoin>(this.Joins);
builder.Filters = new List <ISqlFilter>(this.Filters);
builder.SelectColumns = new List <ISqlColumn>(this.SelectColumns);
builder.Distinct = this.Distinct;
builder.Parameters = new List <DbParameterValue>(this.Parameters);
builder.OrderBy = new List <SqlOrderColumn>(this.OrderBy);
builder.PagingOptions = this.PagingOptions;
builder.Prefix = this.Prefix;
builder.Suffix = this.Suffix;
return(builder);
}
public void Test_ListQuery_BuildThrowsExceptionWhenNoConfig()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("Person", new Mock.MockConnection());
var request = new ListQueryDefinition();
request.Filters = new List <ListFilter>(new ListFilter[] {
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
}
});
Assert.ThrowsException <System.Exception>(() =>
{
var stmt = ListQueryBuilder.Create(query, request)
.Apply();
});
}
public void Test_ListQuery_FluentWithParametersFromQueryBuilder()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection())
.AddParameter("@Foo", "Bar", DbType.String)
.AddParameter("@Bar", "Baz", DbType.String);
var queryDef = new ListQueryDefinition();
queryDef.Limit = 10;
queryDef.Offset = 0;
var dapperCmd = query.ListQuery(queryDef)
.NoValidation()
.DefaultSort("FirstName")
.Render()
.ToDapperCommand();
Assert.AreEqual(2, (dapperCmd.Parameters as DynamicParameters).ParameterNames.Count());
//.GetResultAsync<Person>().ConfigureAwait(false).GetAwaiter().GetResult();
}
public void Test_ListQuery_ApplyEventHandlers()
{
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
bool beforeInvoked = false;
bool afterInvoked = false;
queryDef.Order = new List <SortField>()
{
new SortField()
{
Name = "FirstName"
}
};
var qb = ListQueryBuilder.Create(query, queryDef)
.NoValidation()
.BeforeApply((config) =>
{
beforeInvoked = true;
Assert.IsNotNull(config);
Assert.AreEqual(0, config.QueryBuilder.OrderBy.Count);
config.QueryBuilder.OrderBy.Add("LastName");
})
.AfterApply((config, appliedQuery) =>
{
afterInvoked = true;
Assert.IsNotNull(config);
Assert.IsNotNull(appliedQuery);
Assert.AreEqual(2, appliedQuery.OrderBy.Count);
Assert.IsTrue(appliedQuery.OrderBy.Any(x => x.ColumnName == "LastName"));
})
.Apply();
Assert.IsTrue(beforeInvoked);
Assert.IsTrue(afterInvoked);
Assert.AreEqual(2, qb.OrderBy.Count);
}
public void Test_ListQuery_TypeDerivedHandler()
{
var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[PersonID] IN (SELECT [t1].[PersonID] FROM [dbo].[PersonPhoneNumber] AS [t1] WHERE ([t1].[PhoneNumber] LIKE @p0) AND ([t1].[PersonID]=[t0].[PersonID])));";
// SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[PersonID] IN (SELECT [t1].[PersonID] FROM [dbo].[PersonPhoneNumber] AS [t1] WHERE ([t1].[PhoneNumber] LIKE @p0) AND ([t1].[PersonID]=[t0].[PersonID])));
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
queryDef.Filters = new List <ListFilter>(new ListFilter[] {
new ListFilter()
{
Name = "PhoneNumber", Operator = ListFilterOperator.Like, Value = "555"
}
});
var stmt = ListQueryBuilder.Create(query, queryDef)
.ValidateWith <Mock.Person>()
.AddFilterHandlers <Mock.PersonFilters>()
.Apply()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText);
Assert.AreEqual(1, stmt.Parameters.Count);
}