Linq - How to nest filters?

Question

I'm trying to use linq to filter a lot of things based on a customers selection. At the moment I have only used 3 variables, but these needs to be nested in all select cases, to really catch every selection option.

The below code works, but I find this too complex, and I need to add even more variables... How can I optimise my code, or what is the best way to tackle this?

Thanks in advance!

    Public Function GetProducts(ByVal weight As String, ByVal taal As String, ByVal desc As String) As IEnumerable(Of CLPProduct)
        dc = New CLPDataContext
        Select Case weight
            Case "OK"
                Select Case taal
                    Case "Nederlands"
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" And a.NLProduct <> "" Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" And a.NLProduct = "" Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" Order By a.ID Ascending
                        End Select
                    Case "Duits"
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" And a.DEProduct <> "" Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" And a.DEProduct = "" Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" Order By a.ID Ascending
                        End Select
                    Case "Frans"
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" And a.FRProduct <> "" Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" And a.FRProduct = "" Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" Order By a.ID Ascending
                        End Select
                    Case Else
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" And (a.NLProduct <> "" And a.FRProduct <> "" And a.DEProduct <> "") Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" And (a.NLProduct = "" Or a.FRProduct = "" Or a.DEProduct = "") Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Where a.Weight <> "" Order By a.ID Ascending
                        End Select
                End Select
            Case "NOK"
                Select Case taal
                    Case "Nederlands"
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" And a.NLProduct <> "" Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" And a.NLProduct = "" Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" Order By a.ID Ascending
                        End Select
                    Case "Duits"
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" And a.DEProduct <> "" Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" And a.DEProduct = "" Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" Order By a.ID Ascending
                        End Select
                    Case "Frans"
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" And a.FRProduct <> "" Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" And a.FRProduct = "" Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" Order By a.ID Ascending
                        End Select
                    Case Else
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" And (a.NLProduct <> "" And a.FRProduct <> "" And a.DEProduct <> "") Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" And (a.NLProduct = "" Or a.FRProduct = "" Or a.DEProduct = "") Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Where a.Weight = "" Order By a.ID Ascending
                        End Select
                End Select
            Case Else
                Select Case taal
                    Case "Nederlands"
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.NLProduct <> "" Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.NLProduct = "" Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Order By a.ID Ascending
                        End Select
                    Case "Duits"
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.DEProduct <> "" Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.DEProduct = "" Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Order By a.ID Ascending
                        End Select
                    Case "Frans"
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where a.FRProduct <> "" Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where a.FRProduct = "" Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Order By a.ID Ascending
                        End Select
                    Case Else
                        Select Case desc
                            Case "OK"
                                Return From a In dc.CLPProducts Select a Where (a.NLProduct <> "" And a.FRProduct <> "" And a.DEProduct <> "") Order By a.ID Ascending
                            Case "NOK"
                                Return From a In dc.CLPProducts Select a Where (a.NLProduct = "" Or a.FRProduct = "" Or a.DEProduct = "") Order By a.ID Ascending
                            Case Else
                                Return From a In dc.CLPProducts Select a Order By a.ID Ascending
                        End Select
                End Select
        End Select
End Function

Answer 1

So you have a queryable sequence of Products, where every Product has a limited number of properties, say less than 64 properties.

You also have a sequence of filters. You want to filter the sequence of Products, such that it returns the sequence of Products that match all filters.

For example, you have several checkboxes and textBoxes, the operator types values and checks the boxes:

X Name "Hybrid Pump X14"
0 ShopLocation "New York"
X MaxPrice "200"

The operator wants to query all Products with name "Hybrid Pump X14" with a price less or equal than 200. ShopLocation is not checked, so not considered in your query.

As an extension method:

IEnumerable<Product> Where(IEnumerable<Product> source,
    IEnumerable<Func<Product,bool>> predicates)
{
    // TODO: implement
}

This method will filter your product, such that it will return only those products that match all Predicates

Note that I use an AND in predicates. Not OR, nor XOR etc.

Usage:

IEnumerable<Product> allProducts = ...
IEnumerable<Func<Product, bool> filters = new Func<Product, bool>[]
{
    product => product.Name == "Hybrid Pump X14",
    product => product.Price <= 200,
};
IEnumerable<Product> filteredProducts = allProducts.Where(filters);

Code will be simple:

IEnumerable<Product> Where(IEnumerable<Product> source,
IEnumerable<Func<Product,bool>> predicates)
{
    // TODO: decide what to do if source, predicates equal null

    IEnumerable<Product> filteredProducts = products;
    foreach (var predicate in predicates)
    {
        filteredProducts = filteredProducts.Where(predicate);
    }
    return filteredProducts.
}

Alas, this has only moved your original problems to how to make a sequence of filters

You didn't say how you would decide on which values to filter: parameters weight / taal / desc.

class FilterCollection : IEnumerable<Func<Product, bool>>
{
    private readonly List<Func<Product, bool> filters = new List<Func<Product, bool>>();

    void Clear() => this.filters.Clear();

    void AddFilterMaxWeight(decimal weight)
    {
        this.Filters.Add(product => product.Weight <= weight);
    }

    void AddFilterLanguage(string language)
    {
        this.Filters.Add(product => product.Taal == language);
    }

    // etc, do this for every possible filter

    // IEnumerable<Product>, IEnumerable:
    public IEnumerator<Product> GetEnumerator()
    {
        return this.filters.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator();
    }
}

Usage:

FilterCollection filters = new Filtercollection();

// somehow you decide which filters to be used, for example operator input:
public void OnCheckBoxTaalChecked(object sender, EventArgs e)
{
    bool filterByTaal = this.checkBoxTaal.Checked;
    if (filterByTaal)
    {
        filters.AddLanguage(this.textBoxTaal.Text);
    }
}

public IEnumerable<Product> FilterProducts()
{
    IEnumerable<Product> allProducts = ...
    IEnumerable<Product> filteredProducts = allProducts.Where(this.filters);
    this.ProcessFilteredProducts(filteredProducts);
}

If you also want to be able to remove filters, consider:

private enum filterProperties =
{
    Taal,
    Weight,
    Name,
}

private readonly Dictionary<filterProperties, Func<Product, bool> filters = new Dictionary<filterProperties, Func<Product, bool>>()

public bool IsFilteredByTaal =>this.Filters.Contains(filterProperties.Taal);

public void RemoveFilterByTaal()
{
    if (this.IsFilteredByTaal)
        this.filters.Remove(filterProperties.Taal);
}

public void AddFilterByTaal(string language)
{
    this.Filters.Add(filterProperties.Taal, product => product.Taal == language);
}

public void ReplaceFilterByTaal(string language)
{
    this.RemoveFilterByTaal();
    this.AddFilterByTaal();
}

Answer 2

I'm not 100% sure that I got your logic right, but try this:

Public Function GetProducts(ByVal weight As String, ByVal taal As String, ByVal desc As String) As IEnumerable(Of CLPProduct)

    Dim dc = New CLPDataContext

    Dim results = From a In dc.CLPProducts

    Select Case weight
        Case "OK"
            results = From a In results Where a.Weight <> ""
        Case "NOK"
            results = From a In results Where a.Weight = ""
        Case Else
    End Select

    Select Case taal
        Case "Nederlands"
            Select Case desc
                Case "OK"
                    results = From a In results Where a.NLProduct <> ""
                Case "NOK"
                    results = From a In results Where a.NLProduct = ""
                Case Else
            End Select
        Case "Duits"
            Select Case desc
                Case "OK"
                    results = From a In results Where a.DEProduct <> ""
                Case "NOK"
                    results = From a In results Where a.DEProduct = ""
                Case Else
            End Select          
        Case "Frans"
            Select Case desc
                Case "OK"
                    results = From a In results Where a.FRProduct <> ""
                Case "NOK"
                    results = From a In results Where a.FRProduct = ""
                Case Else
            End Select              
        Case Else
    End Select

    results = From a In results Order By a.ID

    Return results

End Function