Sorting and filtering through EF

Question

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": .....

Answer 1

The method GetAsync expect a filter and a sort. I guess that method can filter and sort directly in DB, which will be more efficient (instead of retrieving all the data in memory to then filter, sort).

I recommend to use that like :

Expression<Func<BlackList, bool>> mainPredicate = x =>
    (!filterByLastName || x.LastName == lastName) &&
    (!filterByFirstName || x.FirstName == firstName) &&
    (!filterByMiddleName || x.MiddleName == middleName);

Func<IQueryable<BlackList>, IOrderedQueryable<BlackList>>? sort = null;
switch (request.SortBy)
{
    case "LastName":
        sort = q => q.OrderBy(x => x.LastName);
        break;
    case "LastNameDecs":
        sort = q => q.OrderByDescending(x => x.LastName);
        break;
    case "FirstName":
        sort = q => q.OrderBy(x => x.FirstName);
        break;
    case "FirstNameDecs":
        sort = q => q.OrderByDescending(x => x.FirstName);
        break;
    ...
}

var result = await _readonlyRepository.GetAsync(cancellationToken, mainPredicate, sort);

The example don't retake all filter parameters, but if you understand the idea you can complete this easily.

The trick is to write the mainPredicate it at one shot. Else, you will need to write manually a expression.

Answer 2

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));

Answer 3

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!