Base solution for your next web application
Open Closed

ASP.NET Zero Power Tools App Service Replace Ids by Names #9024


User avatar
0
AstarIT created

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)
  • User Avatar
    0
    ismcagdas created
    Support Team

    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.

  • User Avatar
    0
    AstarIT created

    Ok thank you I will try your approach

  • User Avatar
    0
    ismcagdas created
    Support Team

    Let us know if you face any problems while doing that.