public TestSearchQuery2(QueryBuilderFactory queryBuilderFactory, ILogger logger)
{
logger.LogInformation("Creating queries.");
Name = "query";
queryBuilderFactory.Create <MySearch4Result>(this, "searchModel4", "Search for all Model4")
.WithParameterBuilder()
.BeginSearch()
.Add <MySearch4>()
.BuildWithAggregate()
.BuildQueryResult(ctx =>
{
var searches = ctx.GetQueryResults <SearchResultModel>();
var result = new MySearch4Result();
result.Results = searches.GetTypeList <MySearch4>();
result.Aggregates = new List <SearchAggregateModel>();
var agg = ctx.GetSystemItems <SearchResult>();
agg.ForEach(a => result.Aggregates.AddRange(a.Aggregates));
ctx.SetResults(new List <MySearch4Result> {
result
});
}).BuildQuery().BuildWithSingleResult();
}
//[Test]
public void SimpleIntegrationTest()
{
var connStr = ConfigurationManager.ConnectionStrings["MyConnStr"].ToString();
var proVider = ConfigurationManager.ConnectionStrings["MyConnStr"].ProviderName;
const string firstName = "Ja";
const string lastName = "Bond";
var qb = QueryBuilderFactory.Init("Person");
qb.AddSelect("FirstName")
.AddSelect("LastName")
.AddLike("FirstName", firstName)
.AddLike("LastName", lastName);
var pagingFactory = new PagingFactory(innerQuery: qb, currentPage: 2, rowsPerPage: 10, orderBy: "FirstName");
var query = pagingFactory.CreateQuery();
var sqlParams = pagingFactory.CreateParameters();
dynamic data;
using (var db = Database.OpenConnectionString(connStr, proVider))
{
data = db.Query(query, sqlParams);
}
foreach (var item in data)
{
Console.WriteLine(String.Format("First: {0} Last{1}", data.FirstName, data.LastName));
}
}
public IEnumerable <dynamic> Search(out int totalRows, string displayName, int currentPage, int rowsPerPage, bool sortDesc, string orderBy)
{
var qb = QueryBuilderFactory.Init("Person");
//could do one select statement here * or dbo.Person.*
// but this way shows the fluent mannery in which they can be added.
qb.AddSelect("PersonId")
.AddSelect("FirstName")
.AddSelect("LastName")
.AddSelect("DisplayName")
.AddLike("DisplayName", displayName);
var pager = new PagingFactory(innerQuery: qb, currentPage: currentPage, rowsPerPage: rowsPerPage,
sortDesc: sortDesc, orderBy: orderBy);
var query = pager.CreateQuery();
var sqlParams = pager.CreateParameters();
var countQuery = qb.CreateCount().CreateQuery();
dynamic data;
dynamic count;
using (var db = Database.OpenConnectionString(DataHelper.ConnectionStr(), DataHelper.Provider()))
{
data = db.Query(query, sqlParams);
count = db.QueryValue(countQuery, sqlParams);
}
totalRows = Convert.ToInt32(count);
return(data);
}
public void SunnyDay()
{
const string firstName = "Ja";
const string lastName = "Bond";
var q = QueryBuilderFactory.Init("Person");
q.AddSelect("FirstName")
.AddSelect("LastName")
.AddLike("FirstName", firstName)
.AddLike("LastName", lastName);
var pagingFactory = new PagingFactory(innerQuery: q, currentPage: 2, rowsPerPage: 10, orderBy: "FirstName");
var result = pagingFactory.CreateQuery();
var sqlParams = pagingFactory.CreateParameters();
Assert.IsTrue(sqlParams.Count() == 2);
var s1 = "With OuterQuery AS (";
var s2 = "SELECT FirstName,LastName";
var s3 = "FROM Person";
var s4 = "WHERE FirstName LIKE @0 AND LastName LIKE @1";
var s5 = ") SELECT * ";
var s6 = "FROM OuterQuery";
var s7 = "WHERE RowNumber BETWEEN 11 AND 20";
var s8 = "RowNumber";
Assert.IsTrue(result.Contains(s1));
Assert.IsTrue(result.Contains(s2));
Assert.IsTrue(result.Contains(s3));
Assert.IsTrue(result.Contains(s4));
Assert.IsTrue(result.Contains(s5));
Assert.IsTrue(result.Contains(s6));
Assert.IsTrue(result.Contains(s7));
Assert.IsTrue(result.Contains(s8));
}
public TestDocumentDbQuery(QueryBuilderFactory queryBuilderFactory, ILogger logger)
{
logger.LogInformation("Creating document db queries.");
Name = "query";
queryBuilderFactory.Create <Model1>(this, "searchModel1", "Search for a single Model 1 by Id")
.WithCache(TimeSpan.FromSeconds(15))
.WithParameterBuilder()
.WithProperty(x => x.Id)
.BuildQuery()
.BuildWithSingleResult();
queryBuilderFactory.Create <Model2>(this, "searchModel2", "Search for a single Model 2 by Id")
.WithCache(TimeSpan.FromSeconds(15))
.WithParameterBuilder()
.WithProperty(x => x.Id)
.BuildQuery()
.BuildWithSingleResult();
queryBuilderFactory.Create <Model5>(this, "searchModel5", "Search for a single Model 5 by Id")
.WithParameterBuilder()
.WithProperty(x => x.Id)
.BuildQuery()
.BuildWithSingleResult();
}
public void WhereClauseEmpty()
{
var firstName = string.Empty;
var lastName = string.Empty;
var ds = new DateSearch("Created", "", "");
var q = QueryBuilderFactory.Init("Person");
q.AddSelect("FirstName")
.AddSelect("LastName")
.AddLike("FirstName", firstName)
.AddLike("LastName", lastName)
.AddBetween(ds);
var pagingFactory = new PagingFactory(innerQuery: q, currentPage: 2, rowsPerPage: 10, orderBy: "FirstName");
var result = pagingFactory.CreateQuery();
Console.WriteLine(result);
var sqlParams = pagingFactory.CreateParameters();
Assert.IsTrue(sqlParams.Count() == 0);
var expectedNoEmptyWhere = "WHERE ";
Assert.IsFalse(result.Contains(expectedNoEmptyWhere));
var endAnd = AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result, endAnd.Msg);
}
public QueryConfigObjectGraphType(QueryBuilderFactory queryBuilderFactory)
{
Name = "query";
queryBuilderFactory.Create <MyQuiz>(this, "GetAllMyQuizes")
.WithParameterBuilder()
.BeginQuery <MyQuiz>()
.WithPropertyFromSource(x => x.Owner, ctx => GetEmail(ctx))
.BuildQueryResult(ctx =>
{
ctx.SetResults(ctx.GetQueryResults <MyQuiz>());
})
.BuildQuery()
.BuildWithListResult();
queryBuilderFactory.Create <Question>(this, "GetMyQuestions")
.WithParameterBuilder()
.BeginQuery <Question>()
.WithProperty(x => x.QuizId)
.WithPropertyFromSource(x => x.Owner, ctx => GetEmail(ctx))
.BuildQueryResult(ctx =>
{
ctx.SetResults(ctx.GetQueryResults <Question>());
})
.BuildQuery()
.BuildWithListResult();
}
public void DateNoStartDate()
{
// empty string for started and end date
var ds = new DateSearch("Created", "", "12/5/2011");
var name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(sqlParams.Count(), 3);
Assert.AreEqual(name, sqlParams[0], "name");
var date1 = sqlParams[1].ToString();
var dat1Expected = "1/1/1753";
Assert.IsTrue(date1.Contains(dat1Expected), "date1 failed");
}
protected CoreParser(string defaultField, Analyzer analyzer, QueryParser parser)
{
this.analyzer = analyzer;
this.parser = parser;
filterFactory = new FilterBuilderFactory();
filterFactory.AddBuilder("RangeFilter", new RangeFilterBuilder());
filterFactory.AddBuilder("NumericRangeFilter", new NumericRangeFilterBuilder());
queryFactory = new QueryBuilderFactory();
queryFactory.AddBuilder("TermQuery", new TermQueryBuilder());
queryFactory.AddBuilder("TermsQuery", new TermsQueryBuilder(analyzer));
queryFactory.AddBuilder("MatchAllDocsQuery", new MatchAllDocsQueryBuilder());
queryFactory.AddBuilder("BooleanQuery", new BooleanQueryBuilder(queryFactory));
queryFactory.AddBuilder("NumericRangeQuery", new NumericRangeQueryBuilder());
queryFactory.AddBuilder("DisjunctionMaxQuery", new DisjunctionMaxQueryBuilder(queryFactory));
if (parser != null)
{
queryFactory.AddBuilder("UserQuery", new UserInputQueryBuilder(parser));
}
else
{
queryFactory.AddBuilder("UserQuery", new UserInputQueryBuilder(defaultField, analyzer));
}
queryFactory.AddBuilder("FilteredQuery", new FilteredQueryBuilder(filterFactory, queryFactory));
queryFactory.AddBuilder("ConstantScoreQuery", new ConstantScoreQueryBuilder(filterFactory));
filterFactory.AddBuilder("CachedFilter", new CachedFilterBuilder(queryFactory,
filterFactory, maxNumCachedFilters));
SpanQueryBuilderFactory sqof = new SpanQueryBuilderFactory();
SpanNearBuilder snb = new SpanNearBuilder(sqof);
sqof.AddBuilder("SpanNear", snb);
queryFactory.AddBuilder("SpanNear", snb);
BoostingTermBuilder btb = new BoostingTermBuilder();
sqof.AddBuilder("BoostingTermQuery", btb);
queryFactory.AddBuilder("BoostingTermQuery", btb);
SpanTermBuilder snt = new SpanTermBuilder();
sqof.AddBuilder("SpanTerm", snt);
queryFactory.AddBuilder("SpanTerm", snt);
SpanOrBuilder sot = new SpanOrBuilder(sqof);
sqof.AddBuilder("SpanOr", sot);
queryFactory.AddBuilder("SpanOr", sot);
SpanOrTermsBuilder sots = new SpanOrTermsBuilder(analyzer);
sqof.AddBuilder("SpanOrTerms", sots);
queryFactory.AddBuilder("SpanOrTerms", sots);
SpanFirstBuilder sft = new SpanFirstBuilder(sqof);
sqof.AddBuilder("SpanFirst", sft);
queryFactory.AddBuilder("SpanFirst", sft);
SpanNotBuilder snot = new SpanNotBuilder(sqof);
sqof.AddBuilder("SpanNot", snot);
queryFactory.AddBuilder("SpanNot", snot);
}
public string GetSql(DatabaseBase database)
{
if (database == null)
{
throw new NullReferenceException("database cannot be null or empty");
}
return(QueryBuilderFactory.GetTruncateQuery(database, this.mTable));
}
public string GetSql(DatabaseBase database)
{
if (database == null)
{
throw new NullReferenceException("database cannot be null");
}
return(QueryBuilderFactory.GetInsertQuery(database, this, (Parameters)null));
}
private string GetSql(DatabaseBase database, Parameters parameters)
{
if (database == null)
{
throw new CooqDataException.CooqPreconditionException("database cannot be null");
}
return(QueryBuilderFactory.GetDeleteQuery(database, this, parameters));
}
private string GetSql(DatabaseBase database, Parameters parameters)
{
if (database == null)
{
throw new NullReferenceException("database cannot be null");
}
return(QueryBuilderFactory.GetUpdateQuery(database, this, parameters));
}
public PagingBooksQuery(QueryBuilderFactory queryBuilderFactory, ILogger logger)
{
logger.LogInformation("Creating queries.");
Name = "query";
this.AddBookAuthorsOutputQueries(queryBuilderFactory);
}
public CachedFilterBuilder(QueryBuilderFactory queryFactory,
FilterBuilderFactory filterFactory,
int cacheSize)
{
this.queryFactory = queryFactory;
this.filterFactory = filterFactory;
this.cacheSize = cacheSize;
}
public QueryConfigObjectGraphType(QueryBuilderFactory queryBuilderFactory, ILogger logger)
{
Name = "query";
//--Begin--
//--End--
}
public static void AddPublisherQueries(this BooksQuery booksQuery, QueryBuilderFactory queryBuilderFactory)
{
queryBuilderFactory.Create <Publisher>(booksQuery, GetPublisherByIdQuery)
.WithCache(TimeSpan.FromSeconds(10))
.WithParameterBuilder()
.WithProperty(x => x.Id)
.BuildQuery()
.BuildWithSingleResult();
}
public static void AddBooksQueries(this BooksQuery booksQuery, QueryBuilderFactory queryBuilderFactory)
{
// Example 1: We are getting a single Book. You are defining the argument yourself to pass into the repository with context. There's no caching and paging support. This is what comes out-of-the-box.
queryBuilderFactory.Create <Book>(booksQuery, "getBookNoCache")
.WithParameterBuilder()
.WithProperty(x => x.Id)
.BuildQuery()
.BuildWithSingleResult();
// Example 2: We are getting a single Book. The argument to pass into the repository is defined by the Model with at least one property with the KeyAttribute.
// The work is done by the cache repository which will cache the book result for a specific time you have defined. There's no paging support.
queryBuilderFactory.Create <Book>(booksQuery, "getBook")
.WithCache(TimeSpan.FromSeconds(10))
.WithParameterBuilder()
.WithKeys()
.BuildQuery()
.BuildWithSingleResult();
// Example 3: We are getting a list of Books based on an argument. You are defining the key to pass into the repository without having to use context directly.
// The cache repository which will cache the book result for a specific time you have defined. There's no paging support.
queryBuilderFactory.Create <Book>(booksQuery, "getBooksByCategory")
.WithCache(TimeSpan.FromSeconds(10))
.WithParameterBuilder()
.WithProperty(x => x.Category)
.BuildQuery()
.BuildWithListResult();
// Example 4: We are getting a list of paged Books. Technically, you are able to get all books by using TotalCount, although there's already a default page limit of 10 items per page if you don't specify.
// There's no caching support.
queryBuilderFactory.Create <Book>(booksQuery, "getPagedBooks")
.WithPaging()
.BuildWithListResult();
queryBuilderFactory.Create <Book>(booksQuery, "getPagedBooksByCategory")
.WithPaging()
.WithParameterBuilder()
.WithProperty(x => x.Category, true)
.BuildQuery()
.BuildWithListResult();
// Example 6: We are getting a list of paged Books with a argument to be passed in. You are defining the key to pass into the repository without having to use context directly.
// The cache repository which will cache the book result for a specific time you have defined. You will get paged results with a default page limit of 10 items per page if you don't specify.
queryBuilderFactory.Create <Book>(booksQuery, "getCachedPagedBooksByCategory")
.WithPaging()
.WithCache(TimeSpan.FromSeconds(10))
.WithParameterBuilder()
.WithProperty(x => x.Category)
.BuildQuery()
.BuildWithListResult();
}
public QueryConfigObjectGraphType(QueryBuilderFactory queryBuilderFactory, ILogger logger)
{
Name = "query";
// 1) Defines a simple way to get a single product by using the Key
// which is the Id property in this case.
queryBuilderFactory.Create <Product>(this, "GetProductById")
.WithParameterBuilder() // 2) Starts the query builder.
.WithKeys() // 3) Specify the use of Key attribute. This will create a query parameter on the client side to send in.
.BuildQuery() // 4) Finalize the query builder.
.BuildWithSingleResult(); // 5) Specify that we are expected to get back a single entity.
queryBuilderFactory.Create <CustomerOrder>(this, "GetOrderById")
.WithParameterBuilder()
.BeginQuery <Order>()
.WithProperty(x => x.Id)
.BuildQueryResult((ctx) =>
{
var results = ctx.GetQueryResults <Order>();
// Only a single order should match.
var order = results.Single();
ctx.SetResults(new List <CustomerOrder>
{
new CustomerOrder
{
Id = order.Id,
IsActive = order.IsActive,
Ordered = order.Ordered
}
});
ctx.Items["CustomerIdList"] = results.Select(x => (object)x.CustomerId).ToList();
ctx.Items["ProductIdList"] = order.ProductIdList.Select(x => (object)x).ToList();
})
.ThenWithQuery <Product>()
.WithPropertyFromSource(y => y.Id, ctx => (List <object>)ctx.Items["ProductIdList"])
.BuildQueryResult(ctx =>
{
var customerOrder = ctx.GetResults <CustomerOrder>().Single();
customerOrder.Products = ctx.GetQueryResults <Product>();
})
.ThenWithQuery <Customer>()
.WithPropertyFromSource(y => y.Id, ctx => (List <object>)ctx.Items["CustomerIdList"])
.BuildQueryResult(ctx =>
{
var customerOrder = ctx.GetResults <CustomerOrder>().Single();
customerOrder.Customer = ctx.GetQueryResults <Customer>().Single();
})
.BuildQuery()
.BuildWithSingleResult();
//--End--
}
private void TestCreateBuilder <T>(Action <IQueryBuilder <T> > assert)
{
// arrange
var target = new QueryBuilderFactory();
// act
var result = target.Create <T>();
// assert
assert(result);
}
public DataTable ExecuteUserQuery(int QuestionId, string Query, User User)
{
ValidateUserQuery(Query);
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.DatabaseDataSourcesConnectionString))
{
try
{
conn.Open();
var exerciseService = IocContainer.Container.GetInstance <IExerciseService>();
MultipleChoiceExercise exercise = (MultipleChoiceExercise)exerciseService.GetByQuestion(QuestionId, User);
var question = exercise.Questions.Single(x => x.ExerciseQuestionId == QuestionId);
// Start a transaction first
_sourceDA.BeginUserTransaction(conn);
IQueryBuilder queryBuilder = QueryBuilderFactory.CreateQueryBuilder(SourceDatabaseType.TSQL);
// Do any commands that the user has already ran on this data source (Execute Query type only, not submitted answers)
if (User.UserId != -1)
{
List <UserActionModel> queries = _da.ListQuestionQueries(question.ExerciseQuestionId, User.UserId);
foreach (UserActionModel query in queries)
{
try
{
String userQuery = queryBuilder.BuildQuery(query.Description, exercise, question);
_sourceDA.ExecuteUserQuery(userQuery, conn);
}
catch (Exception)
{
}
}
}
// Do the commands
String builtQuery = queryBuilder.BuildQuery(Query, exercise, question);
DataTable results = _sourceDA.GetDataTable(builtQuery, conn, 100);
return(results);
}
catch (Exception)
{
throw;
}
finally
{
// Roll back the transaction
_sourceDA.RollBackUserTransaction(conn);
}
}
}
public void EmptyStringShouldNotAddLike()
{
var firstName = string.Empty;
var q = QueryBuilderFactory.Init("Person");
q.AddSelect("FirstName")
.AddSelect("LastName")
.AddSelect("DisplayName")
.AddLike("FirstName", firstName, true);
var query = q.Create().CreateQuery();
Console.WriteLine("Query:" + query);
Assert.IsFalse(query.ToLower().Contains("like"));
}
public string GenerateSql <TABLE>(TABLE table) where TABLE : TableBase
{
if ((object)table == null)
{
throw new NullReferenceException("table cannot be null");
}
StringBuilder stringBuilder = new StringBuilder();
object[] customAttributes1 = table.GetType().GetCustomAttributes(true);
string str1 = (string)null;
foreach (object obj in customAttributes1)
{
if (obj is TableAttribute)
{
str1 = QueryBuilderFactory.CreateTableComment(table.Name, ((TableAttribute)obj).Description, table.DefaultDatabase);
break;
}
}
if (str1 == null)
{
str1 = QueryBuilderFactory.CreateTableComment(table.Name, string.Empty, table.DefaultDatabase);
}
stringBuilder.Append(str1).Append(Environment.NewLine);
foreach (FieldInfo fieldInfo in table.GetType().GetFields())
{
if (typeof(ColumnBase).IsAssignableFrom(fieldInfo.FieldType))
{
ColumnBase ColumnBase = (ColumnBase)fieldInfo.GetValue(table);
object[] customAttributes2 = fieldInfo.GetCustomAttributes(true);
string str2 = (string)null;
foreach (object obj in customAttributes2)
{
if (obj is ColumnAttribute)
{
str2 = QueryBuilderFactory.CreateColumnComment(table.Name, ColumnBase.Name, ((ColumnAttribute)obj).Description, table.DefaultDatabase);
break;
}
}
if (str2 == null)
{
str2 = QueryBuilderFactory.CreateColumnComment(table.Name, ColumnBase.Name, string.Empty, table.DefaultDatabase);
}
stringBuilder.Append(str2).Append(Environment.NewLine);
}
}
return(stringBuilder.ToString());
}
public BooksQuery(QueryBuilderFactory queryBuilderFactory, ILogger logger)
{
logger.LogInformation("Creating queries.");
Name = "query";
this.AddBooksQueries(queryBuilderFactory);
this.AddPublisherQueries(queryBuilderFactory);
this.AddBookReviewQueries(queryBuilderFactory);
queryBuilderFactory.Create <Author>(this, "getAuthorByHomeCity")
.WithParameterBuilder()
.WithProperty(x => x.HomeCity)
.BuildQuery()
.BuildWithListResult();
}
public QueryConfigObjectGraphType(QueryBuilderFactory queryBuilderFactory, ILogger logger)
{
Name = "query";
//--Begin--
queryBuilderFactory.Create <Customer>(this, "GetCustomerById", "Get Customer By Id.")
.WithParameterBuilder()
.WithKeys()
.BuildQuery()
.BuildWithSingleResult();
queryBuilderFactory.Create <Order>(this, "GetOrderById", "Get Order By Id.")
.WithParameterBuilder()
.WithKeys()
.BuildQuery()
.BuildWithSingleResult();
//--End--
}
/// <summary>
/// This Operation is called, when the module shall initialize itself.
/// </summary>
/// <param name="typeRegistry">The type registry for service requests or registrations.</param>
/// <inheritdoc/>
public void Initialize(ITypeRegistry typeRegistry)
{
var odataObjectFactory = new ODataObjectFactory();
var pathService = new ODataPathService();
var modelItemNamingService = new ModelItemNamingService();
var navigationPropertyBuilderFactory = new NavigationPropertyBuilderFactory(modelItemNamingService);
var modelBuilderFactory = new ModelBuilderFactory(pathService, navigationPropertyBuilderFactory);
var queryBuilderFactory = new QueryBuilderFactory();
var operationContextFactory = new OperationContextFactory(pathService);
var dataTypeRegistry = new DataTypeRegistry(new DefaultDataTypeKeyLookupStrategy());
typeRegistry.RegisterInstance <IModelBuilderFactory>(modelBuilderFactory);
typeRegistry.RegisterInstance <IDataTypeRegistry>(dataTypeRegistry);
typeRegistry.RegisterInstance <IODataObjectFactory>(odataObjectFactory);
typeRegistry.RegisterInstance <IODataPathService>(pathService);
typeRegistry.RegisterInstance <IOperationContextFactory>(operationContextFactory);
typeRegistry.RegisterInstance <IModelItemNamingService>(modelItemNamingService);
typeRegistry.RegisterInstance <IQueryBuilderFactory>(queryBuilderFactory);
}
public void DateBetweenSunnyDayInnerQueryTest()
{
var start = new DateTime(2011, 11, 5);
var end = new DateTime(2011, 12, 5);
var ds = new DateSearch("Created", start.ToShortDateString(), end.ToShortDateString());
const string name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(name, sqlParams[0], "name");
var date1 = sqlParams[1].ToString();
Assert.IsTrue(date1.Contains("11/5/2011"), "date1");
var date2 = sqlParams[2].ToString();
Assert.IsTrue(date2.Contains("12/5/2011"), "date2");
var endAnd = AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result, endAnd.Msg);
}
public void BetweenShouldWork()
{
var from = "200";
var to = "300";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere("Name", "=", "John")
.AddBetween("price", from, to);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, "Name");
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND price BETWEEN @1 AND @2 ";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual("John", sqlParams[0], "name");
var val1 = sqlParams[1].ToString();
Assert.AreEqual(from, val1, "min");
var val2 = sqlParams[2].ToString();
Assert.AreEqual(to, val2, "max");
var endAnd = AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result, endAnd.Msg);
}
public AdminQuery(QueryBuilderFactory queryBuilderFactory,
ILogger logger,
IConfiguration configuration)
{
logger.LogInformation("Building queries.");
Name = "query";
queryBuilderFactory.Create <Organization>(this, "GetOrganizationById")
.AssertWithClaimsPrincipal(DefaultAssertion)
.WithParameterBuilder()
.WithKeys()
.BuildQuery()
.BuildWithSingleResult();
queryBuilderFactory.Create <Organization>(this, "GetAllOrganizations")
.AssertWithClaimsPrincipal(DefaultAssertion)
.WithParameterBuilder()
.BuildQuery()
.BuildWithListResult();
_configuration = configuration;
}
public TestSearchQuery(QueryBuilderFactory queryBuilderFactory, ILogger logger)
{
logger.LogInformation("Creating queries.");
Name = "query";
queryBuilderFactory.Create <Model1>(this, "searchModel1", "Search for a single Model 1 by Id")
.WithCache(TimeSpan.FromSeconds(15))
.WithParameterBuilder()
.WithProperty(x => x.Id)
.BuildQuery()
.BuildWithSingleResult();
queryBuilderFactory.Create <Model2>(this, "searchModel2", "Search for a single Model 2 by Id")
.WithCache(TimeSpan.FromSeconds(15))
.WithParameterBuilder()
.WithProperty(x => x.Id)
.BuildQuery()
.BuildWithSingleResult();
queryBuilderFactory.Create <MySearchResult>(this, "search1", "Searches across Models.")
.WithCache(TimeSpan.FromSeconds(10))
.WithParameterBuilder()
.BeginSearch()
.Add <MySearch1>()
.Add <MySearch2>()
.Add <MySearch3>()
.Build()
.BuildQueryResult(ctx =>
{
var searches = ctx.GetQueryResults <SearchResultModel>();
var search1 = searches.GetTypeList <MySearch1>();
var search2 = searches.GetTypeList <MySearch2>();
var search3 = searches.GetTypeList <MySearch3>();
//ctx.Items["search1"] = search1;
ctx.Items["search1IdList"] = search1.Select(x => (object)x.Id).ToList();
//ctx.Items["search2"] = search1;
ctx.Items["search2IdList"] = search2.Select(x => (object)x.Id).ToList();
//ctx.Items["search3"] = search1;
ctx.Items["search3IdList"] = search3.Select(x => (object)x.Id).ToList();
})
.ThenWithQuery <Model1>()
.WithPropertyFromSource(x => x.Id, ctx => (List <object>)ctx.Items["search1IdList"]).BuildQueryResult(x =>
{
var results = x.GetQueryResults <Model1>();
x.SetResults(results.Select(r => new MySearchResult
{
DateField = r.DateField,
DoubleField = r.DoubleField,
Field = r.Field,
Id = r.Id,
IntField = r.IntField
}).ToList());
})
.ThenWithQuery <Model2>()
.WithPropertyFromSource(x => x.Id, ctx => (List <object>)ctx.Items["search2IdList"]).BuildQueryResult(x =>
{
var results = x.GetQueryResults <Model2>();
var currentResults = results.Select(r => new MySearchResult
{
DateField = r.DateField,
DoubleField = r.DoubleField,
Field = r.Field,
Id = r.Id,
IntField = r.IntField
}).ToList();
currentResults.AddRange(x.GetResults <MySearchResult>());
x.SetResults(currentResults);
})
.ThenWithQuery <Model3V2>()
.WithPropertyFromSource(x => x.Id, ctx => (List <object>)ctx.Items["search3IdList"]).BuildQueryResult(x =>
{
var results = x.GetQueryResults <Model3V2>();
var currentResults = results.Select(r => new MySearchResult
{
DateField = r.DateField,
DoubleField = r.DoubleField,
Field = r.Field,
Id = r.Id,
IntField = r.IntField
}).ToList();
currentResults.AddRange(x.GetResults <MySearchResult>());
x.SetResults(currentResults);
})
.BuildQuery().BuildWithListResult();
queryBuilderFactory.Create <MySearchResult2>(this, "searchWithAggregate", "Searches across Models.")
.WithParameterBuilder()
.BeginSearch()
.Add <MySearch3>()
.BuildWithAggregate()
.BuildQueryResult(ctx =>
{
var searches = ctx.GetSystemItems <SearchResult>();
var output = new MySearchResult2();
var idList = new List <string>();
searches.ForEach(search =>
{
idList.AddRange(search.Values.Select(y => ((MySearch3)y.Value).Id));
output.Aggregates.AddRange(search.Aggregates);
});
ctx.Items["Output"] = output;
ctx.Items["search3IdList"] = idList.Select(id => (object)id).ToList();
})
.ThenWithQuery <Model3V2>()
.WithPropertyFromSource(x => x.Id, ctx => (List <object>)ctx.Items["search3IdList"]).BuildQueryResult(ctx =>
{
var results = ctx.GetQueryResults <Model3V2>();
var output = (MySearchResult2)ctx.Items["Output"];
output.Results = results.Select(r => new MySearchResult
{
DateField = r.DateField,
DoubleField = r.DoubleField,
Field = r.Field,
Id = r.Id,
IntField = r.IntField
}).ToList();
ctx.SetResults(new List <MySearchResult2> {
output
});
}).BuildQuery().BuildWithSingleResult();
}
public string GetSql()
{
return(QueryBuilderFactory.GetUpdateQuery(DatabaseProvider.INSTANCE, this, (Parameters)null));
}
