linq to entity join on string equals int

Question

I saw this post Linq int to string, and tried it

            var personalInfoQuery = from t in crnnsupContext.Tombstones
                                    join i in crnnsupContext.InitialEducations on t.InitialEducation equals SqlFunctions.StringConvert((double)i.InitalEducationID) 
                                    where t.RegNumber == 25952
                                    select new CPersonalInfo 
                                    { 
                                       Tombstone = t, 
                                       InitialEducation = i
                                    };

in the database t.InitialEducation is char, i.InitalEducationID is int, but the retrieved result is null. I am pretty sure the value is not empty in the SQL server. So I think the problem is SqlFunctions.StringConvert((double)i.InitalEducationID)

when i remove the join statement, it got this person's information.

Does anyone know why. thanks

t and i are the instance of your class. You are missing the property Name. I mean t.VariableName. — Pankaj, Mar 29 '12 at 17:34

score 1 · Accepted Answer · answered Apr 03 '12 at 13:44

Finally find the reason!! t.InitialEducation is nvarchar(1) in the database, i.InitalEducationID is int, after I modified to SqlFunctions.StringConvert((double)i.InitialEducationID, 1) it works!

"1" is the length of the returned string, the default length is 10, I guess there are some extra space.

score 0 · Answer 2 · answered Apr 02 '12 at 14:17

Since you're using the SqlFunctions.StringConvert method, I'm assuming you're using EF as the underlying LINQ provider, no?

From the information given, it would appear that you're looking to do a 1 to many join on those properties. The code you've written could coalesce (not enough info on the context to be certain) as an INNER JOIN in SQL, so to force the LEFT JOIN behavior, you can add a .DefaultIfEmpty() call:

crnnsupContext.InitialEducations.DefaultIfEmpty()

but i am sure the result should not be empty – pita Apr 02 '12 at 18:07 — pita, Apr 02 '12 at 18:07

linq to entity join on string equals int

2 Answers2