System.String ToString() error on a database call using c#

I'm running into a snag with some code of mine. I have a controller in which I calling to retrieve a company list from my database. However, when I test it I get an error stating that states: LINQ to Entities does not recognize the method System.String ToString() method, and this method cannot be translated into a store expression.

I cannot seem to figure out a way to fix that and it's been suggested that I use a foreach loop.

public List<SelectListItem> GetCompanyList()
{
    using (var ctx = new Risc_Track_beEntities())
    {
        var companies = ctx.tblCodeBrokerComps.Where(comp => comp.BrokerCompStatus == 1);
        var activeBrokerLocs = companies.Select(b => new SelectListItem
        {
            Value = b.BrokerCompID.ToString(),
            Text = b.BrokerComp
        });
        return activeBrokerLocs.ToList<SelectListItem>();
    }
}
Jon Skeet
people
quotationmark

LINQ to SQL doesn't know how to translate ToString() into SQL in this case.

The simplest solution is to use AsEnumerable to "transfer control" to LINQ to Objects. You can use an initial Select to make sure you only pull the relevant fields from the database, then AsEnumerable to get an IEnumerable<T>, then another Select for the final result:

var activeBrokerLocs = companies.Select(b => new { b.BrokerCompID,
                                                   b.BrokerComp })
                                .AsEnumerable()
                                .Select(b => new SelectListItem
                                {
                                    Value = b.BrokerCompID.ToString(),
                                    Text = b.BrokerComp
                                });

people

See more on this question at Stackoverflow