Problem with converting int to string in Linq to e

Question

var items = from c in contacts
            select new ListItem
            {
                Value = c.ContactId, //Cannot implicitly convert type 'int' (ContactId) to 'string' (Value).
                Text = c.Name
            };
var items = from c in contacts
            select new ListItem
            {
                Value = c.ContactId.ToString(), //Throws exception: ToString is not supported in linq to entities.
                Text = c.Name
            };

Is there anyway I can achieve this? Note, that in VB.NET there is no problem use the first snippet it works just great, VB is flexible, im unable to get used to C#'s strictness!!!

Answer 1

I solved a similar problem by placing the conversion of the integer to string out of the query. This can be achieved by putting the query into an object.

var items = from c in contacts
            select new 
            {
                Value = c.ContactId,
                Text = c.Name
            };
var itemList = new SelectList();
foreach (var item in items)
{
    itemList.Add(new SelectListItem{ Value = item.ContactId, Text = item.Name });
}

Answer 2

I ran into this same problem when I was converting my MVC 2 app to MVC 3 and just to give another (clean) solution to this problem I want to post what I did...

IEnumerable<SelectListItem> producers = new SelectList(Services.GetProducers(),
    "ID", "Name", model.ProducerID);

GetProducers() simply returns an entity collection of Producers. P.S. The SqlFunctions.StringConvert didn't work for me.

Answer 3

public static IEnumerable<SelectListItem> GetCustomerList()
        {
            using (SiteDataContext db = new SiteDataContext())
            {
                var list = from l in db.Customers.AsEnumerable()
                           orderby l.CompanyName
                           select new SelectListItem { Value = l.CustomerID.ToString(), Text = l.CompanyName };

                return list.ToList();
            }
        }

Answer 4

var items = from c in contacts
select new ListItem
{
    Value = String.Concat(c.ContactId), //This Works in Linq to Entity!
    Text = c.Name
};

I found that SqlFunctions.StringConvert((double)c.Age) did not work for me either the field is of type Nullable<Int32>

Took me a lot of searching over the last few days of trial and error to find this.

I hope this helps a few coders out there.

Answer 5

Use LinqToObject : contacts.AsEnumerable()

var items = from c in contacts.AsEnumerable()
            select new ListItem
            {
                Value = c.ContactId.ToString(),
                Text = c.Name
            };

Answer 6

SqlFunctions.StringConvert will work, but I find it cumbersome, and most of the time, I don't have a real need to perform the string conversion on the SQL side.

What I do if I want to do string manipulations is perform the query in linq-to-entities first, then manipulate the stings in linq-to-objects. In this example, I want to obtain a set of data containing a Contact's fullname, and ContactLocationKey, which is the string concatination of two Integer columns (ContactID and LocationID).

// perform the linq-to-entities query, query execution is triggered by ToArray()
var data =
   (from c in Context.Contacts
   select new {
       c.ContactID,
       c.FullName,
       c.LocationID
   }).ToArray();

// at this point, the database has been called and we are working in
// linq-to-objects where ToString() is supported
// Key2 is an extra example that wouldn't work in linq-to-entities
var data2 =
   (from c in data
    select new {
       c.FullName,
       ContactLocationKey = c.ContactID.ToString() + "." + c.LocationID.ToString(),
       Key2 = string.Join(".", c.ContactID.ToString(), c.LocationID.ToString())
    }).ToArray();

Now, I grant that it does get cumbersome to have to write two anonymous selects, but I would argue that is outweighed by the convenience of which you can perform string (and other) functions not supported in L2E. Also keep in mind that there is probably a performance penalty using this method.