How can I produce a join on a substring and an integer in Entity Framework?

Question

Using Entity Framework, I'm trying to join two tables like this.

...
join f in ent.FTypes on Int32.Parse(c.CourseID[0].ToString()) equals f.FTypeID
...

The first character of the string CourseID is a digit, and FTypeID is an int.

This doesn't seem to work though. The exception message I get is:

LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression."} System.Exception {System.NotSupportedException}

What I want to replicate is the SQL string equivalent (which works fine):

join FType f on SUBSTRING(c.CourseID, 1, 1) = f.FTypeID

Does anyone have the solution to have to do this in LINQ to Entities?

Answer 1

This is a rather nasty join, but I did some testing in Entity Framework with similar data and arrived at something you can test on yours.

It uses string.Substring to grab the first character from your string operand, and then uses a combination of the EF-only method SqlFunctions.StringConvert (these methods are found in System.Data.Objects.SqlClient) with a cast to double¹ and finally a string.Trim² for your integer operand.

I have tested this and confirmed that all functions are supported at least in EF 4. Several other methods proposed or featured in the question do not work, because Entity Framework does not know how to tranlsate them to the appropriate SQL equivalent.

join f in ent.FTypes
on c.CourseID.Substring(0, 1) 
   equals SqlFunctions.StringConvert((double)f.FTypeID).Trim()

It produces a SQL join that looks like the following:

INNER JOIN [dbo].[FTypes] AS [Extent2] 
ON ((SUBSTRING([Extent1].[CourseID], 0 + 1, 1)) = (LTRIM(RTRIM(STR( CAST( [Extent2].[FTypeID] AS float)))))) 
OR ((SUBSTRING([Extent1].[CourseID], 0 + 1, 1) IS NULL) AND (LTRIM(RTRIM(STR( CAST( [Extent2].[FTypeID] AS float)))) IS NULL))

So based on that, you might want to do some additional filtering as necessary.

Give it a shot and see if that helps solve the problem.

---

¹ The cast to double is necessary because SqlFunctions.StringConvert does not have an overload for integer and there is no other single best match, so I force one.

² The resultant string needs to be trimmed because the string conversion generates some excess padding.