Linq2SQL to produce Like operator

Question

Hi,

I have a string "Word1 Word2" and I want to transform it to a query such as "Like '%Word1%Word2%'".

At the moment I have:

from t in Test
where t.Field.Contains("Word1 Word2")

How to do this in LINQ2SQL? Do I need to create two separate queries for this, can't I write it in the same statement?

Thx in advance

Answer 1

Well that would translate to

LIKE "%Word1 Word2%"

which is probably not what you want... If you write your query like this:

where t.Field.Contains("Word1") && t.Field.Contains("Word2")

It will generate the following SQL :

DECLARE @p0 VarChar(4) SET @p0 = '%ab%'
DECLARE @p1 VarChar(4) SET @p1 = '%cd%'
....
SELECT ...
WHERE ([t0].[Field] LIKE @p0) AND ([t0].[Field] LIKE @p1)

Answer 2

As work-around, maybe this will suffice:

from t in Test
where t.Field.Contains("Word1") && t.Field.Contains("Word2")

...with some post-filtering on the client side, to ensure that word2 comes after word1.

Answer 3

from t in Test
where SqlMethods.Like(t.Field, "%Word1%Word2%")
select t