C#•15mo ago

✅ Filtering problems in .Net Core

Hello everyone, I'm in the midst of a coding project and have encountered a challenge. I'm attempting to narrow down checklist questions by product type, but I'm encountering an issue with null product types. Even when I provide a productTypeId, I'm still getting checklist questions with null productTypeId. Any insights or suggestions would be highly valued. Your assistance is greatly appreciated! Thank you in advance!

7 Replies

_vegabyte_OP•15mo ago

public class GetAllChecklistsQuery : UserParams, IRequest<PagedList<GetAllChecklistsQueryResult>>
    {
        public int ProductTypeId { get; set; }
        public string ChecklistType { get; set; }
        public bool? Status { get; set; }
    }

public class GetAllChecklistsQuery : UserParams, IRequest<PagedList<GetAllChecklistsQueryResult>>
    {
        public int ProductTypeId { get; set; }
        public string ChecklistType { get; set; }
        public bool? Status { get; set; }
    }

public class GetAllChecklistsQueryResult
    {
            public int Id { get; set; }
            public string ChecklistType { get; set; } 
            public List<ChecklistQuestion> ChecklistQuestions { get; set; }

        public class ChecklistQuestion
        {
            public int? ProductTypeId { get; set; }
            public string ProductType { get; set; }
            public int Id { get; set; }
            public string ChecklistsQuestions { get; set; }
            public bool IsOpenField { get; set; }
            public bool IsActive { get; set; }
            public DateTime CreatedAt { get; set; }
            public DateTime UpdatedAt { get; set; }
            public string AddedBy { get; set; }
        }
    }

public class GetAllChecklistsQueryResult
    {
            public int Id { get; set; }
            public string ChecklistType { get; set; } 
            public List<ChecklistQuestion> ChecklistQuestions { get; set; }

        public class ChecklistQuestion
        {
            public int? ProductTypeId { get; set; }
            public string ProductType { get; set; }
            public int Id { get; set; }
            public string ChecklistsQuestions { get; set; }
            public bool IsOpenField { get; set; }
            public bool IsActive { get; set; }
            public DateTime CreatedAt { get; set; }
            public DateTime UpdatedAt { get; set; }
            public string AddedBy { get; set; }
        }
    }

public class Handler : IRequestHandler<GetAllChecklistsQuery, PagedList<GetAllChecklistsQueryResult>>
    {
        private readonly StoreContext _context;

        public Handler(StoreContext context)
        {
            _context = context;
        }

        public async Task<PagedList<GetAllChecklistsQueryResult>> Handle(GetAllChecklistsQuery request,
            CancellationToken cancellationToken)
        {
            IQueryable<ChecklistTypes> checklistDescriptions = _context.ChecklistTypes
                .Include(ct => ct.ChecklistQuestions)
                .ThenInclude(x => x.ProductType);

            if (request.ProductTypeId > 0)
            {
                 checklistDescriptions = checklistDescriptions.Where(x =>
                  x.ChecklistQuestions.Any(x => x.ProductTypeId.HasValue && x.ProductTypeId.Value > 0));
            }

            if (!string.IsNullOrEmpty(request.ChecklistType))
            {
                checklistDescriptions =
                    checklistDescriptions.Where(x => x.ChecklistType.Contains(request.ChecklistType));
            }

            if (request.Status != null)
            {
                checklistDescriptions = checklistDescriptions.Where(x => x.IsActive == request.Status);
            }
    }
      }

public class Handler : IRequestHandler<GetAllChecklistsQuery, PagedList<GetAllChecklistsQueryResult>>
    {
        private readonly StoreContext _context;

        public Handler(StoreContext context)
        {
            _context = context;
        }

        public async Task<PagedList<GetAllChecklistsQueryResult>> Handle(GetAllChecklistsQuery request,
            CancellationToken cancellationToken)
        {
            IQueryable<ChecklistTypes> checklistDescriptions = _context.ChecklistTypes
                .Include(ct => ct.ChecklistQuestions)
                .ThenInclude(x => x.ProductType);

            if (request.ProductTypeId > 0)
            {
                 checklistDescriptions = checklistDescriptions.Where(x =>
                  x.ChecklistQuestions.Any(x => x.ProductTypeId.HasValue && x.ProductTypeId.Value > 0));
            }

            if (!string.IsNullOrEmpty(request.ChecklistType))
            {
                checklistDescriptions =
                    checklistDescriptions.Where(x => x.ChecklistType.Contains(request.ChecklistType));
            }

            if (request.Status != null)
            {
                checklistDescriptions = checklistDescriptions.Where(x => x.IsActive == request.Status);
            }
    }
      }

Result

{
  "checklists": [
    {
      "id": 1,
      "checklistType": "Required Documents",
      "checklistQuestions": [
        {
          "productTypeId": 1,
          "productType": "ICE",
          "id": 1,
          "checklistsQuestions": "COA",
          "isOpenField": false,
          "isActive": true,
          "createdAt": "2023-09-21T13:59:36.2071666",
          "updatedAt": "0001-01-01T00:00:00",
          "addedBy": "ADMIN"
        },
        {
          "productTypeId": null,
          "productType": null,
          "id": 2,
          "checklistsQuestions": "Birth Certificate",
          "isOpenField": false,
          "isActive": true,
          "createdAt": "2023-09-21T14:07:19.3636358",
          "updatedAt": "0001-01-01T00:00:00",
          "addedBy": "ADMIN"
        }
      ]
    }
  ],
  "currentPage": 1,
  "pageSize": 10,
  "totalCount": 1,
  "totalPages": 1,
  "hasPreviousPage": false,
  "hasNextPage": false
}

{
  "checklists": [
    {
      "id": 1,
      "checklistType": "Required Documents",
      "checklistQuestions": [
        {
          "productTypeId": 1,
          "productType": "ICE",
          "id": 1,
          "checklistsQuestions": "COA",
          "isOpenField": false,
          "isActive": true,
          "createdAt": "2023-09-21T13:59:36.2071666",
          "updatedAt": "0001-01-01T00:00:00",
          "addedBy": "ADMIN"
        },
        {
          "productTypeId": null,
          "productType": null,
          "id": 2,
          "checklistsQuestions": "Birth Certificate",
          "isOpenField": false,
          "isActive": true,
          "createdAt": "2023-09-21T14:07:19.3636358",
          "updatedAt": "0001-01-01T00:00:00",
          "addedBy": "ADMIN"
        }
      ]
    }
  ],
  "currentPage": 1,
  "pageSize": 10,
  "totalCount": 1,
  "totalPages": 1,
  "hasPreviousPage": false,
  "hasNextPage": false
}

Pobiega•15mo ago

if (request.ProductTypeId > 0)
            {
                 checklistDescriptions = checklistDescriptions.Where(x =>
                  x.ChecklistQuestions.Any(x => x.ProductTypeId.HasValue && x.ProductTypeId.Value > 0));
            }

if (request.ProductTypeId > 0)
            {
                 checklistDescriptions = checklistDescriptions.Where(x =>
                  x.ChecklistQuestions.Any(x => x.ProductTypeId.HasValue && x.ProductTypeId.Value > 0));
            }

I wouldn't use x as my lambda argument for both lambdas here, as the outer one is still in scope. and that where doesn't do what you think it does it just says at least one item in the collection must have a non-null, non zero ProductTypeId and thats exactly what your response is as well, there was a checklist with at least one non-null, non-zero question

_vegabyte_OP•15mo ago

But it is possible to filter out those ProductTypeId that has a null value? I've also tried

if (!string.IsNullOrEmpty(request.ProductType))
{
    checklistDescriptions = checklistDescriptions.Where(x =>
        x.ChecklistQuestions.Any(question =>
            question.ProductType != null &&
            question.ProductType.ProductTypeName == request.ProductType));
}

if (!string.IsNullOrEmpty(request.ProductType))
{
    checklistDescriptions = checklistDescriptions.Where(x =>
        x.ChecklistQuestions.Any(question =>
            question.ProductType != null &&
            question.ProductType.ProductTypeName == request.ProductType));
}

But here is based on ProductTypeName I thought passing ProductTypeId instead of ProductTypeName will work

Pobiega•15mo ago

You do understand that Any returns true if one or more items match right? so it wont filter out only the items that match your filtering must be added to the includeQueryable

_context.ChecklistTypes
        .Include(ct => ct.ChecklistQuestions.Where(x => x.ProductTypeId != null));

_context.ChecklistTypes
        .Include(ct => ct.ChecklistQuestions.Where(x => x.ProductTypeId != null));

_vegabyte_OP•15mo ago

Oh, I misunderstand its uses. Here I only get the ChecklistQuestions that has ProductTypeId right? Should I create another API for ChecklsitQuestions that has null ProductTypeId? Since I need them also. I just want to exclude them when creating a filter base on ChecklistType.

Pobiega•15mo ago

You need to include any filtering you want for your child-items when you include them so if you want that to be conditional, you must make the include conditional

_vegabyte_OP•15mo ago

The first approach I use is I include the items I need into a IQueryable and I use If else for the conditionals. I will revise my first implementation. Thanks

Gaming

Programming

✅ Filtering problems in .Net Core