Apply filters only if parameters has values in linq

Question

Iam using LINQ to retrieve records from the database. Everything is working fine, except I need to apply the different filters in a single query. If the parameters are not Null and greater than zero then apply filters. Otherwise, if they are Null or zero, then don't apply the filters. Below is the example:

bankId(int) and accountNumber(string) are my two parameters.
if bankId > 0 then get the records matching the bankId, if accountnumber is passed then get the records matching the accountnumber.
If both are passed pull all the records matching with bankid and accountnumber.
If both are not passed then return all records without applying filter.

Currently I am able to achieve it like this, Without applying any filter I am fetching all records and then if bankId is passed applying filter on the above result and if accountnumber is passed again applying filter on the above result.

But the issue here is this approach is leading to multiple if conditions which I don't want.

Is there any best and simple way to apply these filters in single query and get the result?

Please suggest, Thanks in advance.

There are several duplicate questions. You can't chain LINQ methods to create new queries as needed. You can check whether the parameter has a value and call `query=query.Where(...)` to add another `WHERE` condition, effectively ANDing them — Panagiotis Kanavos, Feb 03 '22 at 13:16
Do it like i did it in my question: https://stackoverflow.com/questions/23674778/linq-differences-between-single-where-with-multiple-conditions-and-consecutive Use multiple `Where`. Important: Don't execute the query in the `if`s, for example with `ToList`. — Tim Schmelter, Feb 03 '22 at 13:18
`leading to multiple if conditions which I don't want.` what does this mean? What does your code look like? You could replace multiple `if`s with a switch expression. On the other hand, only two `if`s are needed here — Panagiotis Kanavos, Feb 03 '22 at 13:18
Along with the problem you should **include the code you have up until this point** (*make some effort to answer your problem/question as [so] is not a code writing service*). After [doing more research](http://meta.stackoverflow.com/questions/261592) if you have a problem you can **post what you've tried** with a **clear explanation of what isn't working** and providing a **[mcve]**. I suggest reading [*How do I ask a Good Question*](/help/how-to-ask) and [*Writing the Perfect Question*](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/). — Igor, Feb 03 '22 at 13:20

score 1 · Answer 1 · answered Feb 03 '22 at 13:22

build your statement this way

var items = context.accounts.AsQueryable();
if (bankId > 0)
{
    items = items.Where(x => x.bankId == bankId);
}
if (!string.IsNullOrEmpty(accountnumber))
{
    items = items.Where(x => x.accountnumber == accountnumber);
}
var result = items.ToList(); // query db / materialize here!

Sadik · Answer 2 · 2022-02-11T13:12:06.043

You can define a function and filter by this function using LINQ operation. C# supports functional programming in linq operations.

Here below I defined a function(filterAccounts) that returns boolean if satisfies the requirements.

After that I sent this function as a parameter to Linq Where method. Where method call's filterAccounts method for each element and if returns true, than yields it and finally returns all elements that returns true.

I hope it helps.

List items = getItemsAslist();
    
    Func<item, bool> filterAccounts = (i) => 
        {
            return (bankId > 0 ? i.bankId == bankId : true) 
                   && (!string.IsNullOrEmpty(accountnumber) ? accountNumber == i.accountNumber : true)
        };
    
    items = items.Where(filterAccounts);

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Feb 03 '22 at 14:13

Apply filters only if parameters has values in linq

2 Answers2