public void UsingQueryBuilder()
{
// subject.
var posts = new List <Post>()
{
new Post {
Id = 1, AuthorId = 1, Title = "Hello 1", Content = "World"
},
new Post {
Id = 2, AuthorId = 1, Title = "Hello 2", Content = "World"
},
new Post {
Id = 3, AuthorId = 2, Title = "Hello 3", Content = "World"
},
};
// the query.
var query = posts.AsQueryable();
var queryBuilder = new WhereBuilder(query);
queryBuilder.Compare("AuthorId", ConditionOperators.Equal, 1);
queryBuilder.And(subQuery =>
{
subQuery.Compare("Content", ConditionOperators.Equal, "World");
subQuery.Or("Title", ConditionOperators.Contains, 3);
});
query = (IQueryable <Post>)queryBuilder.Build();
Assert.AreEqual(2, query.Count());
}
public void WheresMyBuilder()
{
var expectedWhere =
@"WHERE ((Name = @p1) AND (Age < @p2)) AND (Age > @p3) AND (Name != @p4) AND (TheDate = @p5) AND (WhatIsIt = @p6)";
var search = new TestSearch
{
Name = "Person"
};
var builder = new WhereBuilder<TestType>();
var theDate = DateTime.Now.AddDays(-1);
builder.Add(x => x.Name == "test" && x.Age < 3);
builder.Add(x => x.Age > 11);
builder.Add(x => x.Name != search.Name);
builder.Add(x => x.TheDate == theDate);
builder.Add(x => x.WhatIsIt == TestEnum.TheSetting);
builder.AddParam("@pageIndex", 20);
Assert.Contains(expectedWhere, builder.Build());
Assert.Equal(20, builder.GetParamsForTesting()["@pageIndex"]);
Assert.Equal("test", builder.GetParamsForTesting()["@p1"]);
Assert.Equal(3, builder.GetParamsForTesting()["@p2"]);
Assert.Equal(11, builder.GetParamsForTesting()["@p3"]);
Assert.Equal("Person", builder.GetParamsForTesting()["@p4"]);
Assert.Equal(theDate, builder.GetParamsForTesting()["@p5"]);
Assert.Equal(0, builder.GetParamsForTesting()["@p6"]);
}
public static IQueryable <T> Query <T>(this IQueryable <T> query, Action <WhereBuilder> callback)
{
var queryBuilder = new WhereBuilder(query);
callback(queryBuilder);
var ret = queryBuilder.Build();
return((IQueryable <T>)ret);
}
public static IQueryable Query(this DbContext context, Type pocoType, Action <WhereBuilder> callback)
{
var set = SetMethod.MakeGenericMethod(pocoType).Invoke(context, new object[] { });
var queryable = set as IQueryable;
var builder = new WhereBuilder(queryable);
callback(builder);
var result = builder.Build();
return(result);
}
public static IQueryable Query(this DbContext context, Type pocoType, Action <WhereBuilder> callback)
{
var set = context.Set(pocoType);
var queryable = set.AsQueryable();
var builder = new WhereBuilder(queryable);
callback(builder);
var result = builder.Build();
return(result);
}
public void WhereBuilder_Add_MeasureSlicer_Test()
{
WhereBuilder <int> builder = new WhereBuilder <int>(_resolver);
builder.Define(b => b.Measure("quantity").GreaterOrEquals(2));
var predicate = builder.Build();
Assert.IsNotNull(predicate);
Assert.IsFalse(predicate.FiltersOnAggregation());
}
public void WhereBuilder_Add_DimensionSlicer_Test()
{
WhereBuilder <int> builder = new WhereBuilder <int>(_resolver);
builder.Define(b => b.Dimension("category").IsEquals("clothes"));
var predicate = builder.Build();
Assert.IsNotNull(predicate);
Assert.IsTrue(predicate.FiltersOnAggregation());
}
public void NullTest()
{
WhereParams <InvoiceIssueDomain> param = null;
var build = new WhereBuilder <InvoiceIssue, InvoiceIssueDomain>(_dc.InvoiceIssue, param);
build.WhereBind(x => x.InvoicePrefix, y => y.InvoicePrefix);
var query = build.Build();
var sql = query.ToString();
Assert.NotEmpty(sql);
}
public void StringValueTest(WhereOperator oper, string expected)
{
var param = new WhereParams <InvoiceIssueDomain>();
param.SetValues(x => x.InvoicePrefix, oper, "SS");
var build = new WhereBuilder <InvoiceIssue, InvoiceIssueDomain>(_dc.InvoiceIssue, param);
build.WhereBind(x => x.InvoicePrefix, y => y.InvoicePrefix);
var query = build.Build();
var sql = query.ToString();
Assert.Contains(expected, sql);
}
public void DecimalValueTest(WhereOperator oper, string expected)
{
var param = new WhereParams <InvoiceIssueDomain>();
param.SetValues(x => x.Sum, oper, 1.0m, 3.0m);
var build = new WhereBuilder <InvoiceIssue, InvoiceIssueDomain>(_dc.InvoiceIssue, param);
build.WhereBind(x => x.Sum, y => y.Total);
var query = build.Build();
var sql = query.ToString();
Assert.Contains(expected, sql);
}
public void DateTimeValueTest(WhereOperator oper, string expected)
{
var param = new WhereParams <InvoiceIssueDomain>();
param.SetValues(x => x.ModifyDate, oper, DateTime.Today, DateTime.Now);
var build = new WhereBuilder <InvoiceIssue, InvoiceIssueDomain>(_dc.InvoiceIssue, param);
build.WhereBind(x => x.ModifyDate, y => y.ModifyDate);
var query = build.Build();
var sql = query.ToString();
Assert.Contains(expected, sql);
}
public void SubQueryTest(WhereOperator oper, string expected)
{
var param = new WhereParams <InvoiceIssueDomain>();
param.SetValues(x => x.ProductQty, oper, 1, 3);
var build = new WhereBuilder <InvoiceIssue, InvoiceIssueDomain>(_dc.InvoiceIssue, param);
build.WhereBind <int?>(x => x.ProductQty, y => y.InvoiceIssueItems.Select(z => (int?)z.Qty));
var query = build.Build();
var sql = query.ToString();
Assert.Contains(expected, sql);
}
public void WhereBuilder_Add_And_Expression_With_DimensionSlicers_And_MeasureSlicer_Test()
{
WhereBuilder <int> builder = new WhereBuilder <int>(_resolver);
builder.Define(b =>
b.And(x => x.Dimension("category").IsEquals("clothes"),
x => x.Measure("quantity").GreaterOrEquals(2))
);
var predicate = builder.Build();
Assert.IsNotNull(predicate);
Assert.IsTrue(predicate.FiltersOnAggregation());
Assert.IsTrue(predicate.FiltersOnFacts());
}
internal virtual IQueryable <T> BuildQuery <T>(IQueryable <T> source)
where T : class
{
if (_filterSource == null)
{
return(source);
}
var whereBuilder = new WhereBuilder <T>(source, _filterSource);
source = whereBuilder.Build();
if (_filterSource is ISortableSearch search)
{
var orderByBuilder = new OrderByBuilder <T>(source);
source = orderByBuilder.Build(search);
}
return(source);
}
public void Should_append_where_word()
{
string whereSql = _whereBuilder.Build(_filter1);
Assert.That(whereSql.Contains(_wordWhere));
}
public void Should_append_where_word()
{
var whereSql = _whereBuilder.Build(_filter1);
Assert.Contains(_wordWhere, whereSql);
}
public virtual string GetWhereSql(Filter filter, int parameterStartIndex)
{
var whereBuilder = new WhereBuilder(this, parameterStartIndex);
return(whereBuilder.Build(filter));
}