[Solved]-Linq 2 Entities : Performing a join on two columns with different types-LINQ,C#

In LINQ to SQL you can use Convert.ToString(obj2.Id), but it seems EF doesn't support that (see this thread on MSDN). However, you might be able to use a Model Defined Function.

Update: Since it doesn't seem an EF solution will work, I would just create a SQL View with your int field converted and whatever other fields you need. Or you could keep your existing entities and just join across a two-column view with the int and string IDs. The query optimizer should be able to figure out what you're doing to make the performance impact negligible.

Finally, I made a view based upon the table, with a new int field holding the converted string, on DB side. Then I mapped my EF entity to the view. It works :)

Sad I didn't find a proper solution on EF side. Hopefully, the 4.0 version will solve this kind of issues.

Anyway, thx for your help.

Does it help if you use the functional C# syntax instead of the special Linq syntax?

var results = FirstEntity.Join(SecondEntity, 
     obj => obj.SecondEntityId, obj2 => obj2.Id.ToString(), 
    (obj, obj2) => new { First = obj, Second = obj2 });

We typically break something like this up into 2 queries (lack of ToString() in linq to entities support makes me want to harm small children).

var query1 = (from SecondEntity obj2 in context.SecondEntity
             select obj2.ID).ToList();

// now we're using linq to objects which does support ToString()
query1 = query1.Select(x => x.ToString());

// mixing linq to entities and linq to objects
var query2 = from FirstEntity obj in context.FirstEneity
             join SecondEntity obj2 in query1 on obj.SecondEntityId equals obj2.ID

I'm doing this without VS, so some of the syntax could be wrong and it's not a particularly nice solution, but EF is V1.