Abstracting Dynamic Linq to SQL where

Question

UPDATE: Groo's answer was marked correct as it would be a good alternative. I decided to stick with the select/switch statements to avoid the performance issues of using reflection. As far as I can tell there is no way to do what I want without using Dynamic LINQ (and reflection) or a decompiler (to manually code each LINQ statement, which I am already doing by hand). Note: to make this update DropDownList16 through DropDownList20 were removed (needed the characters).

Is there a way to abstract the building of a dynamic Linq to SQL query?

I am trying to build a Linq to SQL query with a dynamic where clause, based on user supplied filters. The user needs to be able to filter strings, integers, and dates using advanced options (equal, not equal, contains, starts with, etc). The user needs to be able to use these filters with as many or few columns as desired.

My current switch for one column:

VB

Select Case type
    Case StringFilterTypes.Any  ''//Do nothing (same as else)
    Case StringFilterTypes.Contains
        query = From view In query Where view.Name.Contains(userValue) Select view
    Case StringFilterTypes.Exactly
        query = From view In query Where view.Name = userValue Select view
    Case StringFilterTypes.StartsWith
        query = From view In query Where view.Name.StartsWith(userValue) Select view
    Case StringFilterTypes.EndsWith
        query = From view In query Where view.Name.EndsWith(userValue) Select view
    Case Else                   ''//Do nothing (same as Any).
End Select

C#

switch (type) {
    case StringFilterTypes.Any: //Do nothing (same as else)
        break;
    case StringFilterTypes.Contains:
        query = from view in querywhere view.Name.Contains(userValue)view;
        break;
    case StringFilterTypes.Exactly:
        query = from view in querywhere view.Name == userValueview;
        break;
    case StringFilterTypes.StartsWith:
        query = from view in querywhere view.Name.StartsWith(userValue)view;
        break;
    case StringFilterTypes.EndsWith:
        query = from view in querywhere view.Name.EndsWith(userValue)view;
        break;
    default:                    //Do nothing (same as Any).
        break;
}

I will need to use this select/switch with all the string columns and a similar switch with all the integer and datetime columns. As a result, I want to make these select/switch statments into a function where the type, view.Name, and userValue are supplied variables. I can pass type and userValue since they are local variables, but how can I pass view.Name into the function?

I have looked into extensions, but they seem to require that I pass view, know the column already, and don't support select/switch statements.
I also looked into adding the where statements as strings but feel this removes the only two reasons for using linq (compiler verified queries, automated escaping of user input to prevent sql injection).
Expression Trees look promising, but I am not sure how to use them and if they are a good idea where performance is an issue.

The best solution would be something like:

VB

Public Function ApplyFilters(query As IQueryable(Of DBName.ViewName)) As IQueryable(Of DBName.ViewName)
    ''//...
    Filter_String (query, Type, View.Name, userValue)
    ''//...
End Function
Public Function Filter_String (query As IQueryable(Of DBName.ViewName), type As StringFilterTypes, column as ???, userValue As String) As IQueryable(Of DBName.ViewName)
    Select Case type
        Case StringFilterTypes.Any  ''//Do nothing (same as else)
        Case StringFilterTypes.Contains
            query = From view In query Where column.Contains(userValue) Select view
        Case StringFilterTypes.Exactly
            query = From view In query Where column = userValue Select view
        Case StringFilterTypes.StartsWith
            query = From view In query Where column.StartsWith(userValue) Select view
        Case StringFilterTypes.EndsWith
            query = From view In query Where column.EndsWith(userValue) Select view
        Case Else                   ''//Do nothing (same as Any).
    End Select

    Return query
End Function

C#

public IQueryable<DBName.ViewName> ApplyFilters(IQueryable<DBName.ViewName> query)
{
    //...
    Filter_String (query, Type, View.Name, userValue);
    //...
}
public IQueryable<DBName.ViewName> Filter_String(IQueryable<DBName.ViewName> query, StringFilterTypes type, ??? column, string userValue)
{
    switch (type) {
        case StringFilterTypes.Any: //Do nothing (same as else)
            break;
        case StringFilterTypes.Contains:
            query = from view in querywhere column.Contains(userValue)view;
            break;
        case StringFilterTypes.Exactly:
            query = from view in querywhere column == userValueview;
            break;
        case StringFilterTypes.StartsWith:
            query = from view in querywhere column.StartsWith(userValue)view;
            break;
        case StringFilterTypes.EndsWith:
            query = from view in querywhere column.EndsWith(userValue)view;
            break;
        default:
            break;                  //Do nothing (same as Any).
    }

    return query;
}

Update 2012/02/27:
For BlueRaja - Danny Pflughoeft I am providing a sample application. Obviously the data connection will have to be supplied by the end user (MS SQL database named "Test" with table named "testTable" and columns named "Column#" where # is all numbers 1-20).

Default.aspx

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="WebApplication1.test._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="DropDownList1" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList2" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList3" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList4" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False"  Text ="Contains"    Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList5" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList6" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox6" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList7" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox7" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList8" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox8" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList9" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox9" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList10" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox10" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList11" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox11" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList12" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox12" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList13" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False"  Text ="Contains"    Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox13" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList14" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False" Text ="Contains"     Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox14" runat="server"></asp:TextBox>
        <br />

        <asp:DropDownList ID="DropDownList15" runat="server">
            <asp:ListItem Selected="True"  Text ="Any"          Value="-1" />
            <asp:ListItem Selected="False"  Text ="Contains"    Value="0" />
            <asp:ListItem Selected="False" Text ="Is exactly"   Value="1" />
            <asp:ListItem Selected="False" Text ="Starts with"  Value="2" />
            <asp:ListItem Selected="False" Text ="Ends with"    Value="3" />
        </asp:DropDownList>
        <asp:TextBox ID="TextBox15" runat="server"></asp:TextBox>
        <br />

        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>

    </div>
    </form>
</body>
</html>

Default.aspx.vb

Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadComplete
        Dim _db As TestDataContext = New TestDataContext(ConfigurationManager.ConnectionStrings("TestDataConn").ConnectionString)

        Dim query As IQueryable(Of testTable) = _
         From view In _db.testTables
         Select view

        Select Case DropDownList1.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column1.Contains(TextBox1.Text) Select view
            Case "1"
                query = From view In query Where view.Column1 = TextBox1.Text Select view
            Case "2"
                query = From view In query Where view.Column1.StartsWith(TextBox1.Text) Select view
            Case "3"
                query = From view In query Where view.Column1.EndsWith(TextBox1.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList2.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column2.Contains(TextBox2.Text) Select view
            Case "1"
                query = From view In query Where view.Column2 = TextBox2.Text Select view
            Case "2"
                query = From view In query Where view.Column2.StartsWith(TextBox2.Text) Select view
            Case "3"
                query = From view In query Where view.Column2.EndsWith(TextBox2.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList3.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column3.Contains(TextBox3.Text) Select view
            Case "1"
                query = From view In query Where view.Column3 = TextBox3.Text Select view
            Case "2"
                query = From view In query Where view.Column3.StartsWith(TextBox3.Text) Select view
            Case "3"
                query = From view In query Where view.Column3.EndsWith(TextBox3.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList4.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column4.Contains(TextBox4.Text) Select view
            Case "1"
                query = From view In query Where view.Column4 = TextBox4.Text Select view
            Case "2"
                query = From view In query Where view.Column4.StartsWith(TextBox4.Text) Select view
            Case "3"
                query = From view In query Where view.Column4.EndsWith(TextBox4.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList5.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column5.Contains(TextBox5.Text) Select view
            Case "1"
                query = From view In query Where view.Column5 = TextBox5.Text Select view
            Case "2"
                query = From view In query Where view.Column5.StartsWith(TextBox5.Text) Select view
            Case "3"
                query = From view In query Where view.Column5.EndsWith(TextBox5.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList6.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column6.Contains(TextBox6.Text) Select view
            Case "1"
                query = From view In query Where view.Column6 = TextBox6.Text Select view
            Case "2"
                query = From view In query Where view.Column6.StartsWith(TextBox6.Text) Select view
            Case "3"
                query = From view In query Where view.Column6.EndsWith(TextBox6.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList7.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column7.Contains(TextBox7.Text) Select view
            Case "1"
                query = From view In query Where view.Column7 = TextBox7.Text Select view
            Case "2"
                query = From view In query Where view.Column7.StartsWith(TextBox7.Text) Select view
            Case "3"
                query = From view In query Where view.Column7.EndsWith(TextBox7.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList8.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column8.Contains(TextBox8.Text) Select view
            Case "1"
                query = From view In query Where view.Column8 = TextBox8.Text Select view
            Case "2"
                query = From view In query Where view.Column8.StartsWith(TextBox8.Text) Select view
            Case "3"
                query = From view In query Where view.Column8.EndsWith(TextBox8.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList9.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column9.Contains(TextBox9.Text) Select view
            Case "1"
                query = From view In query Where view.Column9 = TextBox9.Text Select view
            Case "2"
                query = From view In query Where view.Column9.StartsWith(TextBox9.Text) Select view
            Case "3"
                query = From view In query Where view.Column9.EndsWith(TextBox9.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList10.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column10.Contains(TextBox10.Text) Select view
            Case "1Column10"
                query = From view In query Where view.Column10 = TextBox10.Text Select view
            Case "2"
                query = From view In query Where view.Column10.StartsWith(TextBox10.Text) Select view
            Case "3"
                query = From view In query Where view.Column10.EndsWith(TextBox10.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList11.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column11.Contains(TextBox11.Text) Select view
            Case "1"
                query = From view In query Where view.Column11 = TextBox11.Text Select view
            Case "2"
                query = From view In query Where view.Column11.StartsWith(TextBox11.Text) Select view
            Case "3"
                query = From view In query Where view.Column11.EndsWith(TextBox11.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList12.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column12.Contains(TextBox12.Text) Select view
            Case "1"
                query = From view In query Where view.Column12 = TextBox12.Text Select view
            Case "2"
                query = From view In query Where view.Column12.StartsWith(TextBox12.Text) Select view
            Case "3"
                query = From view In query Where view.Column12.EndsWith(TextBox12.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList13.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column13.Contains(TextBox13.Text) Select view
            Case "1"
                query = From view In query Where view.Column13 = TextBox13.Text Select view
            Case "2"
                query = From view In query Where view.Column13.StartsWith(TextBox13.Text) Select view
            Case "3"
                query = From view In query Where view.Column13.EndsWith(TextBox13.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList14.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column14.Contains(TextBox14.Text) Select view
            Case "1"
                query = From view In query Where view.Column14 = TextBox14.Text Select view
            Case "2"
                query = From view In query Where view.Column14.StartsWith(TextBox14.Text) Select view
            Case "3"
                query = From view In query Where view.Column14.EndsWith(TextBox14.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select

        Select Case DropDownList15.SelectedValue
            Case "-1"
                ''//Do nothing (same as else)
            Case "0"
                query = From view In query Where view.Column15.Contains(TextBox15.Text) Select view
            Case "1"
                query = From view In query Where view.Column15 = TextBox15.Text Select view
            Case "2"
                query = From view In query Where view.Column15.StartsWith(TextBox15.Text) Select view
            Case "3"
                query = From view In query Where view.Column15.EndsWith(TextBox15.Text) Select view
            Case Else
                ''//Do nothing (same as Any).
        End Select


        GridView1.DataSource = query
        GridView1.DataBind()
    End Sub

End Class

Why do you have both C# and VB.Net code snippets? What language is your real code written in? — vgru, Feb 24 '12 at 21:37
VB, but most people are more familiar with C#. I will accept C# or VB, I am better with C# anyways. — Trisped, Feb 24 '12 at 21:41
Why do you want to do this? What advantage does `Filter_String(query, StringFilterTypes.StartsWith, column, value)` have over simply using `query.Where(o => o.column.StartsWith(value))`? — BlueRaja - Danny Pflughoeft, Feb 24 '12 at 21:55
Resulting function would be used with many different columns. For example I might use it with view.Name, view.City, view.Description, etc. While I can create a switch for each column it becomes very tedious. Also, I have a view.LName and view.FName which require the options: view.FName, view.LName, view.FName and view.LName, view.FName or view.LName and each of those require the Contains, Equals, StartsWith, and EndsWith options. The select/switch would require code for 24 possible outcomes. — Trisped, Feb 24 '12 at 22:15
What `switch` statement? Just have the users of your interface call `Where()` directly, like you are now. They will use it in almost exactly the same way they'd use `Filter_String()`, except that they don't have to learn a new API for it. — BlueRaja - Danny Pflughoeft, Feb 25 '12 at 06:02
@BlueRaja - Danny Pflughoeft: The code is being used on a website, not a code interface. Sorry if that was not clear. — Trisped, Feb 25 '12 at 18:32
@Trisped: Code is **always** used as an interface, even if it's only used on one website, and even if you're the only developer. I ask again - why not just call `.Where()` where you would otherwise be called `Filter_String()`? There is nothing that `Filter_String` can do that `Where` cannot, with exactly the same amount of code, but `Filter_String` is more obfuscated — BlueRaja - Danny Pflughoeft, Feb 25 '12 at 19:10
@BlueRaja - Danny Pflughoeft: Ah, I understand now, you are asking why I want to use the function instead of adding the where directly. The goal is to replace the 100+ lines of repetitive code with 2-3 simple functions/methods which encapsulate the select/switch statements. This way, each column which needs a string filter takes 1 line of code instead of 14. Also, if a change needed to be made (like adding a StringFilterTypes.DoesNotContain) would only need to be coded in one place (the function/method) instead of several (each switch/select). You know, reduce repetitive code. — Trisped, Feb 25 '12 at 23:05
@Trisped: You can achieve the exact same thing as `Filter_String` using one line already. `Filter_String(query, StringFilterTypes.StartsWith, column, value)` does exactly the same thing as `query.Where(o => o.column.StartsWith(value))` (or in VB.Net, `query.Where(Function(o) o.column.StartsWith(value))`) — BlueRaja - Danny Pflughoeft, Feb 26 '12 at 00:51
@BlueRaja - Danny Pflughoeft: You missed the select/switch which resolves if .Contains, =, .StartsWith, or .EndsWith should be used. This is the main purpose of Filter_String, to resolve which where condition to add to the existing query. Or are you saying there is a way to write a lambda expression which uses a switch AND is compatible with Linq to SQL? — Trisped, Feb 26 '12 at 01:21

vgru · Accepted Answer · 2012-02-24T21:47:48.677

0

Note that Dynamic LINQ is not prone to SQL injection in the same way as a query concatenated from user supplied strings. The string passed is tokenized and parsed to create the expression tree which will be translated into a query, and it doesn't support an unlimited number of operations.

You cannot drop a table using a Dynamic LINQ query, for example.

Still, it is important to note that you need to use Dynamic LINQ with parameter placeholders (or "named parameters"). This has same benefits as using named parameters with a DBComand.

Also, you can check out PredicateBuilder by Albahari.

It allows you to convert a static expression like this:

p => p.Price > 100 &&
     p.Price < 1000 &&
    (p.Description.Contains ("foo") || p.Description.Contains ("far"))

Into this:

var outer = PredicateBuilder.True<Product>();
outer = outer.And (p => p.Price > 100);
outer = outer.And (p => p.Price < 1000);
{
   var inner = PredicateBuilder.False<Product>();
   inner = inner.Or (p => p.Description.Contains ("foo"));
   inner = inner.Or (p => p.Description.Contains ("far"));
   outer = outer.And (inner);
}

edited Feb 24 '12 at 21:47

answered Feb 24 '12 at 21:33

vgru

49,838
16
120
201

The resulting method needs to work with any column. It looks like your suggestion still requires me to statically specify the column (view.Name in my example, p.Description in your example) in the result rather then passing it as a variable. – Trisped Feb 24 '12 at 21:40
@Trisped: I've updated my answer slightly. Dynamic LINQ where clause should be the way to go in your case. – vgru Feb 24 '12 at 21:42
@Trisped: you could also check these threads: ["Is injection possible through Dynamic LINQ"](http://stackoverflow.com/a/9022254/69809) and ["Dynamic LINQ with direct user input, any dangers?"](http://stackoverflow.com/a/438714/69809). Basically, I would go with Dynamic LINQ, but would also make sure I do my best in sanitizing the input. Columns names should be easy to sanitize (just check if the value represents a valid column name). Column values, on the other hand, should be passed as parameters (not concatenated). That should cover every part of your query. – vgru Feb 24 '12 at 21:58
So I managed to get Dynamic LINQ working. It does not seem to support the Contains, StartsWith, or EndsWith function I am using in my example. It does escape the parameters correctly. Looks like I will have to stick with the select/switch statements or use something which will allow me to write the SQL text directly. – Trisped Feb 25 '12 at 03:35
1

Would SqlMethods.Like help? E.g. http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/10/16/linq-to-sql-like-operator.aspx – Andrew Morton Feb 27 '12 at 20:59
@Andrew Morton: SqlMethods.Like would help, I would be able to combine the Contains, StartsWith, and EndsWith cases into one function call to get the string and the Linq query. I would have to manually escape the text value which would not be too hard... – Trisped Feb 27 '12 at 22:54

BlueRaja - Danny Pflughoeft · Answer 2 · 2012-02-27T20:52:31.040

0

There is no need to write a custom Filter_String method - there is already a method the callers can call to filter a query by the contents of a string: Where(). I don't know why you insist there needs to be a switch statement - There is no need for a switch statement.

For example, instead of writing Filter_String(query, StringFilterTypes.StartsWith, column, value), your callers can simply write

query.Where(o => o.column.StartsWith(value))

There is no need for a new interface, when LINQ already natively supports what you need.

Similarly, instead of Filter_String(query, StringFilterTypes.EndsWith, column, value), they can write

query.Where(o => o.column.EndsWith(value))

and instead of Filter_String(query, StringFilterTypes.Contains, column, value), they can write

query.Where(o => o.column.Contains(value))

I hope this makes clear what I was trying to say in the comments above.

edited Feb 27 '12 at 20:52

answered Feb 26 '12 at 02:30

BlueRaja - Danny Pflughoeft

84,206
33
197
283

o => o.StartsWith(value) is not a string, it is a lambda expression. It is specified before compilation. The decision on how to filter is made at run time. There is no "they" which can write anything, there is only me. For example, I release the product to the end user. They see a web page with 20 DropDownLists, one for each column. Each DropDownList contains 5 entries: Any, Contains, Equals, Starts With, Ends With. Next to each DropDownList is a TextBox where the user can enter a string (value). After applying the filters the user presses the Apply button. There are no query strings. – Trisped Feb 26 '12 at 04:10
On my end I would write query = Filter_String (query, DDL1.SelectedValue, view.Name, TB1.Text); query = Filter_String (query, DDL2.SelectedValue, view.Name, TB2.Text); query = Filter_String (query, DDL3.SelectedValue, view.Name, TB3.Text); ... query = Filter_String (query, DDL20.SelectedValue, view.Name, TB20.Text); Since this will not work (View is not valid) I have to write each switch for each column. There is no way at compile time to know which option the user will take, so all must be planed for. And yes, there is Dlinq, but it does not support .StartsWith, .EndsWith, or .Contains. – Trisped Feb 26 '12 at 04:18
Sorry if there was tone in my previous comments, I was working on another problem when I wrote those. I have updated my question to have a sample problem. If you need it in C# let me know (or use http://www.developerfusion.com/tools/convert/vb-to-csharp/). Please feel free to change anything in Default.aspx.vb as long as all filters can be applied. I tried to add your query.Where(o => o.EndsWith(value)) code but was unable to get it to work. Query is an IQueriable so I would need query.Where(o => o.Column1.EndsWith(value)). – Trisped Feb 27 '12 at 19:59

Abstracting Dynamic Linq to SQL where

2 Answers2