LINQ to Entities ToString() - None of the proposed solutions work?

I'm posting this more because I'd like to learn more, because my work-around has been to basically avoid using LINQ to Entities! It would nice if I could use it though. So if anyone can enlighten me..

I'm working on an ASP.Net MVC 3 application (my first one) using code-first Entity Framework as my data model. It's going to be an online shop and I am working on the 'Admin' controller where, as an admin, you will be able to edit products etc. When it comes to adding a new product, you must specify a category, which means a dropdown list of categories.

I started off by just returning an IEnumerable collection of categories in the ViewBag, based on the information I found here. This seemed like a really nice way to approach things.

But I got the same error you'll see below. So following a suggestion I read online, I created a separate model specifically for creating new and editing existing products. The Categories collection is used to build a dropdown list in the view.

public class ProductModel
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public double Price { get; set; }
    public bool IsVisible { get; set; }

    public IEnumerable<SelectListItem> Categories
    {
        get
        {
            return new DataAccess()
                .Categories
                .OrderBy(c => c.Description)
                .Select(c => new SelectListItem
                    {
                        Value = c.Id.ToString(),
                        Text = c.Description
                    });
        }
    }
}

This results in the following error message: "LINQ to Entities开发者_高级运维 does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.". One proposed online solution was to use the SqlFunction methods provided by the Entity Framework. So I tried this instead ...

    public IEnumerable<SelectListItem> Categories
    {
        get
        {
            return new DataAccess()
                .Categories
                .OrderBy(c => c.Description)
                .Select(c => new SelectListItem
                    {
                        Value = SqlFunctions.StringConvert((double)c.Id),
                        Text = c.Description
                    });
        }
    }

But this results in this error message: "The specified method 'System.String StringConvert(System.Nullable`1[System.Double])' on the type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities store expression.".

I've even tried adding a read-only property to the data class called IdAsString, but this obviously makes the Entity Framework very unhappy!

In the end, I realised that it wasn't worth all the time I was putting into it, and just stopped using LINQ altogether. This works fine, but it would be nice to know if there is a "correct" way to do this that actually works!

This is my current working solution:

    public IEnumerable<SelectListItem> Categories
    {
        get
        {
            var dal = new DataAccess();
            var categories = dal.Categories;
            var items = new List<SelectListItem>();

            foreach (var category in categories)
                items.Add(new SelectListItem
                    {
                        Value = category.Id.ToString(),
                        Text = category.Description
                    });

            return items;
        }
    }

So if anyone is already doing this and they think they're doing it the "correct" way, then I'd love to know what you think!

Materialize the query before you invoke ToString will make the last part a non linq-to-entities query. You could optimize this by only selecting the id and description, then calling AsEnumerable, and then getting the selectlistitems.

    return new DataAccess() 
        .Categories 
        .OrderBy(c => c.Description)
        .AsEnumerable()
        .Select(c => new SelectListItem 
            { 
                Value = c.Id.ToString(), 
                Text = c.Description 
            });