Linq Dynamic Error with Dynamic Where clause

Question

I have a four dropdownlists that are used to filter/order a gridview.

If any of the first 3 dropdowns have a selected value other than 0 the the WhereFlag is set to True. The fourth dropdown is used to dictate which column the grid is ordered by.

My code for databinding the gridview uses System.Linq.Dynamic and is shown below...

Dim dc As New DiaryDataContext

    If WhereFlag = False Then
        'This section works...

        Dim AList = dc.D_AreaSubAreas _
                    .OrderBy(ddl_SortBy.SelectedValue)

        Dim dl As List(Of D_AreaSubArea)
        dl = AList.ToList

        dl.Insert(0, New D_AreaSubArea With {.Ref = 0,
                                            .Area = "",
                                            .SubArea = "",
                                            .Allocation = "Allocation...",
                                            .Redundant = False})

        gv_AreaSubArea.DataSource = dl
        gv_AreaSubArea.DataBind()      
        'Gridview successfully binds
        'If ddl_SortBy value is changed... Gridview binds OK.
    Else
        'This section gives error...

        Dim WhereBuild As New StringBuilder

        If ddl_AreaFilter.SelectedIndex <> 0 Then
            WhereBuild.Append("Area = '" & ddl_AreaFilter.SelectedValue & "'")
            AndFlag = True
            End If

        If ddl_SubAreaFilter.SelectedIndex <> 0 Then
            If AndFlag = True Then
                WhereBuild.Append(" AND ")
                End If
            WhereBuild.Append("SubArea = '" & ddl_SubAreaFilter.SelectedValue & "'")
            AndFlag = True
            End If

        If ddl_AllocFilter.SelectedIndex <> 0 Then
            If AndFlag = True Then
                WhereBuild.Append(" AND ")
                End If
            WhereBuild.Append("Allocation = '" & ddl_AllocFilter.SelectedValue & "'")
        End If

        'ERROR HERE
        Dim AList = dc.D_AreaSubAreas _
                    .Where(WhereBuild.ToString) _
                    .OrderBy(ddl_SortBy.SelectedValue)
        'END ERROR

        Dim dl As List(Of D_AreaSubArea)
        dl = AList.ToList

        dl.Insert(0, New D_AreaSubArea With {.Ref = 0,
                                            .Area = "",
                                            .SubArea = "",
                                            .Allocation = "Allocation...",
                                            .Redundant = False})


        gv_AreaSubArea.DataSource = dl
        gv_AreaSubArea.DataBind()


    End If

The error I get is with the dynamic where clause. The error I get is

ParseException is unhandled by user code. Character Literal must contain exactly one character

It points to the query AList in the Else fork. The only difference between it and the query in the If fork is the addition of the Where clause... but I have been unable to deduce what is wrong with my code. AHA.

jnovo · Accepted Answer · 2014-04-02T14:35:54.037

1

The error points to the place where the LINQ query is evaluated and, as the message says, the problem is that a character is expected but several characters were provided.

Check whenever ddl_AreaFilter.SelectedValue, ddl_SubAreaFilter.SelectedValue or ddl_AllocFilter.SelectedValue actually contain a character or a string. If they contain more than one character, you should replace ' with \" when building the where condition, for instance:

WhereBuild.Append("Area = """ & ddl_AreaFilter.SelectedValue & """")

EDIT

You must make sure that the type of the value contained in each SelectedValue string match the corresponding database type. For instance, if the database column is a numeric type, the string content will be casted to a numeric type.

When you specify the value in the comparison using quotes, you are indicating that the type on the right side of the comparison is either character or string (depending on whenever you use single or double quotes, respectively).

So, what are the Area, SubArea and Allocation types in the database?

Single character: the value in your query should be around single quotes: Area = 'value'
Strings (for instance, varchar):you must use double quotes: Area = "value"
Other: then you should use no quotes: Area = value

edited Apr 02 '14 at 14:35

answered Apr 02 '14 at 13:27

jnovo

5,659
2
38
56

jnovo Thanks... Unfortunately your suggestion give a error Conversion from String to Type Long is not Valid. I have tried a version where my WhereBuild is a stringbuilder and one where its a string that I concatenate to. Also in VS2010 intellisence adds spaces to the last escaped portions so that it displays as & " \ "") – Mych Apr 02 '14 at 14:08
For explanation all dropdowns provide string values – Mych Apr 02 '14 at 14:11
@jnovo... Thanks for your help... between us we got the answer. – Mych Apr 02 '14 at 14:30
I did place an Update in the answer with the final solution but its not visible.... The final solution is WhereBuild.Append("Area = """).Append(ddl_AreaFilter.SelectedValue).Append("""") The \" did not work. – Mych Apr 02 '14 at 14:35
@Mych I'll update the answer, now I see that the problem was that I was using the C# quote escape character (`\"`) when in VB, it is `""`. Sorry about the misguidance. – jnovo Apr 02 '14 at 14:37
No problem... I'm just glad I don't have to pull any more hair :) – Mych Apr 02 '14 at 14:39

Linq Dynamic Error with Dynamic Where clause

1 Answers1