Underscore is a wildcard when searching a varchar(MAX) field

Thread is closed for posting
2 posts, 0 answers
  1. Russell
    Russell avatar
    12 posts
    Member since:
    Jan 2007

    Posted 16 Jul 2014 Link to this post

    If you have a string like "A-B" in a varchar(MAX) field and you search for "A_B" DataAccess will return "A-B" from the database.

    To reproduce I created a table called Tests with a varchar(MAX) field called Str.

    I added 2 test records.  "A" and "A-B"

    I created a new Entities Model with the Tests Table and ran the following code.

    Dim SearchStr As String = "A_B"
    Dim EM As New EntitiesModel
    Dim Obj As Test = (From e In EM.Tests Where e.Str = SearchStr  Select e).FirstOrDefault

    Obj.Str returns "A-B" from the Tests Table

    If you set SearchStr to "_" it will return "A" from the Tests Table
  2. Thomas
    Thomas avatar
    590 posts

    Posted 17 Jul 2014 Link to this post

    Hi Russel,

    yes, this seems to be a glitch. With MSSQL 2000, varchar(max) columns could not be sorted or compared except for LIKE and IS NULL. Later versions seem to have lifted this restriction, but our default converter initialization was not changed accordingly. I will mark this as a bug.
    Workaround: Please specify the converter to be used for varchar(max) columns as VariableLengthAnsiStringConverter; and for nvarchar(max) use VariableLengthStringConverter. You can do that most efficiently either in the fluent mapping code by specifying
    or by using the .rlinq editor (Model Operations-> Select Members, Column, SqlType varchar(max) -> Operations : Change Converter).

    Please excuse the inconvenience caused by this.

    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Back to Top