Filter entityframework core with conditionals in linq

Question

I am trying to make filters using linq but I want to include in the filter if the input data has valid values ​​this my code:

    public  async Task<ICollection<Carrier>> getList(Carrier carrier)
      {
                
       var objList =await _context.Carrier.Include(u => u.Usu).Include(p => p.Pai)
       .Include(d => d.Doc)
       .Include(i => i.Ins)
       .Include(a => a.Acc)
       .Include(s => s.Set)
       .Where(x=> carrier.CarCode!=null ? x.CarCode == carrier.CarCode:null 
       && carrier.UsuId != 0 ? x.UsuId == carrier.UsuId:null 
       && carrier.CarLegalname != ""  ? x.CarLegalname == carrier.CarLegalname:null)
       .ToListAsync();
          
        return objList;
    }

When you enter the non-null data or with valid values, those data must be included for the filter but if they were not entered, they should not be included in the filter

Answer 1

As I understood the question, what you basically need is the way to form Where-condition of you LINQ request dynamically, depending on values your method gets as parameters. And it is perfectly possible. The type of parameter passed to the Where method is Expression<Func<T, bool>>, where T is a type of your data model (in this case – Carrier). This type of expression is called predicate expression. And nothing prevents you from building the predicate expression you need in the runtime. The only thing is that building such an expression in runtime is a bit tricky, so it would be nice to have some helper that would allow you to use shortcuts. In my humble opinion, the best thing to use here would be the PredicateBuilder class from LinqKit package. It allows to dynamically connect boolean expressions using '''And''' and '''Or''' methods. Using LinqKit you can come up with something like this:

        public  async Task<ICollection<Carrier>> getList(Carrier carrier)
        {
            Expression<Func<Carrier, bool>> predicate = PredicateBuilder.New<Carrier>(true);
            if (carrier.CarCode != null)
                predicate = predicate.And(_ => _.CarCode == carrier.CarCode);
            if (carrier.UsuId != 0)
                predicate = predicate.And(_ => _.UsuId == carrier.UsuId);
            if (carrier.CarLegalname != null)
                predicate = predicate.And(_ => _.CarLegalname == carrier.CarLegalname);

            var objList = await _context.Carrier
                .Include(u => u.Usu).Include(p => p.Pai)
                .Include(d => d.Doc)
                .Include(i => i.Ins)
                .Include(a => a.Acc)
                .Include(s => s.Set)
                .Where(predicate)
                .ToListAsync();
          
            return objList;
        }

PS An alternative way of chaining Where methods in LINQ-query would also work. Many people use it and there is nothing wrong with it. Though it has some small drawbacks. Firstly, it gives slightly less clear (in my opinion at least) code (you repeat query.Where again and again, which does not look nice). And secondly, this way allows only connecting conditions by AND operator, and you cannot connect them by OR operator this way. And you might need it at some point. So using LinqKit gives either more clarity and flexibility.

Answer 2

.Where(x=> carrier.CarCode!=null ? x.CarCode == carrier.CarCode:null 
        && carrier.UsuId != 0 ? x.UsuId == carrier.UsuId:null 
        && carrier.CarLegalname != ""  ? x.CarLegalname == carrier.CarLegalname:null)

When you enter the non-null data or with valid values, those data must be included for the filter but if they were not entered, they should not be included in the filter

So if the input carrier.Carcode equals null, you don't want to filter, otherwise you want to filter on carries.CarCode == x.CarCode. Similar filtering with UsuId and CarLegalName

Carrier carrier = ...
var result = dbContext.Carriers
...
.Where(x => ( (carrier.CarCode == null)     || (carrier.CarCode == x.CarCode) )
         && ( (carrier.UsuId == 0)          || (carrier.UsuId == x.UsuId)     )
         && ( (carrier.CarLegalName == "" ) || (carrier.CarLegalName == x.CarLegalName) );

There is room for improvement

Check carrier only once

If you would use this code, and you have 1000 carriers, how many times will the code check that carrier.CarCode == null? Wouldn't it be better to check this only once, and create the query accordingly?

I'll do this as an extension method. If you are not familiar with extension methods, see extension methods demystified

public IQueryable<Carrier> Where(this IQueryable<Carrier> source, Carrier carrier)
{
    if (carrier == null) return source;

    if (carrier.CarCode != null)
        source = source.Where(x => x.CarCode == carrier.CarCode;
    if (carrier.UsuId != 0)
        source = source.Where(x => x.UsuId == carrier.UsuId);
    if (carrier.CarLegalName != String.Empty)
        source = source.Where(x.CarLegalName == carrier.CarLegalName);

    return source;
}

What this does, it will check the properties of carrier only once and create an IQueryable with a Where(..).Where(...).Where(...).

The query is created, it is not executed yet..

Only after you start enumerating, either explicitly using GetEnumerator() / MoveNext(), or implicitly using foreach(...) or any of the non-delayed execution LINQ methods ( = all LINQ methods that don't return IQueryable<...>), like ToList, ToDictionary, FirstOrDefault, Sum, Any, ..., the IQueryable.Expression is sent to the IQueryable.Provider, who will translate it into SQL and contact the database.

The Where(..).Where(...).Where(...) will result in a SQL similar to Where(... && ... && ...)

Usage:

var result = dbContext.Carriers
    .Where(carrier)
    .Select(x => ...)
    .ToList();

Don't use Include to fetch data, use Select

When querying data, always use Select and select only the properties that you actually plan to use. Only use Include if you plan to update the fetched data.

People tend to use Include as an alternative for Select to save some typing. However, this has several disadvantages.

You'll fetch more data then you need

Database management systems are extremely optimized in selecting data. The slower part of the process is the transfer of the selected data from the DBMS to your local process. Hence it is wise to limit the amount of transferred data.

If you use Include to fetch sub-items, it is very likely that you'll fetch properties that you will not used.

Apart from this, you'll also fetch the same value over and over again.

If you have a School database with a one-to-many relation between Schools and Students, then every School will have zero or more Students, and every Student will attend exactly one School, namely the School that the foreign key SchoolId refers to.

So every Student of this School [10] will have a value of SchoolId equal to 10. If you use Include to fetch School [10] with its 1000 Students, you will transfer this value 10 over a 1000 times. What a waste of processing power!

You'll expose the database layout

Another problem of using Include instead of Select is that you expose the table layout to your callers. This will limit your freedom to change your database. If in future your database gets an extra column, your caller suddenly gets this value, while until now he apparently didn't need this. It will even be a bigger problem if you decide to split tables, or to remove unused columns. If you use Select, you can precisely decide what data is provided and what not. If data moves to other tables, you use change the Select, users won't notice this.

Answer 3

The problem I usually run into when trying to do stuff like that in EF is that EF often doesn't know how to translate complex checks into SQL. On the bright side, the query isn't executed until the 'ToListAsync' call. Since that's the case, we can instead perform our null checks prior to EF taking over and doing its magic. This is the pattern I typically follow for something like this since I've had problems with it in the past. However, I'm not sure if this is the best practice, or if EF can handle more complex stuff like this nowadays..

    public async Task<ICollection<Carrier>> getList(Carrier carrier)
    {
        var query = _context.Carrier
            .Include(u => u.Usu)
            .Include(p => p.Pai)
            .Include(d => d.Doc)
            .Include(i => i.Ins)
            .Include(a => a.Acc)
            .Include(s => s.Set);
        if (carrier.CarCode != null) query = query.Where(x => x.CarCode == carrier.CarCode);
        if (carrier.UsuId != 0) query = query.Where(x => x.UsuId == carrier.UsuId);
        if (carrier.CarLegalname != "") query = query.Where(x => x.CarLegalname == carrier.CarLegalname);
        return await query.ToListAsync();
    }

Also, sorry if I messed up some of your variable names.. I wasn't working with Intellisense..