Entity Framework 4 Limitation - [System.NotSupportedException] - "LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression."

I received this System.NotSupportedException at runtime (not design time) today when attempting a conversion of a Nullable Integer field to a string for population of an ASP.NET MVC 2 dropdownlist:

"LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression."

I was consuming an Entity Framework Datamodel indirectly (via a model) via a call to the Html.DropDownListFor() method:

<div class="editor-field">
<%: Html.DropDownListFor(model => model.Year, ViewData.Model.YearList) %>
<%: Html.ValidationMessageFor(model => model.Year) %>
</div>

The best workaround for this issue I've found (that keeps all processing happening in SQL Server rather than doing a client-side evaluation/enumeration) is using the SqlFunctions.StringConvert method:

public IEnumerable YearList 
        {
            get
            {
                var list = new WorkforceEntities().Collections;
                return list.Select(a => new SelectListItem()
                {
                    Text = SqlFunctions.StringConvert((double)a.FinancialYear),
                    Value = SqlFunctions.StringConvert((double)a.FinancialYear.Value)
                }).Distinct().ToList();
            }
        }

Unfortunately this is a limitation of the Entity Framework versions 1 and 2 - as .ToString() is not one of the supported CLR to Database Canonical Model translations as detailed here: http://msdn.microsoft.com/en-us/library/bb738681.aspx

Ensuring the server side evaluatation takes place is more important if we were filtering this list - but the golden rule and preferred outcome is the same - to minimize the amount of data going across the wire.

Checking SQL Profiler on the SQL server side, this evaluates to the following T-SQL in SQL Server:

SELECT 
[Distinct1].[C1] AS [C1], 
[Distinct1].[C2] AS [C2], 
[Distinct1].[C3] AS [C3]
FROM ( SELECT DISTINCT 
 1 AS [C1], 
 STR( CAST( [Extent1].[Quarter] AS float)) AS [C2], 
 STR( CAST( [Extent1].[Quarter] AS float)) AS [C3]
 FROM [cfg].[Collection] AS [Extent1]
)  AS [Distinct1]

Which is using all SQL Server Canonical Functions - as best practice (performance-wise) dictates.

DDK