LINQ Filter query with multiple optional parameters

Question

I need to write a LINQ Query against a returned list that takes in multiple optional parameters. There will be the following variables:

plan, Id, FirstName, LastName, DateFrom, DateTo, MemDateOfBirth

I want to return a filtered list back via LINQ using these parameters, but all of them are optional. At least one will be supplied when the user hits the search button, but that will be up to the user to decide what they want to search by. If they supply more than 1, I need to filter by all methods they supply...

So for instance, if they supply a first name and to and from dates, I want to return a filtered list of all instances for a person by that first name between the to and from dates, etc...

What is the easiest way to accomplish this with LINQ? These variables are optional parameters, so any or all of them could be supplied. I know I could return the main list, then filter through it multiple times to get the results, but I was wondering if there was a quicker, easier way to do it via LINQ...

thanks in advance for your help!

Solution 1

i found this to be the simplest way to solve this type of problem

var q = from mt in myTable
        where (mt.FIrstname == FirstNameparam || FirstNameparam == null)
        && (mt.lastname == lastnameParam || lastnameParam == null)
        && (mt.DateField == DateParam || DateParam == null)
        select new
        {
            mt.FIrstname,
            mt.lastname,
            mt.DateField
        };

Solution 2

Yestoday i was asked this question on interview, so here are my thoughts:)

All previous answers (dynamic LINQ, multiple Where clauses) won't let you to get rid of code that checks if your optional parameters are specified - for example, to use dLINQ you still have to create a 'concatenated' string. These concatenations will be based on presence of you optional params, so why to create string (or why to cofigure filters list) if you can directly create Where-sequence in the same way?

Well, to be honest, my approach also contains this logic, but you won't see it (umm.. maybe just a little bit :) ).

---

So, what we gonna use are:

• Reflection (aka slow guy, so if you fighting for milliseconds, i suppose, you better go with multiple if-Where's)
• Attributes
• Expressions
• and of course LINQ.

At first, let me show you how it will work. Let's say, we have model:

public class Person
{
    public string Name { get; }
    public int Age { get; }

    public Person( string name, int age )
    {
        Name = name;
        Age = age;
    }
}

And also you will have.. Let's call it Filtering object.

Neat bonus! For example, if you dealing with ASP.Net Core, you can get this object automatically for GET-method, using [FromQuery].

public class PersonFilterParams : IFilterParams<Person>
{
    [Filter( FilterType.Equals )]
    public string Name { get; set; }

    [Filter( nameof( Person.Age ), FilterType.GreaterOrEquals )]
    public int? MinAge { get; set; }

    [Filter( nameof( Person.Age ), FilterType.LessOrEquals )]
    public int? MaxAge { get; set; }

    [Filter( nameof( NonExistingProp ), FilterType.LessOrEquals )]
    public int? NonExistingProp { get; set; }
}

And here is how to use it:

// you can skip properies here
var filter = new PersonFilterParams
{
    //Name = "Name 4",
    //MinAge = 2,
    MaxAge = 20,
    NonExistingProp = 20,
};

var filteredPersons = persons
    .Filter( filter )
    .ToList();

That's all!

---

Now let's see how it implemented.

In short:

we have extension-method, that accepts Filtering object, breaks it down using reflection, for non-null properties creates lambdas using expression, adds these lambdas to source IEnumerable<T>.

For type-safety filtering objects must implement generic interface IFilterParams<T>.

Code:

public enum FilterType
{
    None,

    Less,
    LessOrEquals,
    Equals,
    Greater,
    GreaterOrEquals,
}

[AttributeUsage( AttributeTargets.Property, Inherited = false, AllowMultiple = false )]
sealed class FilterAttribute : Attribute
{
    public string PropName { get; }
    public FilterType FilterType { get; }

    public FilterAttribute() : this( null, FilterType.Equals )
    {
    }

    public FilterAttribute( FilterType filterType ) : this( null, filterType )
    {
    }

    public FilterAttribute( string propName, FilterType filterType )
    {
        PropName = propName;
        FilterType = filterType;
    }
}

public interface IFilterParams<T>
{

}

public static class Extensions
{
    public static IEnumerable<T> Filter<T>( this IEnumerable<T> source, IFilterParams<T> filterParams )
    {
        var sourceProps = typeof( T ).GetProperties();
        var filterProps = filterParams.GetType().GetProperties();

        foreach ( var prop in filterProps )
        {
            var filterAttr = prop.GetCustomAttribute<FilterAttribute>();

            if ( filterAttr == null )
                continue;

            object val = prop.GetValue( filterParams );

            if ( val == null )
                continue;

            // oops.. little hole..
            if ( prop.PropertyType == typeof( string ) && (string)val == string.Empty )
                continue;

            string propName = string.IsNullOrEmpty( filterAttr.PropName )
                ? prop.Name
                : filterAttr.PropName;

            if ( !sourceProps.Any( x => x.Name == propName ) )
                continue;

            Func<T, bool> filter = CreateFilter<T>( propName, filterAttr.FilterType, val );

            source = source.Where( filter );
        }

        return source;
    }

    private static Func<T, bool> CreateFilter<T>( string propName, FilterType filterType, object val )
    {
        var item = Expression.Parameter( typeof( T ), "x" );
        var propEx = Expression.Property( item, propName );
        var valEx = Expression.Constant( val );

        Expression compareEx = null;

        switch ( filterType )
        {
            case FilterType.LessOrEquals:
                compareEx = Expression.LessThanOrEqual( propEx, valEx );
                break;

            case FilterType.Less:
                compareEx = Expression.LessThan( propEx, valEx );
                break;

            case FilterType.Equals:
                compareEx = Expression.Equal( propEx, valEx );
                break;

            case FilterType.Greater:
                compareEx = Expression.GreaterThan( propEx, valEx );
                break;

            case FilterType.GreaterOrEquals:
                compareEx = Expression.GreaterThanOrEqual( propEx, valEx );
                break;

            default:
                throw new Exception( $"Unknown FilterType '{filterType}' on property '{propName}'!" );
        }

        var lambda = Expression.Lambda<Func<T, bool>>( compareEx, item );

        Func<T, bool> filter = lambda.Compile();

        return filter;
    }
}

Solution 3

Assuming that you don't want to generalize that for many tables, I would do something like:

var query = 
    from m in db.table
    select m;

if (plan.HasValue)
{
    query = query.Where( x => x.plan == plan.Value);
}

... other filters ....

// Then use data (for ex. make a list).
var list = query.ToList();

Depending on the provider, you might also write condition directly in LINQ with something like:

from m id db.table
where plan == null || m.plan == plan
where...
select m;

or using !plan.HasValue || m.plan == plan.

However, this is more fragile as not all providers handle those cases the same way or support them. One need to be particularly careful if the corresponding column allows null.

Comments

[juharr]

I'd just have multiple Where calls within if statements.

[Phil1970]

For a single use (and predefined conditions for each filtered column), that code is too complex... and you still have to confirm that it will works with your provider and do the filtering on the server.

[Rajput]

but this solution can be used in every kind of filtering even the second part is shown more generic approach to build this filter.Check he has 7 item to filter his criteria this will make is around 7! = 5040 combination and i dont think so he going to make this many query to filtering items.

[Phil1970]

If you look at the comments, one of them is Expression Code is not executed on sql server so this is a red flag. In any case, you don't have 5040 combinations as each filter can be activated independently so you would only have 7 conditions... See also my answer.