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

as @Max said, the method in your repository does exactly what you asked for. In addition, whenever you are calling .ToList() on a dbContext you are actually doing the request on the database. In your code you actually are doing this in SQL : select Id, LastName,[...] from (select * from person) dataForSort where dataForSort.Lastname = 'xxx' and [...] — hunB
– hunB, Commented Aug 12, 2022 at 7:00
@hunB Yes exactly. I just want to figure out how to write the correct call to this function to apply sorting and filtering in DB — Vadim Tregubenko
– Vadim Tregubenko, Commented Aug 12, 2022 at 7:15

Max · Accepted Answer · 2022-08-12 07:01:16Z

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

edited Aug 12, 2022 at 7:01

answered Aug 12, 2022 at 6:43

Max

8704 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vadim Tregubenko Over a year ago

All right. For this, I turned here to do the fitration and sorting as it should be through the EF. I'll try your example. Thanks

Vadim Tregubenko Over a year ago

hard values were there only to test the filtering performance

Ivan Stoev Over a year ago

The answer is correct in general. But the example is not - filter must be Expression<Func<...>> and sort must be build differently.

Max Over a year ago

Ok :) now I understand :)

Vadim Tregubenko Over a year ago

@Max and for sorting it is correctly written there? Func<IQueryable<T>, IOrderedQueryable<T>>

ludovicoilgrande · Accepted Answer · 2022-08-12 06:34:42Z

1

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!

answered Aug 12, 2022 at 6:34

ludovicoilgrande

8112 bronze badges

1 Comment

Vadim Tregubenko Over a year ago

Thank you. I did so. But I'm wondering how to sort and filter using the function given in the example.

vernou · Accepted Answer · 2022-08-12 12:29:03Z

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.

Collectives™ on Stack Overflow

Sorting and filtering through EF

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related