LINQ Filter query with multiple optional parameters

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 == LastnNameParam || LastnNameParam == null)
        && (mt.DateField == DateParam || DateParam == null)
        select new
        {
            mt.FIrstname,
            mt.lastname,
            mt.DateField
        };

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.

Yesterday 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;
    }
}

You can use EntityFramework.DynamicFilters which allow you to create dynamic filters or you can create where clause dynamically in linq. here is great tutorial how to create where clause dynamically.

https://www.codeproject.com/Tips/582450/Build-Where-Clause-Dynamically-in-Linq

This filter creation is bit tedious but this will serve your purpose.