Sorting and filtering through EF-CodePudding

I did sorting and filtering on the client. But as I understand it is not correct. I have a method to get data from a service.

Task<Result<IEnumerable<T>>> GetAsync(CancellationToken cancellationToken, 
        Expression<Func<T, bool>>? filter = null,
        Func<IQueryable<T>, IOrderedQueryable<T>>? sort = null, 
        string? include = null);

How do I write a query if I have 5 criteria for filtering (First Name, Last Name...)

I'm new to C#, so this might be a dumb question. Now I have sorting and filtering like this :( used in the project EF and PgSql

 public async Task<IOrderedEnumerable<BlackList>> Handle(GetBlackListRequest request, CancellationToken cancellationToken)
    {
        IOrderedEnumerable<BlackList> x = null;
        var result = await _readonlyRepository.GetAsync(cancellationToken);
        var dataForSort = result.Value.Select(x => new BlackList
        {
            ItemId = x.Id,
            LastName = x.LastName,
            FirstName = x.FirstName,
            MiddleName = x.MiddleName,
            PhoneNumber = x.PhoneNumber,
            CreateDate = x.CreateDate,
            //Status = x.Status
        }).ToList();

        string? lastName = request.LastNameS,
            firstName = request.FirstNameS,
            middleName = request.MiddleNameS,
            phone = request.PhoneNameS;
        DateTime? date = null;

        bool filterByLastName = true,
            filterByFirstName = false,
            filterByMiddleName = false,
            filterByPhone = false,
            filterByDate = true;

        Func<BlackList, bool> predicateByLastName = x => x.LastName == lastName;
        Func<BlackList, bool> predicateByFirstName = x => x.FirstName == firstName;
        Func<BlackList, bool> predicateByMiddleName = x => x.MiddleName == middleName;
        Func<BlackList, bool> predicateByPhone = x => x.PhoneNumber == phone;
        Func<BlackList, bool> predicateByDate = x => x.CreateDate == date;

        Func<BlackList, bool> mainPredicate = x => (!filterByLastName || predicateByLastName(x))
                                                && (!filterByFirstName || predicateByFirstName(x))
                                                && (!filterByMiddleName || predicateByMiddleName(x))
                                                && (!filterByPhone || predicateByPhone(x))
                                                && (!filterByDate || predicateByDate(x));

        foreach (var entity in dataForSort.Where(mainPredicate))
        {
            Console.WriteLine(entity);
        }


        switch (request.SortBy)
        {
            case "LastName":
                x = dataForSort.OrderBy(x => x.LastName);
                break;
            case "LastNameDecs": .....

CodePudding user response：

You have GetAsync which expect a filter and a sort lambda parameter. Use it, instead of filtering and ordering AFTER you fetch de data do it WHEN you fetch data.

It can be achieved like this:

  Expression<Func<<BlackList, bool>> filter = x => x.LastName == request.LastNameS && x.CreateDate == null; 
Func<BlackList, bool> sort = x => x.LastName; 
var result = await _readonlyRepository.GetAsync(cancellationToken, filter , sort);

Why did you use hardcoded values to set if filter will be used or not ? I don't understand your point. If you know already which filter can be applied don't over do it by using conditions afterwards, just use the filter you need.

That's a waste of time

string? lastName = request.LastNameS,
            firstName = request.FirstNameS,
            middleName = request.MiddleNameS,
            phone = request.PhoneNameS;
        DateTime? date = null;

        bool filterByLastName = true,
            filterByFirstName = false,
            filterByMiddleName = false,
            filterByPhone = false,
            filterByDate = true;

        Func<BlackList, bool> predicateByLastName = x => x.LastName == lastName;
        Func<BlackList, bool> predicateByFirstName = x => x.FirstName == firstName;
        Func<BlackList, bool> predicateByMiddleName = x => x.MiddleName == middleName;
        Func<BlackList, bool> predicateByPhone = x => x.PhoneNumber == phone;
        Func<BlackList, bool> predicateByDate = x => x.CreateDate == date;

        Func<BlackList, bool> mainPredicate = x => (!filterByLastName || predicateByLastName(x))
                                                && (!filterByFirstName || predicateByFirstName(x))
                                                && (!filterByMiddleName || predicateByMiddleName(x))
                                                && (!filterByPhone || predicateByPhone(x))
                                                && (!filterByDate || predicateByDate(x));

CodePudding user response：

So what you could do is this:

Declare a variable query that is queryable: var query = context.Persons.AsQueryable(); in this case context is your DbContext and Persons is your class (probably a table in the database).

Now using the query above if you want to sort something you could do:

query = query.Where(c => c.PearsonFirstName == FirstName)

And then you could do it again like so:

query = query.Where(c => c.PearsonLastName == LastName)

The queries basically stack on top of each other. Use if statement to sort only specific parameters, hope this helps somehow!