Join in LINQ Query Syntax: moving right side to the left

Question

I have a scenario where I need to join two tables:

A

|---------------------|------------------|
|          ID         |        Name      |
|---------------------|------------------|
|          1          |        John      |
|---------------------|------------------|
|          2          |        Matt      |
|---------------------|------------------|
|          3          |        Emma      |
|---------------------|------------------|

B

|---------------------|------------------|
|          ID         |        Text      |
|---------------------|------------------|
|          1          |  blah blah John  |
|---------------------|------------------|
|          2          | this is some data|
|---------------------|------------------|
|          3          | My name is Jeff  |
|---------------------|------------------|

I need to use LINQ's query syntax to join these two tables.

The left table needs to be Table A.

Though I need to join based on whether the "text" column contains the text from the Name column in Table A.

The code should look something like this:

var result = from ta in A
             join tb in B on tb.Text.Contains(ta.Name)

I can't seem to use tb on the left side of the join.

I can only use ta.

tb works on the right side of the join.

Is there any way I can switch it around, so that I'm able to use tb on the left side?

Answer 1

Joins in LINQ can work only with equality matching. But you could use SelectMany method combined with Where. It will essentially do a join by condition.

In query syntax, it would be like this:

from ta in A
from tb in B
where tb.Text.Contains(ta.Name)
// the rest of your query

See also Perform custom join operations