Usage contains in LINQ to SQL join

Question

How can I bind LINQ to SQL without an exact match? For example, let's say I have a form table with John Smith (2) data, and I want to join it in the Smith field in the name table. Something like that

 var query = from f in db.form join n in db.name on f.nameField like '%' + n.firstName + '%' 

Although the like keyword seems inaccessible to me.

Answer 1

You cannot use like in a Linq connection. In fact, you cannot use like in Linq at all, just ordinary string methods like StartsWith , EndsWith or Contains .

You will need to do something like this:

 var query = from f in db.form from n in db.name.Where(x => f.nameField.Contains(x.firstName)) ... 

Answer 2

There is actually a way to do this , but it is not as neat as the standard linq material is used:

 from c in dc.Organization where SqlMethods.Like(c.Hierarchy, "%/12/%") select *; 

(borrowed from the LP user the answer in a related question)