Using the Power Tools I have create the followiing code. I need to present Assigneed People The table Document hast the assignees Ids this Ids belong to personnel. Documets are assinged to personnel.
So the field Assignee in the Document Repository has only the Ids of the Personel in the format 1,2,5,12,11, etc. So What will be the best way to replace this numbers by names in the AppService. This app service it is already generated. Assignne has to have the names to be presented to the user.
This is the picture where the numbers appear
Below is the code the (//) are the parts that I been done however I have This error (There is already an open DataReader associated with this Command which must be closed first) in this line ( var btRecord = _tenantPersonnelRepository.FirstOrDefault(id);)
Note: I can replace it at times the Ids for the Names and the changes are made it in the row tables without inserting option... This is really wear.
``
public async Task<PagedResultDto<GetTenantDocumentForViewDto>> GetAll(GetAllTenantDocumentsInput input)
{
// //Add Assignees Full Name.
// var tenantDocu = _tenantDocumentRepository.GetAll().ToList();
// if (tenantDocu.Count() > 0)
// {
// foreach (var Item in tenantDocu)
// {
// if (Item.Assignees != null && Item.Assignees != "")
// {
// string bt = Item.Assignees;
// Item.Assignees = "";
// string[] splitbt = bt.Split(new char[] { ',' });
// int[] myIntsbt = splitbt.Select(int.Parse).ToArray();
// try
// {
// var assigneed = "";
// for (int i = 0; i < splitbt.Length; i++)
// {
// var id = myIntsbt[i];
// var btRecord = _tenantPersonnelRepository.FirstOrDefault(id);
// assigneed = assigneed + btRecord.FirstName + " " + btRecord.LastName + " ";
// }
// Item.Assignees = assigneed;
// }
// catch (Exception ex)
// {
// throw new UserFriendlyException("Error Adding Assignees..." + ex.Message, ex.Message);
// };
// };
// };
// };
var filteredTenantDocuments = _tenantDocumentRepository.GetAll()
.Include( e => e.DocumentTypeFk)
.Include( e => e.DocumentStatuFk)
.WhereIf(!string.IsNullOrWhiteSpace(input.Filter), e => false || e.Name.Contains(input.Filter) || e.Description.Contains(input.Filter) || e.DocumentPath.Contains(input.Filter) || e.Assignees.Contains(input.Filter))
.WhereIf(!string.IsNullOrWhiteSpace(input.NameFilter), e => e.Name == input.NameFilter)
.WhereIf(!string.IsNullOrWhiteSpace(input.DescriptionFilter), e => e.Description == input.DescriptionFilter)
.WhereIf(!string.IsNullOrWhiteSpace(input.DocumentPathFilter), e => e.DocumentPath == input.DocumentPathFilter)
.WhereIf(input.MinSignDateFilter != null, e => e.SignDate >= input.MinSignDateFilter)
.WhereIf(input.MaxSignDateFilter != null, e => e.SignDate <= input.MaxSignDateFilter)
.WhereIf(input.MinStartDateFilter != null, e => e.StartDate >= input.MinStartDateFilter)
.WhereIf(input.MaxStartDateFilter != null, e => e.StartDate <= input.MaxStartDateFilter)
.WhereIf(input.MinExpirationDateFilter != null, e => e.ExpirationDate >= input.MinExpirationDateFilter)
.WhereIf(input.MaxExpirationDateFilter != null, e => e.ExpirationDate <= input.MaxExpirationDateFilter)
.WhereIf(input.MinReviewDateFilter != null, e => e.ReviewDate >= input.MinReviewDateFilter)
.WhereIf(input.MaxReviewDateFilter != null, e => e.ReviewDate <= input.MaxReviewDateFilter)
.WhereIf(!string.IsNullOrWhiteSpace(input.AssigneesFilter), e => e.Assignees == input.AssigneesFilter)
.WhereIf(!string.IsNullOrWhiteSpace(input.DocumentTypeNameFilter), e => e.DocumentTypeFk != null && e.DocumentTypeFk.Name == input.DocumentTypeNameFilter)
.WhereIf(!string.IsNullOrWhiteSpace(input.DocumentStatuNameFilter), e => e.DocumentStatuFk != null && e.DocumentStatuFk.Name == input.DocumentStatuNameFilter);
// HERE IS WHERE THE INSERTION ON THE TABLE HABPPENS ... THIS IS WEAR... WHY INSERT IN THE DABASE... CHANGE THE IDS FOR NAMES IN THE TABLE.
//foreach (var item in filteredTenantDocuments)
//{
// if (item.Assignees != null && item.Assignees != "")
// {
// var fRecord = tenantDocu.FirstOrDefault(x => x.Id == item.Id);
// item.Assignees = fRecord.Assignees;
// }
//}
var pagedAndFilteredTenantDocuments = filteredTenantDocuments
.OrderBy(input.Sorting ?? "id asc")
.PageBy(input);
var tenantDocuments = from o in pagedAndFilteredTenantDocuments
join o1 in _lookup_documentTypeRepository.GetAll() on o.DocumentTypeId equals o1.Id into j1
from s1 in j1.DefaultIfEmpty()
join o2 in _lookup_documentStatuRepository.GetAll() on o.DocumentStatuId equals o2.Id into j2
from s2 in j2.DefaultIfEmpty()
select new GetTenantDocumentForViewDto() {
TenantDocument = new TenantDocumentDto
{
Name = o.Name,
Description = o.Description,
DocumentPath = o.DocumentPath,
SignDate = o.SignDate,
StartDate = o.StartDate,
ExpirationDate = o.ExpirationDate,
ReviewDate = o.ReviewDate,
Assignees = o.Assignees,
Id = o.Id
},
DocumentTypeName = s1 == null ? "" : s1.Name.ToString(),
DocumentStatuName = s2 == null ? "" : s2.Name.ToString()
};
//foreach (var item in tenantDocuments.)
//{
// if (item.Assignees != null && item.Assignees != "")
// {
// var fRecord = tenantDocu.FirstOrDefault(x => x.Id == item.Id);
// item.Assignees = fRecord.Assignees;
// }
//}
var totalCount = await filteredTenantDocuments.CountAsync();
return new PagedResultDto<GetTenantDocumentForViewDto>(
totalCount,
await tenantDocuments.ToListAsync()
);
}
``
3 Answer(s)
-
0
Hi @AstarIT
It is better to get a list with field containing comma separeted Id values first. After retreiving the list of records, you can manually split comma separated values into an integer list and make a second DB query to get Assignee Names. After that, you can modify result DTOs to set Assignee names.
-
0
Ok thank you I will try your approach
-
0
Let us know if you face any problems while doing that.
