I have the following linq query:

from p in testData where p.testString.Contains("foo [bar]") == "foo [bar]" select p

llblgen generates SQL for SQL server:

SELECT * FROM testTable WHERE testString LIKE '%foo [bar]%'

The problem with this is [] must be escaped (using ESCAPE [) otherwise they are used as matching characters. See the link below.

Is there any way to do this in the linq query?

http://msdn.microsoft.com/en-us/library/ms179859.aspx

Depending on what you want, I think you wouldn't need the ESCAPE clause.

For example, for this given data at DB:

Address

David Carla Cesar Conie

This query:

LinqMetaData metaData = new LinqMetaData(adapter);
     var q = from c in metaData.Customer
          where c.Address.StartsWith("c[ae]")
          select c;

will returns generate this query:

Query: SELECT [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_1].[CompanyName], [LPLA_1].[ContactName], [LPLA_1].[ContactTitle], [LPLA_1].[Address], [LPLA_1].[City], [LPLA_1].[Region], [LPLA_1].[PostalCode], [LPLA_1].[Country], [LPLA_1].[Phone], [LPLA_1].[Fax] FROM [Northwind].[dbo].[Customers] [LPLA_1]  WHERE ( ( ( ( [LPLA_1].[Address] LIKE @Address1))))
    Parameter: @Address1 : String. Length: 6. Precision: 0. Scale: 0. Direction: Input. Value: "c[ae]%".

And returns this data: - Carla - Cesar

So the brackets are indeed a way to scape wildcars and also to make slices (a-b) and such things. So maybe I didn't understand your question. Could you please post the query you are trying to achieve and some sample data found by your sql query?

Sorry maybe I didn't explain myself clearly. What if the string actually contains square brackets in it and you need to escape them?

e.g.

address in db = "This is an address with [square brackets] in it"

LinqMetaData metaData = new LinqMetaData(adapter); var q = from c in metaData.Customer where c.Address.Contains("address with [square brackets] in") select c;

SELECT * FROM testTable WHERE testString LIKE '%foo [bar]%'

Do you want to select records where testTable have a substring that is "foob", "fooa" or "foor", if that's true then the above generated SQL query is the right one.

Otherwise please tell us what's exactly that you need to select.

Also same as David I need to know the exact SQL query you want to execute (What you have in mind).

No I want to select records in testTable where a substring is "foo [bar]" not "foob".

Another way to explain it: 2 queries running on the same table would return the following:

testString = 'foo [bar]'

SELECT * FROM testTable WHERE testString = 'foo [bar]' Results in 1 record

SELECT * FROM testTable WHERE testString LIKE '%foo [bar]%' Results in 0 records

Hi,

nabils wrote:

SELECT * FROM testTable WHERE testString LIKE '%foo [bar]%'
Results in 0 records

I think ESCAPE isn't necessarily here, as you could escape the first bracket "[" using brackets literals "[]", as stated in the link you posted :

expression: LIKE '[ [ ]' Meaning: [

Then the second bracket "]" in your string is evaluated as part of the match string. So, you want to imitate this query:

SELECT * FROM testTable WHERE testString LIKE '%foo[[]bar]%'

Then your code will look like:

var q = from p in testData
     where p.testString.Contains("foo [[]bar]")
     select p;

I hope understand you well and being helpful.

Yes thank you. I must have missed that in the docs. That would do the trick.