How can I create a union in a substring and an integer in the Entity Framework?

Question

Using the Entity Framework, I am trying to join two tables as follows.

... 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 int.

This does not seem to work. The exception message I receive is the following:

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

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

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

Does anyone have a solution to do this in LINQ for Entities?

Answer 1

This is a pretty nasty connection, but I did some testing in the Entity Framework with similar data and came up with something that you can test on your own.

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

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

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

It creates an SQL join that looks like this:

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

Therefore, based on this, you may want to do additional filtering as needed.

Take a picture and see if this helps solve the problem.

---

¹ Double SqlFunctions.StringConvert necessary because SqlFunctions.StringConvert has no overload for an integer, and there is no other single best match, so I force it.

² The resulting string must be truncated because the string conversion generates some redundant padding.