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);
}
protected override async Task <SharedBookListDto> Handle(
SqlQueryContext <GetSharedList, SharedBookListDto> context)
{
using (var reader = await DbConnection.QueryMultipleAsync(context.Sql, context.Parameters))
{
var list = (await reader.ReadAsync <SharedBookListDto>()).SingleOrDefault() ??
throw new ObjectNotExistException <BookList>(new OnExceptionObjectDescriptor
{
["Id"] = context.Query.ListId.ToString()
});
var tags = (await reader.ReadAsync <TagDto>()).ToList();
list.Tags = tags;
var items = new List <SharedBookListItemDto>(await reader.ReadAsync <SharedBookListItemDto>());
var itemsTags =
(await reader.ReadAsync <(string TagName, int BookId)>()).ToLookup(t => t.BookId);
foreach (var item in items)
{
item.Tags = itemsTags[item.BookId].Select(x => x.TagName).ToList();
}
list.Items = items;
list.Moderators = (await reader.ReadAsync <ModeratorDto>()).ToList();
return(list);
}
}
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);
}
protected override async Task <ChunkedCollectionDto <BookDto> > Handle(
SqlQueryContext <FindBooks, ChunkedCollectionDto <BookDto> > context)
{
var rows = (await DbConnection.QueryAsync <BookDbRow>(context.Sql, context.Parameters)).ToList();
if (!rows.Any())
{
return(ChunkedCollectionDto <BookDto> .Empty);
}
var books = new List <BookDto>();
foreach (var row in rows)
{
if (books.Any(a => a.Id == row.Id))
{
continue;
}
var tags = rows.Where(b => b.Id == row.Id && !string.IsNullOrEmpty(b.Tag)).Select(b => b.Tag).ToList();
books.Add(new BookDto
{
Id = row.Id,
Author = row.Author,
Title = row.Title,
Tags = tags,
Genre = row.Genre
});
}
return(new ChunkedCollectionDto <BookDto>(books, rows.Count > context.Query.Count,
context.Query.Chunk));;
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <City>("Id");
foreach (var field in context.GetSelectedFields())
{
switch (field.Key.ToLower())
{
case "name":
query.Select($"{alias}.Name");
break;
case "zipcode":
query.Select($"{alias}.ZipCode");
break;
case "country":
var countryAlias = $"{alias}Country";
query.InnerJoin($"Countries {countryAlias} on {alias}.CountryId = {countryAlias}.Id");
query = new CountryQuery().Build(query, field.Value, countryAlias);
break;
}
}
return(query);
}
/// <summary>
/// 过滤
/// </summary>
/// <param name="context">Sql查询执行上下文</param>
public void Filter(SqlQueryContext context)
{
foreach (var item in context.EntityAliasRegister.Data)
{
Filter(context.WhereClause, item.Key, item.Value);
}
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Person>("Id");
foreach (var field in context.GetSelectedFields())
{
switch (field.Key.ToLower())
{
case "firstname":
query.Select($"{alias}.FirstName");
break;
case "lastname":
query.Select($"{alias}.LastName");
break;
case "address":
var addressAlias = $"{alias}Address";
query.InnerJoin($"Addresses {addressAlias} on {alias}.AddressId = {addressAlias}.Id");
query = new AddressQuery().Build(query, field.Value, addressAlias);
break;
}
}
return(query);
}
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 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);
}
protected override async Task <PrivateBookListItemDto> Handle(
SqlQueryContext <GetPrivateListItem, PrivateBookListItemDto> context)
{
return(await DbConnection.QuerySingleOrDefaultAsync <PrivateBookListItemDto>(context.Sql,
context.Parameters) ??
throw new ObjectNotExistException <BookList>(new OnExceptionObjectDescriptor
{
["Id"] = context.Query.ItemId.ToString()
}));
}
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 SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
// Always get the ID of the email
query.Select($"{alias}.Id");
// Tell Dapper where the Email class begins (at the Id we just selected)
query.SplitOn <Email>("Id");
var fields = context.GetSelectedFields();
if (fields.ContainsKey("address"))
{
query.Select($"{alias}.Address");
}
return(query);
}
protected override async Task <PrivateBookListDto> Handle(
SqlQueryContext <GetPrivateList, PrivateBookListDto> context)
{
var list = (await DbConnection.QueryAsync <PrivateBookListDto, PrivateBookListItemDto, PrivateBookListDto>(
context.Sql, CollectItems, context.Parameters)).FirstOrDefault();
if (list == null)
{
throw new ObjectNotExistForException <BookList, User>(null, new OnExceptionObjectDescriptor
{
["Id"] = context.Query.UserId.ToString()
});
}
return(list);
}
protected override async Task <IEnumerable <SharedBookListItemDto> > Handle(
SqlQueryContext <GetSharedListItems, IEnumerable <SharedBookListItemDto> > context)
{
using (var reader = await DbConnection.QueryMultipleAsync(context.Sql, context.Parameters))
{
var items = (await reader.ReadAsync <SharedBookListItemDto>()).ToList();
var tags = (await reader.ReadAsync <(string TagName, int BookId)>()).ToLookup(t => t.BookId);
foreach (var item in items)
{
item.Tags = tags[item.BookId].Select(t => t.TagName).ToList();
}
return(items);
}
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Email>("Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "address": query.Select($"{alias}.Address"); break;
}
}
return(query);
}
protected override async Task <SharedBookListItemDto> Handle(
SqlQueryContext <GetSharedListItem, SharedBookListItemDto> context)
{
using (var reader = await DbConnection.QueryMultipleAsync(context.Sql, context.Parameters))
{
var item = await reader.ReadSingleOrDefaultAsync <SharedBookListItemDto>() ??
throw new ObjectNotExistException <SharedBookListItem>(new OnExceptionObjectDescriptor
{
["Item Id"] = context.Query.ItemId.ToString()
});
var tags = (await reader.ReadAsync <(string TagName, int BookId)>()).ToList();
item.Tags = tags.Where(t => t.BookId == item.BookId).Select(t => t.TagName);
return(item);
}
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Phone>("Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "number": query.Select($"{alias}.Number"); break;
case "type": query.Select($"{alias}.Type"); break;
}
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.EmployerDeclaration_Id");
query.SplitOn <EmployerDeclaration>("EmployerDeclaration_Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "quarter": query.Select($"{alias}.Quarter"); break;
case "CompanyID": query.Select($"{alias}.CompanyID"); break;
case "EmployerDeclaration_Id": query.Select($"{alias}.EmployerDeclaration_Id"); break;
}
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Country>("Id");
foreach (var field in context.GetSelectedFields())
{
switch (field.Key.ToLower())
{
case "name":
query.Select($"{alias}.Name");
break;
case "isocode":
query.Select($"{alias}.IsoCode");
break;
}
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Address>("Id");
foreach (var field in context.GetSelectedFields())
{
switch (field.Key.ToLower())
{
case "street":
query.Select($"{alias}.Street");
break;
case "city":
var cityAlias = $"{alias}City";
query.InnerJoin($"Cities {cityAlias} on {alias}.CityId = {cityAlias}.Id");
query = new CityQuery().Build(query, field.Value, cityAlias);
break;
}
}
return(query);
}
protected override async Task <ChunkedCollectionDto <SharedBookListPreviewDto> > Handle(
SqlQueryContext <GetUserSharedLists, ChunkedCollectionDto <SharedBookListPreviewDto> > context)
{
var rows = (await DbConnection.QueryAsync <FindSharedBookListsQueryHandler.SharedListDbRow>(context.Sql, context.Parameters)).ToList();
if (!rows.Any())
{
return(ChunkedCollectionDto <SharedBookListPreviewDto> .Empty);
}
var items = rows.Take(context.Query.Count).Select(r => new SharedBookListPreviewDto
{
Id = r.Id,
OwnerId = r.OwnerId,
Name = r.Name,
Type = r.Type,
BooksCount = r.BookCount,
Tags = r.Tags?.Split(',').Where(t => !string.IsNullOrEmpty(t)).ToList() ?? new List <string>()
}).ToList();
return(new ChunkedCollectionDto <SharedBookListPreviewDto>(items, rows.Count > context.Query.Count,
context.Query.Chunk));
}
/// <summary>
/// 添加过滤器列表
/// </summary>
private void AddFilters(IWhereClause whereClause)
{
var context = new SqlQueryContext(AliasRegister, whereClause);
SqlFilterCollection.Filters.ForEach(filter => filter.Filter(context));
}
protected abstract Task <TResult> Handle(SqlQueryContext <TQuery, TResult> context);
protected override async Task <IEnumerable <TagDto> > Handle(SqlQueryContext <GetTags, IEnumerable <TagDto> > context)
{
return(await DbConnection.QueryAsync <TagDto>(context.Sql));
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
var mergedAlias = $"{alias}Merged";
// Deduplicate the person
query.LeftJoin($"Person {mergedAlias} ON {alias}.MergedToPersonId = {mergedAlias}.MergedToPersonId");
query.Select($"{alias}.Id", $"{alias}.MergedToPersonId");
query.SplitOn <Person>("Id");
var fields = QueryBuilderHelper.CollectFields(context.SelectionSet);
if (QueryBuilderHelper.IsConnection(context.SelectionSet))
{
query.Select($"{alias}.CreateDate");
}
if (fields.ContainsKey("firstName"))
{
query.Select($"{mergedAlias}.FirstName");
}
if (fields.ContainsKey("lastName"))
{
query.Select($"{mergedAlias}.LastName");
}
if (fields.ContainsKey("companies"))
{
var personCompanyAlias = $"{alias}PersonCompany";
var companyAlias = $"{alias}Company";
query
.LeftJoin($"PersonCompany {personCompanyAlias} ON {mergedAlias}.Id = {personCompanyAlias}.PersonId")
.LeftJoin($"Company {companyAlias} ON {personCompanyAlias}.CompanyId = {companyAlias}.Id");
query = companyQueryBuilder.Build(query, fields["companies"], companyAlias);
}
if (fields.ContainsKey("emails"))
{
var personEmailAlias = $"{alias}PersonEmail";
var emailAlias = $"{alias}Email";
query
.LeftJoin($"PersonEmail {personEmailAlias} ON {mergedAlias}.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 {mergedAlias}.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 {mergedAlias}.SupervisorId = {supervisorAlias}.Id");
query = Build(query, fields["supervisor"], supervisorAlias);
}
if (fields.ContainsKey("careerCounselor"))
{
var careerCounselorAlias = $"{alias}CareerCounselor";
query.LeftJoin($"Person {careerCounselorAlias} ON {mergedAlias}.CareerCounselorId = {careerCounselorAlias}.Id");
query = Build(query, fields["careerCounselor"], careerCounselorAlias);
}
return(query);
}
protected override async Task <IEnumerable <ModeratorDto> > Handle(SqlQueryContext <GetModerators, IEnumerable <ModeratorDto> > context)
{
return(await DbConnection.QueryAsync <ModeratorDto>(context.Sql, context.Parameters));
}