multiple options for filtering linq

Question

I want to have 3-4 options for searching. For now I am using my queries in If statements.

     if inputcol1 > 0 And Not inputCol2 = "" then 
         Dim list = (From P In db.table
                     Where P.column1 = inputCol1 and P.column2 = inputCol2
                     Select P).ToList()
     end if

and one (or 4) more with another condition but with almost the same query, just one different where clause. So this query is simplified and short. So if I have a really big query it would be a big mess. And I wouldn't want other programmers to read so much code for just a few options.

Is there a proper simple way to just see if the search option is filled in and query it?

Answer 1

It's late now but I am currently working on a VB.NET project and in addition to Gert Arnold's answer, it is important to have the same object in selection i-e. P when chaining because if for instance say P is Project Entity and in your final result (after filtering) you are selecting additional columns or object details like query = (From P in query Select P.Name, P.Deadline), it will throw a Casting Exception because the variable query is not of the same structure or object as Select P.Name, P.Deadline. Thus, use another variable if you want to have a different structure in your final selection such as:

Dim query = (From P in query Where P.Col2 = inputCol2 Select P)
Dim result = From P In query Select P.Name, P.Deadline ... 
'and then displaying it 
DataGridView.DataSource = result.toList()

However, I did try creating a New Type by result = From P in query Select New With {P.Name, P.Deadline} but it didn't work. I don't know why, I will dig into it if I left with spare time.

Answer 2

You might like trying LinqKit. With this library you have a PredicateBuilder class with methods:

public static Expression<Func<T, bool>> True<T>();
public static Expression<Func<T, bool>> False<T>();
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2);
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2);

These are extensions for Expression objects, which can be easily created from lambdas.

With such an expression you can do yourDataSource.Where(expression).

Sorry for c# notation, I don't know VB.net... If anyone would like to fix it to VB, feel free.

EDIT:

Well, PredicateBuilder is just a neat syntax sugar. On their website you can find the full source code which is really simple. Unfortunately, in C#. Here it goes:

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;

public static class PredicateBuilder
{
  public static Expression<Func<T, bool>> True<T> ()  { return f => true;  }
  public static Expression<Func<T, bool>> False<T> () { return f => false; }

  public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1,
                                                      Expression<Func<T, bool>> expr2)
  {
    var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
    return Expression.Lambda<Func<T, bool>>
          (Expression.OrElse (expr1.Body, invokedExpr), expr1.Parameters);
  }

  public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1,
                                                       Expression<Func<T, bool>> expr2)
  {
    var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
    return Expression.Lambda<Func<T, bool>>
          (Expression.AndAlso (expr1.Body, invokedExpr), expr1.Parameters);
  }
}

And that's it! Expressions (which are standard in .net, no additional library needed) provide some fine methods to work with them and they can be used inside the where clause. Give it a try :)

Answer 3

You were on the right track with your code.. you just need to change the if statement for a Ternary Operator:

    Dim table = New Dictionary(Of Integer, String)
    table.Add(1, "one")
    table.Add(2, "two")
    table.Add(3, "three")

    Dim inputCol1 As Integer
    Dim inputCol2 As String = "one"

    Dim list = (From P In table Where _
                (inputCol1 < 1 OrElse P.Key = inputCol1) _
                And (inputCol2 = "" OrElse P.Value = inputCol2) _
                Select P).ToList()

Find more info about this situations on this question: Conditional filtering

Answer 4

The best way is to expand your query conditionally:

Dim query = (From P In db.table Select P)

If inputCol1.HasValue
    query = (From P in query Where P.column1 = inputCol1 Select P)
End If
If inputCol2.HasValue
    query = (From P in query Where P.inputCol2 = inputCol2 Select P)
End If
' And so on...

Dim list = query.ToList()

Conditional filtering with Not inputCol2.HasValue OrElse P.Value = inputCol2 will create a query with useless predicates. By expanding your query conditionally only the predicates that matter will be incorporated.