asp.net boilerplate IRepository get all include relationship soft delete wrong paginate and count

Question

I have this code that tries to get all records from Experience including Tenant relationship.

public async Task<PagedResultDto<GetExperienceForGuestDto>> GetSearchExperienceForGuest(
            GetExperiencesForGuestInput input)
        {
            return await _unitOfWorkManager.WithUnitOfWorkAsync(async () =>
            {
                using (_unitOfWorkManager.Current.DisableFilter(AbpDataFilters.MustHaveTenant))
                {
                    var filteredExperiences = _experienceRepository.GetAll()
                        .Include(x => x.Tenant)
                        .Include(x => x.Calendars)
                        .Where(e => e.Status == ExperienceStatus.Approve)
                        .WhereIf(!string.IsNullOrWhiteSpace(input.SearchTitle),
                            e => e.Title.Contains(input.SearchTitle) || e.Description.Contains(input.SearchTitle))
                        .WhereIf(input.BookingDate != default,
                            e => e.Calendars.Any(c =>
                                c.Date.Date == input.BookingDate.Date && c.Type != Calendar.Enums.CalendarType.Reject))
                        .WhereIf(input.TenantId != null, e => e.TenantId == input.TenantId)
                        .OrderBy(input.Sorting ?? "id asc");
                    
                    var query = filteredExperiences.PageBy(input);

                    var dbList = await query.ToListAsync();
                    
                    var results = new List<GetExperienceForGuestDto>();

                    foreach (var o in dbList)
                    {
                        var res = new GetExperienceForGuestDto()
                        {
                            Title = o.Title,
                            Description = StringHelper.TruncateAndStripHtml(o.Description),
                            Tenant = ObjectMapper.Map<ExperienceTenantDto>(o.Tenant),
                            Thumbnail = _webUrlService.GetUploadedImageUrl(o.Thumbnail),
                            Id = o.Id,
                            CreationTime = o.CreationTime
                        };

                        results.Add(res);
                    }

                    return new PagedResultDto<GetExperienceForGuestDto>(
                        await filteredExperiences.CountAsync(),
                        results
                    );
                }
            });
        }

The problem is the totalCount and items return different results.

With default MaxResultCount = 10 I got the response:

Total: 16

return: 4 items

With default MaxResultCount = 100 I got the response:

Total: 16

return: 10 items

I found out that there are some Experience that have Tenant had been soft-deleted using IHasDeletionTime and these are the records that count but don't show.

Why Count function not apply the same filter as pageBy?

Please, help me fix this problem.

Thank you!!!

Answer 1

var baseQuery = _experienceRepository.GetAll()
                        .Where(e => e.Tenant.IsDeleted == false) // Need to add this to work, I don't know if is there any better way
                        .Where(e => e.Status == ExperienceStatus.Approve)
                        .WhereIf(!string.IsNullOrWhiteSpace(input.SearchTitle),
                            e => e.Title.Contains(input.SearchTitle) || e.Description.Contains(input.SearchTitle))
                        .Where(e => e.Calendars.Any(c =>c.Date.Date == input.BookingDate.Date))
                        .WhereIf(input.TenantId != null, e => e.TenantId == input.TenantId)
                        .OrderBy(input.Sorting ?? "id asc");