Where 'Like' query adds ESCAPE '~' which does not return any records

Question

 

I am trying to determine how to use the 'Like' operator in VB.
The letter 'R' is entered into a textbox (tbFindNameFirst) and the following code generates the SQL lines below.

Dim NameFirstFilter As String = Me.tbFindNameFirst.Text & "%"

 

Dim qFindPerson As IEnumerable(Of Person) = sqlGPdc.Persons.Where(Function(u) u.NameFirst Like NameFirstFilter)

SELECT [t0].[PersonId], [t0].[FamilyId], [t0].[ChildNumber], [t0].[NameFirst], [t0].[NameMiddle], [t0].[NameNick], [t0].[NameLast], [t0].[NameTitle], [t0].[Gender], [t0].[LineFileId], [t0].[Extra], [t0].[ImportFamilyId]

FROM [dbo].[Persons] AS [t0]

WHERE [t0].[NameFirst] LIKE @p0 ESCAPE '~'

-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [R~%]

-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

The query does not return any records (there are many records with NameFirst beginning with R).
When I try this query in SQL Manager (by making a stored procedure) [R~%] does not return records but [R%] does (as expected).
I have found SqlMethods.Like for C# but nothing about making this work for VB.
Any help would be appriciated.

Answer 1

Dim NameFirstFilter As String = Me.tbFindNameFirst.Text;

Dim qFindPerson As IEnumerable(Of Person) = sqlGPdc.Persons.Where(Function(u) u.NameFirs.StartsWith(NameFirstFilter))

 

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 2

There were bugs around unnecessary escaping of LIKE that were fixed in .NET 4.0 - this could have been one of them (I don't have a repro machine to hand to test).

[)amien

Answer 3

Kristofer,
Thanks so very much for your response. It worked corrrectly once I added "t" for NameFirs for NameFirst.
Lee_37122

Answer 4

Damien,
Thanks so very much for your response. Kristofer's response worked corrrectly once I added "t" for NameFirs for NameFirst.
I assume .NET 4.0 is for VS2010 Beta and cannot be used with VS2008?
Lee_37122