title: Convert CHARINDEX in sql query to LINQ using LAMBDA Expression
body: <p>I have a SQL query which I am trying to convert to Linq using Lambda expression.
  I was able to do a normal join operation ON TABLE1.XR_ID AND TABLE2.ID but then I realized that the SQL query is joining on CHARINDEX. I did some research but I wasn't able to find out any resource leading me in right direction.</p>
  
  <p>SQL:  </p>
  
  <pre><code>SELECT  distinct a.XR_ID FROM 
  TABLE1 a 
  JOIN  TABLE2 b ON CHARINDEX(CONVERT(VARCHAR(20), a.XR_ID), CONVERT(VARCHAR(20), B.ID), 1) = 1
  WHERE 
  b.TYPE = 'Q'
  </code></pre>
  
  <p>LINQ:</p>
  
  <pre><code>var innerQuery =  
               context.TABLE1.
               Join(context.TABLE2, 
               table1 =&gt; table1.XR_ID, 
               table2=&gt; table2.ID, 
               (table1 , table2) =&gt; 
                 new 
                 { 
                  TABLE1 = table1 , 
                  TABLE2 = table2
                 })
                .Where( y=&gt; y.table2.TYPE == "Q")
                .Distinct()
                .ToList();
  </code></pre>
  
  <p>The above query is not giving me the same result as the sql query. Can any one point me in the right direction? </p>