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

[Russell]

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

[Thomas]

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
.WithConverter("VariableLengthAnsiStringConverter")
or by using the .rlinq editor (Model Operations-> Select Members, Column, SqlType varchar(max) -> Operations : Change Converter).

Please excuse the inconvenience caused by this.

Regards,
Thomas
Telerik