title: LINQ Join On Multiple Columns With Different Data Types
body: <p>I've got a situation where I need to join two tables based on two columns, and one of the columns that is to be compared is a nullable integer while one is a string (a big mess I know). In SQL, the following works:</p>
  
  <pre><code> SELECT stuff
   FROM Table1 t1
   Inner Join Table2 t2 on t1.ID = t2.ID and t2.Weird = CAST(t1.Weird AS varchar(11))
  </code></pre>
  
  <p>Now in order to join on multiple columns in LINQ, I know I must do the following:</p>
  
  <pre><code>var queryResult = (from o in T1)
  join p in t2 on new { T1.ID, T1.Weird} equals new {T2.ID, T2.Weird}
  </code></pre>
  
  <p>Problem with that is that naturally I get a "Type Inference Failed" error. Makes sense, Column Weird is a string in one table and int in another. </p>
  
  <p>Now generally with only one column this would be an easy fix, just do a ToString() or even SqlFunctions.StringConvert the integer field like so:</p>
  
  <pre><code>var queryResult = (from o in T1)
  join p in t2 on T1.ID equals SqlFunctions.Convert(T2.ID)
  </code></pre>
  
  <p>That works just fine. However, when I try to combine the two approaches, I get a "Invalid anonymous type member declarator" with the following:</p>
  
  <pre><code>var queryResult = (from o in T1)
  join p in t2 on new { T1.ID, T1.Weird} equals new {T2.ID, SqlFunctions.Convert( T2.Weird)}
  </code></pre>
  
  <p>After some research I found I needed to name the values, so I tried the following:</p>
  
  <pre><code>var queryResult = (from o in T1)
  join p in t2 on new {ID = T1.ID, Weird = T1.Weird} equals new {ID = T2.ID, Weird = SqlFunctions.Convert(T2.Weird)}
  </code></pre>
  
  <p>This tells me the call to the SqlFunctions function is ambiguous. I'm kind of out of thoughts and figured I may be going to far down one road when the answer is right out in front of me. Any idea how I can make that fairly basic SQL statement in LINQ work?</p>