title: How to write SQL SELECT INNER JOIN with multiple conditions (with LIKE) query to LINQ to SQL
body: <p>I've been looking up how to do this, and I found something close, but not quite what I'm looking for. I wonder if this might help others as well, but I could really use the help. I've got a pretty simple SELECT statement I need to convert into LINQ to SQL to speed up searches in our software:</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>SELECT  Animals.*
  FROM    Animals 
          INNER JOIN AnimalAliases
          ON  Animals.AnimalID = AnimalAliases.AnimalID 
          AND AnimalAliases.Alias LIKE N'%USERINPUT%';
  </code></pre>
  
  <p>Basically, I want to be able to do a Inner join with multiple conditions, but one of the conditions has nothing to do with one of the tables, the LIKE statement, which is where I get stuck.</p>
  
  
  
  <pre class="lang-cs prettyprint-override"><code>var query = 
      from animal in context.Animals
      join animalAlias in context.AnimalAliases
          on new { animal.AnimalID, "USERINPUT" }
      equals new { animalAlias.AnimalID, animalAlias.Alias }
      select animal;
  </code></pre>
  
  <p>but that doesn't work obviously, because I dont want EQUALS to USERINPUT, I want to perform various LIKE operations on it..</p>
  
  <p>Anyone have any insight?</p>