Not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression

Question

I have looked at all the examples related to this but have not been able to solve my issue.

I am creating a dropdownlist in asp .net mvc3.

I have a repository which returns:

    public IEnumerable<SelectListItem> GetPropertyTypeSelectList()
    {
        var propertyTypes = from p in db.PropertyType
                                orderby p.PropertyTypeDescription
                                select new SelectListItem
                                {
                                    Text = p.PropertyTypeDescription,
                                    Value = p.PropertyTypeId.ToString()
                                };
        return propertyTypes;
    }

My viewmodel looks like this:

public class AddPropertyViewModel
{
    public Property Property { get; set; }
    public IEnumerable<SelectListItem> PropertyTypes { get; set; }
    public IEnumerable<SelectListItem> FurnishedTypes { get; set; }
}

My controller for "create" action for HttpGet looks like this:

    public ActionResult AddProperty()
    {
        AddPropertyViewModel viewModel = new AddPropertyViewModel
        {
            PropertyTypes = websiterepository.GetPropertyTypeSelectList()

        };
        return View(viewModel);
    }

and the view is like this:

    <div class="editor-label">
        @Html.LabelFor(model => model.Property.PropertyType)
        @Html.DropDownListFor(model => model.Property.PropertyType, Model.PropertyTypes)
    </div>

I am getting the error above. From what I have read it looks like ToString() is causing the problem. But I am not sure how to correct it.

Thanks.

Answer 1

LINQ to SQL doesn't know how to translate the .ToString() call to a SQL expression.

So replace:

var propertyTypes = 
    from p in db.PropertyType
    orderby p.PropertyTypeDescription
    select new SelectListItem
    {
        Text = p.PropertyTypeDescription,
        Value = p.PropertyTypeId.ToString()
    };

with:

var propertyTypes = db
    .PropertyType
    .OrderBy(x => x.PropertyTypeDescription)
    .ToList()
    .Select(x => new SelectListItem
    {
        Text = p.PropertyTypeDescription,
        Value = p.PropertyTypeId.ToString()
    });

Notice the .ToList() call to eagerly execute the SQL query after building the expression up until the OrderBy clause and then do the .ToString() on the client (LINQ to Objects instead of LINQ to Entities) where the .ToString() expression is perfectly supported.

So here basically we are constructing a SQL query up until the OrderBy clause (including) and then will call .ToList to eagerly execute this query and fetch the resultset on the client. Then we continue chaining with a .Select statement. But we are no longer doing any LINQ to Entities or SQL stuff. We are now doing LINQ to Objects because all the resultset is now in-memory. And doing .ToString in LINQ to Objects doesn't pose any challenge.

Another possibility is to use the SqlFunctions.StringConvert built-in function that knows how to translate it to SQL. This way you are keeping it lazy:

var propertyTypes = 
    from p in db.PropertyType
    orderby p.PropertyTypeDescription
    select new SelectListItem
    {
        Text = p.PropertyTypeDescription,
        Value = SqlFunctions.StringConvert((double)p.PropertyTypeId)
    };