Linq 2 Entities : Performing a join on two columns with different types

Question

Hello,

I'm currently using Entity framework, and I want to perform a Linq query with a join on two columns, one being of type 'String', and the other of type 'Int32'.

Somethign similar to

from FirstEntity obj in context.FirstEntity 
                      join SecondEntity obj2 in context.SecondEntity on obj.SecondEntityId equals obj2.Id.ToString()

with obj.SecondEntityId as String and obj2.Id as Int32

Unfortunately, the ToString operation does not seen to be supported by EF. I get stuck with this error (or some similar errors, if I want to convert string to Int32):

LINQ to Entities does not recognize the method 'System.String ToString()' method

and until now, I was not able to find a work around.

Thx for any help.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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