LINQ Join On Multiple Columns With Different Data Types

Question

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:

 SELECT stuff
 FROM Table1 t1
 Inner Join Table2 t2 on t1.ID = t2.ID and t2.Weird = CAST(t1.Weird AS varchar(11))

Now in order to join on multiple columns in LINQ, I know I must do the following:

var queryResult = (from o in T1)
join p in t2 on new { T1.ID, T1.Weird} equals new {T2.ID, T2.Weird}

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.

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:

var queryResult = (from o in T1)
join p in t2 on T1.ID equals SqlFunctions.Convert(T2.ID)

That works just fine. However, when I try to combine the two approaches, I get a "Invalid anonymous type member declarator" with the following:

var queryResult = (from o in T1)
join p in t2 on new { T1.ID, T1.Weird} equals new {T2.ID, SqlFunctions.Convert( T2.Weird)}

After some research I found I needed to name the values, so I tried the following:

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)}

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?

Answer 1

Why don't you use .ToString() as you mentioned:

var queryResult = 
from o in T1 
join p in T2 
   on new {ID = T1.ID, Weird = T1.Weird} equals new {ID = T2.ID, Weird = T2.Weird.ToString()}

That should do the trick.