11

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.

Cavyn VonDeylen
  • 4,189
  • 9
  • 37
  • 52

2 Answers2

14

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))
    ...
p.s.w.g
  • 146,324
  • 30
  • 291
  • 331
  • 1
    I don't know if it's some weird implementation error in my code or not, but this gave me an error: `Only arguments that can be evaluated on the client are supported for the String.Contains method.` I got it to work using `IndexOf` instead of `Contains` as detailed [here](http://stackoverflow.com/a/7574433/1080891) – Cavyn VonDeylen Jul 02 '13 at 20:44
  • @CavynVonDeylen Hmm, that seem to be a limitation of Linq-to-SQL. `string.Contains` is the standard mechanism in other ORM's. – p.s.w.g Jul 02 '13 at 21:01
  • 1
    @CavynVonDeylen you can try to do the following `from n in db.name.ToList().Where(x => f.nameField.Contains(x.firstName))`. The `.ToList()` method evaluates the entity set and puts everything out of the database into memory. By doing this, you can use the `string.Contains()` method. Just be aware and do not evaluate all the objects in your table at once. – joao.arruda Jul 07 '15 at 15:01
  • What does two `from`s mean? Does it equal a `join` statement? – Hp93 Aug 23 '17 at 03:55
  • 1
    @Hp93 It equates to a [`CROSS JOIN`](https://technet.microsoft.com/en-us/library/ms190690.aspx), but note that the `Where` does limit the record set of `db.name` being considered. – p.s.w.g Aug 23 '17 at 15:17
6

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)

Community
  • 1
  • 1
Chris
  • 5,442
  • 17
  • 30
  • 2
    +1 Good point, this will work because OP is using Linq-to-SQL. In most other cases, this method will not translate. – p.s.w.g Jul 02 '13 at 16:56
  • 2
    @p.s.w.g true! should've mentioned that, this is strictly linq-to-sql. – Chris Jul 02 '13 at 17:00