Use contains in LINQ to SQL join

Question

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

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

Although the like keyword doesn't seem to be available to me.

Answer 1

You can't use like in a Linq join. In fact, you can't use like in Linq at all, only conventional string methods like StartsWith, EndsWith, or Contains.

You'd have 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

Actually, there is a way of doing it, but it's not as neat as using the standard linq stuff:

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

(borrowed from the user L P's answer in the linked question)