public IEnumerable <dynamic> Select(Filter filter)
{
var query = _queryBuilder.Build(Query.Select, _table, filter);
var parameters = filter.Parameters.ToDictionary(x => "@" + x.Key, x => x.Value);
var result = _db.Execute(query, r => Map(r, filter.DetailMode), parameters).ToList();
return(result);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Person>("Id");
var fields = context.GetSelectedFields();
if (fields.ContainsKey("firstName"))
{
query.Select($"{alias}.FirstName");
}
if (fields.ContainsKey("lastName"))
{
query.Select($"{alias}.LastName");
}
if (fields.ContainsKey("companies"))
{
var personCompanies = $"{alias}PersonCompany";
var companyAlias = $"{alias}Company";
query
.LeftJoin($"PersonCompany {personCompanies} ON {alias}.Id = {personCompanies}.PersonId")
.LeftJoin($"Company {companyAlias} ON {personCompanies}.CompanyId = {companyAlias}.Id");
query = emailQueryBuilder.Build(query, fields["companies"], companyAlias);
}
if (fields.ContainsKey("emails"))
{
var personEmailAlias = $"{alias}PersonEmail";
var emailAlias = $"{alias}Email";
query
.LeftJoin($"PersonEmail {personEmailAlias} ON {alias}.Id = {personEmailAlias}.PersonId")
.LeftJoin($"Email {emailAlias} ON {personEmailAlias}.EmailId = {emailAlias}.Id");
query = emailQueryBuilder.Build(query, fields["emails"], emailAlias);
}
if (fields.ContainsKey("phones"))
{
var personPhoneAlias = $"{alias}PersonPhone";
var phoneAlias = $"{alias}Phone";
query
.LeftJoin($"PersonPhone {personPhoneAlias} ON {alias}.Id = {personPhoneAlias}.PersonId")
.LeftJoin($"Phone {phoneAlias} ON {personPhoneAlias}.PhoneId = {phoneAlias}.Id");
query = phoneQueryBuilder.Build(query, fields["phones"], phoneAlias);
}
if (fields.ContainsKey("supervisor"))
{
var supervisorAlias = $"{alias}Supervisor";
query.LeftJoin($"Person {supervisorAlias} ON {alias}.SupervisorId = {supervisorAlias}.Id");
query = Build(query, fields["supervisor"], supervisorAlias);
}
if (fields.ContainsKey("careerCounselor"))
{
var careerCounselorAlias = $"{alias}CareerCounselor";
query.LeftJoin($"Person {careerCounselorAlias} ON {alias}.CareerCounselorId = {careerCounselorAlias}.Id");
query = Build(query, fields["careerCounselor"], careerCounselorAlias);
}
return(query);
}
public PersonQuery(
IEntityMapperFactory entityMapperFactory,
IQueryBuilder <Person> personQueryBuilder,
IServiceProvider serviceProvider)
{
Field <ListGraphType <PersonType> >(
"people",
description: "A list of people.",
resolve: context =>
{
var alias = "person";
var query = SqlBuilder.From($"Person {alias}");
query = personQueryBuilder.Build(query, context.FieldAst, alias);
// Create a mapper that understands how to uniquely identify the 'Person' class.
var personMapper = entityMapperFactory.Build <Person>(person => person.Id);
using (var connection = serviceProvider.GetRequiredService <IDbConnection>())
{
var results = query.Execute(connection, personMapper);
return(results);
}
}
);
Field <PersonType>(
"person",
description: "Gets a person by ID.",
arguments: new QueryArguments(
new QueryArgument <IntGraphType> {
Name = "id", Description = "The ID of the person."
}
),
resolve: context =>
{
var id = context.Arguments["id"];
var alias = "person";
var query = SqlBuilder
.From($"Person {alias}")
.Where($"{alias}.Id = @id", new { id });
query = personQueryBuilder.Build(query, context.FieldAst, alias);
// Create a mapper that understands how to uniquely identify the 'Person' class.
var personMapper = entityMapperFactory.Build <Person>(person => person.Id);
using (var connection = serviceProvider.GetRequiredService <IDbConnection>())
{
var results = query.Execute(connection, personMapper);
return(results.FirstOrDefault());
}
}
);
}
public QueryContainer Create <TRequest>(TRequest request, QueryContainerDescriptor <QueryableAccount> q)
{
GetAccountListRequest accountListRequest = request as GetAccountListRequest;
if (accountListRequest == null)
{
throw new ArgumentNullException($"{nameof(request).ToString()} shouldn't be null.");
}
if (!string.IsNullOrEmpty(accountListRequest.SearchText))
{
_queryBuilder
.WithWildstarQuery(accountListRequest.SearchText,
new List <string> {
"paymentReference", "tenure.fullAddress", "tenure.primaryTenants.fullName"
})
.WithExactQuery(accountListRequest.SearchText,
new List <string> {
"paymentReference", "tenure.fullAddress", "tenure.primaryTenants.fullName"
});
}
return(_queryBuilder.Build(q));
}
public FormQuery(IEntityMapperFactory entityMapperFactory,
IQueryBuilder <Form> employeeQueryBuilder, IServiceProvider serviceProvider)
{
Name = "Query";
Field <ListGraphType <FormType> >("Forms"
, resolve: context =>
{
var alias = "form";
var query = SqlBuilder
.From($"Form {alias}");
query = employeeQueryBuilder.Build(query, context.FieldAst, alias);
var formMapper = entityMapperFactory.Build <Form>(
form => (form.Form_Id),
context.FieldAst,
query.GetSplitOnTypes()
);
using (var connection = serviceProvider.GetRequiredService <IDbConnection>())
{
var results = query.Execute(connection, formMapper);
return(results);
}
});
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Form_Id");
query.SplitOn <Form>("Form_Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "identification": query.Select($"{alias}.Identification"); break;
case "formCreationDate": query.Select($"{alias}.FormCreationDate"); break;
case "formCreationHour": query.Select($"{alias}.FormCreationHour"); break;
case "attestationStatus": query.Select($"{alias}.AttestationStatus"); break;
case "typeForm": query.Select($"{alias}.TypeForm"); break;
case "employerDeclarations":
{
var employerDeclarationAlias = $"{alias}EmployerDeclaration";
query.LeftJoin($"EmployerDeclaration {employerDeclarationAlias} ON {alias}.Form_Id = {employerDeclarationAlias}.Form_Id");
query = _employerDeclarationBuilder.Build(query, kvp.Value, employerDeclarationAlias);
}
break;
}
}
return(query);
}
public async void Build_PriceDesc3SortQueryString_ShouldReturnAllProductsWithTheSortOrder()
{
// 2.1. resolve dependency
using (var container = _builder.Build())
using (var scope = container.BeginLifetimeScope())
{
// resolve _context
CatalogApiDbContext _context = scope.Resolve <CatalogApiDbContext>();
// seed initial data in inmemory
_context = await SetupInitialDB(_context);
// 3. qs dummy
string priceDescSortQueryString = "?sort=3";
NameValueCollection qsDummy = HttpUtility.ParseQueryString(priceDescSortQueryString);
// act
IQueryBuilder <Product> productQueryBuilder = scope.Resolve <IQueryBuilder <Product> >();
var products = await productQueryBuilder.Build(qsDummy);
var result = products.Select(p => p.Price).ToList();
// assert
Assert.True(result.Zip(result.Skip(1), (a, b) => new { a, b }).All(p => p.a > p.b));
}
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Company>("Id");
var fields = context.GetSelectedFields();
if (fields.ContainsKey("name"))
{
query.Select($"{alias}.Name");
}
if (fields.ContainsKey("emails"))
{
var companyEmailAlias = $"{alias}CompanyEmail";
var emailAlias = $"{alias}Email";
query
.LeftJoin($"CompanyEmail {companyEmailAlias} ON {alias}.Id = {companyEmailAlias}.PersonId")
.LeftJoin($"Email {emailAlias} ON {companyEmailAlias}.EmailId = {emailAlias}.Id");
query = emailQueryBuilder.Build(query, fields["emails"], emailAlias);
}
if (fields.ContainsKey("phones"))
{
var companyPhoneAlias = $"{alias}CompanyPhone";
var phoneAlias = $"{alias}Phone";
query
.LeftJoin($"CompanyPhone {companyPhoneAlias} ON {alias}.Id = {companyPhoneAlias}.PersonId")
.LeftJoin($"Phone {phoneAlias} ON {companyPhoneAlias}.PhoneId = {phoneAlias}.Id");
query = phoneQueryBuilder.Build(query, fields["phones"], phoneAlias);
}
return(query);
}
public async void Build_SubCategoryQueryString_ShouldReturnAllProductsWhoseSubCategoryMatchTheQueryString()
{
// 2.1. resolve dependency
using (var container = _builder.Build())
using (var scope = container.BeginLifetimeScope())
{
// resolve _context
CatalogApiDbContext _context = scope.Resolve <CatalogApiDbContext>();
// seed initial data in inmemory
_context = await SetupInitialDB(_context);
// 3. qs dummy
string SubCategoryQueryString = "?subcategory=40";
NameValueCollection qsDummy = HttpUtility.ParseQueryString(SubCategoryQueryString);
// act
IQueryBuilder <Product> productQueryBuilder = scope.Resolve <IQueryBuilder <Product> >();
var products = await productQueryBuilder.Build(qsDummy);
var result = products.All(p => (int)p.SubCategory.Id == 40);
_output.WriteLine(JsonConvert.SerializeObject(products, Formatting.Indented));
// assert
Assert.NotEmpty(products);
Assert.True(result);
}
}
public async void Build_KeyWordQueryString_ShouldReturnAllProductsWhoseKeyWordMatchTheQueryString()
{
// 2.1. resolve dependency
using (var container = _builder.Build())
using (var scope = container.BeginLifetimeScope())
{
// resolve _context
CatalogApiDbContext _context = scope.Resolve <CatalogApiDbContext>();
// seed initial data in inmemory
_context = await SetupInitialDB(_context);
// 2.2. dummy keyword
string keywordDummy = "Table";
// 3. qs dummy
string KeyWordQueryString = "?keyword=" + keywordDummy;
NameValueCollection qsDummy = HttpUtility.ParseQueryString(KeyWordQueryString);
// act
IQueryBuilder <Product> productQueryBuilder = scope.Resolve <IQueryBuilder <Product> >();
var products = await productQueryBuilder.Build(qsDummy);
var result = products.All(p => p.Name.Contains(keywordDummy) || p.Description.Contains(keywordDummy));
_output.WriteLine(JsonConvert.SerializeObject(products, Formatting.Indented));
// assert
Assert.NotEmpty(products);
Assert.True(result);
}
}
public QueryContainer Create <TRequest>(TRequest request, QueryContainerDescriptor <QueryablePerson> q)
{
if (!(request is GetPersonListRequest personListRequest))
{
throw new ArgumentNullException($"{nameof(request).ToString()} shouldn't be null.");
}
_queryBuilder
.WithWildstarQuery(personListRequest.SearchText,
new List <string> {
"firstname", "surname"
})
.WithExactQuery(personListRequest.SearchText,
new List <string> {
"firstname", "surname"
}, new ExactSearchQuerystringProcessor());
if (personListRequest.PersonType.HasValue)
{
_queryBuilder.WithFilterQuery(string.Join(",", personListRequest.PersonType.Value.GetPersonTypes()), new List <string> {
"tenures.type"
});
}
return(_queryBuilder.Build(q));
}
public async void Build_ReviewScoreDesc9SortQueryString_ShouldReturnAllProductsWithTheSortOrder()
{
// 2.1. resolve dependency
using (var container = _builder.Build())
using (var scope = container.BeginLifetimeScope())
{
// resolve _context
CatalogApiDbContext _context = scope.Resolve <CatalogApiDbContext>();
// seed initial data in inmemory
_context = await SetupInitialDB(_context);
// 3. qs dummy
string ReviewScoreDescSortQueryString = "?sort=9";
NameValueCollection qsDummy = HttpUtility.ParseQueryString(ReviewScoreDescSortQueryString);
// act
IQueryBuilder <Product> productQueryBuilder = scope.Resolve <IQueryBuilder <Product> >();
var products = await productQueryBuilder.Build(qsDummy);
var result = products.Select(p => p.Reviews.Average(r => (int?)r.Score) ?? 0).ToList();
_output.WriteLine(JsonConvert.SerializeObject(result));
// assert
Assert.True(result.Zip(result.Skip(1), (a, b) => new { a, b }).All(p => p.a <= p.b));
}
}
public async void Build_IQueryable_ShouldConstructedWithoutWhereAndOrderByClause()
{
// 2.1. resolve dependency
using (var container = _builder.Build())
using (var scope = container.BeginLifetimeScope())
{
// resolve context
_context = scope.Resolve <CatalogApiDbContext>();
// seed initial data in inmemory
_context = await SetupInitialDB(_context);
// expectedResult
var expectedResult = ProductFaker.GetProductList(50).Count;
// 3. qs dummy
NameValueCollection qsDummy = HttpUtility.ParseQueryString("");
// act
IQueryBuilder <Product> productQueryBuilder = scope.Resolve <IQueryBuilder <Product> >();
var result = await productQueryBuilder.Build(qsDummy);
var resultCount = result.Count;
// assert
Assert.Equal(expectedResult, resultCount);
}
}
[Route("search")]// since we have multiple post actions need to mention route explicitly
public async Task <ActionResult> Query(IList <Expression> expressions)
{
try
{
_logger.LogInformation(LoggingEvents.SearchItem, "Searching for profile");
var profile = await _Repository.Query(_queryBuilder.Build(expressions));
if (profile == null)
{
_logger.LogError(LoggingEvents.SearchItem, "Something must have gone wrong in the profiles repo since its not an empty list is returned");
}
else if (profile.Count == 0)
{
return(NotFound());
}
return(Ok(profile));
}
catch (Exception ex)
{
_logger.LogError(LoggingEvents.SearchItemFailed, "Error Executing the search query: {expressions}", JsonConvert.SerializeObject(expressions));
return(StatusCode(500, ex.ToErrorResponse(LoggingEvents.SearchItemFailed)));
}
}
public async void Build_NoQueryString_ShouldReturnAllProductsWithDateAscOrder()
{
// 2.1. resolve dependency
using (var container = _builder.Build())
using (var scope = container.BeginLifetimeScope())
{
// resolve _context
_context = scope.Resolve <CatalogApiDbContext>();
// seed initial data in inmemory
_context = await SetupInitialDB(_context);
// 3. qs dummy
string noQueryString = "";
NameValueCollection qsDummy = HttpUtility.ParseQueryString("");
// act
IQueryBuilder <Product> productQueryBuilder = scope.Resolve <IQueryBuilder <Product> >();
var products = await productQueryBuilder.Build(qsDummy);
var result = products.Select(p => p.CreationDate).ToList();
_output.WriteLine(JsonConvert.SerializeObject(result, Formatting.Indented));
// assert
Assert.True(result.Zip(result.Skip(1), (a, b) => new { a, b }).All(p => DateTime.Compare(p.a.Date, p.b.Date) <= 0));
}
}
private IQuery buildFetchManyQuery(Context context, IStore store)
{
IQueryBuilder builder = store.NewQueryBuilder();
if (type != null)
{
AttributeInfo info = new AttributeInfo("category", TypeFamily.TEXT, true, null);
builder.Verify(info, MatchOp.CONTAINS, type.GetTypeName());
}
if (predicate != null)
{
if (!(predicate is IPredicateExpression))
{
throw new SyntaxError("Filtering expression must be a predicate !");
}
((IPredicateExpression)predicate).interpretQuery(context, builder);
}
if (type != null && predicate != null)
{
builder.And();
}
builder.SetFirst(InterpretLimit(context, first));
builder.SetLast(InterpretLimit(context, last));
if (include != null)
{
builder.Project(include);
}
if (orderBy != null)
{
orderBy.interpretQuery(context, builder);
}
return(builder.Build());
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Person>("Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "firstName": query.Select($"{alias}.FirstName"); break;
case "lastName": query.Select($"{alias}.LastName"); break;
case "emails":
{
var emailAlias = $"{alias}Email";
query.LeftJoin($"Email {emailAlias} ON {alias}.Id = {emailAlias}.PersonId");
query = emailQueryBuilder.Build(query, kvp.Value, emailAlias);
}
break;
case "phones":
{
var phoneAlias = $"{alias}Phone";
query.LeftJoin($"Phone {phoneAlias} ON {alias}.Id = {phoneAlias}.PersonId");
query = phoneQueryBuilder.Build(query, kvp.Value, phoneAlias);
}
break;
}
}
return(query);
}
public async Task <PagedResult <T> > RunAsync <T, TQuery>(TQuery query) where TQuery : Query
{
var result = new PagedResult <T>();
await _transactionManager
.DoInTransactionAsync(
async() =>
{
var connection = _transactionManager.GetCurrentConnection();
var compiledQuery = _queryBuilder.Build(query);
object parameters = compiledQuery.Parameters;
if (query.Size.Value > 0)
{
result.Data = (await connection.QueryAsync <T>(compiledQuery.Sql, parameters)).ToList();
}
result.Total = await connection.QueryFirstOrDefaultAsync <long>(compiledQuery.CountSql, parameters);
result.PageSize = query.Size.Value;
result.CurrentPage = query.Page.Value;
});
return(result);
}
public async void Build_MinPriceQueryString_ShouldReturnAllProductsWhoseMinPriceMatchTheQueryString()
{
// 2.1. resolve dependency
using (var container = _builder.Build())
using (var scope = container.BeginLifetimeScope())
{
// resolve _context
CatalogApiDbContext _context = scope.Resolve <CatalogApiDbContext>();
// seed initial data in inmemory
_context = await SetupInitialDB(_context);
// 2.2. dummy minprice
decimal minpriceDummy = 30000m;
// 3. qs dummy
string MinPriceQueryString = "?minprice=" + minpriceDummy;
NameValueCollection qsDummy = HttpUtility.ParseQueryString(MinPriceQueryString);
// act
IQueryBuilder <Product> productQueryBuilder = scope.Resolve <IQueryBuilder <Product> >();
var products = await productQueryBuilder.Build(qsDummy);
//var averageList = products.Select(p => p.Reviews.DefaultIfEmpty().Average(r => (int)r.Score));
var result = products.All(p => p.Price > minpriceDummy);
_output.WriteLine(JsonConvert.SerializeObject(products, Formatting.Indented));
// assert
Assert.NotEmpty(products);
Assert.True(result);
}
}
public IEnumerable <FoodTruck> GetFoodTrucks(Coordinate coordinate)
{
var _sodaClient = new SodaClient(_appSettingsManager.AppSettings.Value.SanFranciscoFoodTruckApiUrl, _appSettingsManager.AppSettings.Value.AppToken);
var resource = _sodaClient.GetResource <Dictionary <string, object> >(_appSettingsManager.AppSettings.Value.ResourceId);
IQueryBuilder queryBuilder = _queryBuilders.Where(q => q.IsQueryType(QueryType.Within)).Single();
string query = queryBuilder.Build(coordinate.ConvertToFieldFilters());
var soql = _soqlQuery.Where(query).Limit(_appSettingsManager.AppSettings.Value.SoqlQueryLimit);
var results = resource.Query <FoodTruck>(soql);
return(results);
}
private void ExecuteQueryEntries(IQueryBuilder query, Action <DynamicData> fillArg, List <DynamicData> datas)
{
using (var temp = SqlHelper.BorrowData())
{
var param = temp.Item;
fillArg(param);
var sql = query.Build(param);//编译表达式获取执行文本
SqlHelper.Query(this.ConnectionName, sql, param, datas);
}
}
private void Execute(IQueryBuilder query, Action <DynamicData> fillArg)
{
using (var temp = SqlHelper.BorrowData())
{
var param = temp.Item;
fillArg(param);
var sql = query.Build(param, this);
SqlHelper.Execute(this.ConnectionName, sql, param);
}
}
private void SaveChanges()
{
kernel.ClearTimeout(scheduleHandler);
scheduleHandler = null;
try
{
lock (SyncLock)
{
var queue = BuildSaveQueue();
if (queue.Count > 0)
{
logger.LogDebug("Saving all pending changes to the database.");
}
using (var con = db.GetConnection())
{
con.Open();
while (queue.TryPeek(out var saveData))
{
var query = queryBuilder.Build(saveData);
if (query == null)
{
return;
}
var command = con.CreateCommand();
command.CommandText = query.Command;
var result = command.ExecuteNonQuery();
if (result == 0)
{
logger.LogError("Unable to save data! Abort Query failed");
return;
}
ClearChangeSetState(saveData);
queue.Dequeue();
}
con.Close();
}
}
}
catch (Exception exc)
{
logger.LogInformation("ERROR SAVING DATA!! " + exc);
}
finally
{
ScheduleNextSave();
}
}
private void ExecuteQueryEntry(IQueryBuilder query, Action <DynamicData> fillArg, DynamicData data)
{
using (var temp = SqlHelper.BorrowData())
{
var param = temp.Item;
fillArg(param);
AddToTenant(param);
var sql = query.Build(param, this);//编译表达式获取执行文本
SqlHelper.QueryFirstOrDefault(this.ConnectionName, sql, param, data);
}
}
public async Task <IList <ProductDTO> > GetList(NameValueCollection qs)
{
IList <Product> results = await _queryBuilder.Build(qs);
_logger.LogDebug("result of product query satoshi: {@Result}", results);
// 2. assign results to List<Product> DTO
IList <ProductDTO> products = this._mapper.Map <IList <Product>, IList <ProductDTO> >(results);
// 3. return it!!
return(products);
}
///// <summary>
///// 根据表达式从数据库中查询数据
///// </summary>
///// <param name="expression"></param>
///// <param name="fillArg"></param>
///// <param name="level"></param>
///// <returns></returns>
//private object LoadObject(string expression, Action<DynamicData> fillArg, QueryLevel level)
//{
// var exp = QueryObject.Create(this, expression, level);
// return ExecuteQueryObject(this.ImplementOrElementType, exp, fillArg);
//}
private DomainObject ExecuteQueryObject(Type objectType, IQueryBuilder query, Action <DynamicData> fillArg)
{
using (var temp = SqlHelper.BorrowData())
{
var param = temp.Item;
fillArg(param);
var sql = query.Build(param);//编译表达式获取执行文本
var data = SqlHelper.QueryFirstOrDefault(this.ConnectionName, sql, param);
return(CreateObject(objectType, data));
}
}
public async void Build_AllQueryString_ShouldReturnAllProductsWhoseAllMatchTheQueryString()
{
// 2.1. resolve dependency
using (var container = _builder.Build())
using (var scope = container.BeginLifetimeScope())
{
// resolve _context
CatalogApiDbContext _context = scope.Resolve <CatalogApiDbContext>();
// seed initial data in inmemory
_context = await SetupInitialDBForAllQueryString(_context);
// 2.2. dummy all
int categoryIdDummy = 0;
int subCategoryIdDummy = 0;
string keywordDummy = "collaborative";
int reviewscoreDummy = 5;
decimal maxpriceDummy = 70000m;
decimal minpriceDummy = 60000m;
// 3. qs dummy
string AllQueryString = "?category=" + categoryIdDummy
+ "&subcategory=" + subCategoryIdDummy
+ "&keyword=" + keywordDummy
+ "&reviewscore=" + reviewscoreDummy
+ "&maxprice=" + maxpriceDummy
+ "&minprice=" + minpriceDummy;
NameValueCollection qsDummy = HttpUtility.ParseQueryString(AllQueryString);
// act
IQueryBuilder <Product> productQueryBuilder = scope.Resolve <IQueryBuilder <Product> >();
var products = await productQueryBuilder.Build(qsDummy);
//var averageList = products.Select(p => p.Reviews.DefaultIfEmpty().Average(r => (int)r.Score));
var result = products.All(p =>
{
return((int)p.SubCategory.CategoryId == categoryIdDummy &&
(int)p.SubCategory.Id == subCategoryIdDummy &&
p.Name.Contains(keywordDummy) || p.Description.Contains(keywordDummy) &&
Math.Round(p.Reviews.DefaultIfEmpty().Average(r => (int)r.Score)) == reviewscoreDummy &&
p.Price < maxpriceDummy &&
p.Price > minpriceDummy);
});
_output.WriteLine(JsonConvert.SerializeObject(_context.Products, Formatting.Indented));
// assert
Assert.NotEmpty(products);
Assert.True(result);
}
}
private int GetCount(IQueryBuilder query, Action <DynamicData> fillArg)
{
int count = 0;
//获取总数据数
using (var temp = SqlHelper.BorrowData())
{
var param = temp.Item;
fillArg(param);
var sql = query.Build(param);
count = SqlHelper.ExecuteScalar <int>(this.ConnectionName, sql, param);
}
return(count);
}
public static SqlQueryContext GetQuery(this IPersonRepository personRepository,
ResolveConnectionContext <object> context,
IQueryBuilder <Person> personQueryBuilder,
string sWhere = "")
{
var alias = "Person";
var query = SqlBuilder
.From <Person>(alias)
.OrderBy($"{alias}.CreateDate");
query = !string.IsNullOrEmpty(sWhere) ? query.Where(sWhere) : query;
return(personQueryBuilder.Build(query, context.FieldAst, alias));
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Person>("Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "firstName": query.Select($"{alias}.FirstName"); break;
case "lastName": query.Select($"{alias}.LastName"); break;
case "emails":
{
var emailAlias = $"{alias}Email";
query.LeftJoin($"Email {emailAlias} ON {alias}.Id = {emailAlias}.PersonId");
query = emailQueryBuilder.Build(query, kvp.Value, emailAlias);
}
break;
case "phones":
{
var phoneAlias = $"{alias}Phone";
query.LeftJoin($"Phone {phoneAlias} ON {alias}.Id = {phoneAlias}.PersonId");
query = phoneQueryBuilder.Build(query, kvp.Value, phoneAlias);
}
break;
}
}
if (fields.ContainsKey("supervisor"))
{
var supervisorAlias = $"{alias}Supervisor";
query.LeftJoin($"Person {supervisorAlias} ON {alias}.SupervisorId = {supervisorAlias}.Id");
query = Build(query, fields["supervisor"], supervisorAlias);
}
if (fields.ContainsKey("careerCounselor"))
{
var careerCounselorAlias = $"{alias}CareerCounselor";
query.LeftJoin($"Person {careerCounselorAlias} ON {alias}.CareerCounselorId = {careerCounselorAlias}.Id");
query = Build(query, fields["careerCounselor"], careerCounselorAlias);
}
return(query);
}
public ISitecoreSearchResult Execute(IQueryBuilder queryBuilder, int noOfResults = Int32.MaxValue - 1, Sort sort = null)
{
return Execute(queryBuilder.Build(), noOfResults, sort);
}
