Problem with converting int to string in Linq to entities

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

With EF v4 you can use SqlFunctions.StringConvert. There is no overload for int so you need to cast to a double or a decimal. Your code ends up looking like this:

var items = from c in contacts
            select new ListItem
            {
                Value = SqlFunctions.StringConvert((double)c.ContactId).Trim(),
                Text = c.Name
            };

Answer 2

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 3

Use LinqToObject : contacts.AsEnumerable()

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

Answer 4

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.

Answer 5

Brian Cauthon's answer is excellent! Just a little update, for EF 6, the class got moved to another namespace. So, before EF 6, you should include:

System.Data.Objects.SqlClient

If you update to EF 6, or simply are using this version, include:

System.Data.Entity.SqlServer

By including the incorrect namespace with EF6, the code will compile just fine but will throw a runtime error. I hope this note helps to avoid some confusion.

Answer 6

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 7

var selectList = db.NewsClasses.ToList<NewsClass>().Select(a => new SelectListItem({
    Text = a.ClassName,
    Value = a.ClassId.ToString()
});

Firstly, convert to object, then toString() will be correct.

Answer 8

One more solution:

c.ContactId + ""

Just add empty string and it will be converted to string.

Answer 9

If your "contact" is acting as generic list, I hope the following code works well.

var items = contact.Distinct().OrderBy(c => c.Name)
                              .Select( c => new ListItem
                              {
                                Value = c.ContactId.ToString(),
                                Text = c.Name
                              });

Thanks.

Answer 10

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 11

Using MySql, the SqlFunctions.StringConvert didn't work for me. Since I use SelectListItem in 20+ places in my project, I wanted a solution that work without contorting the 20+ LINQ statements. My solution was to sub-class SelectedListItem in order to provide an integer setter, which moves type conversion away from LINQ. Obviously, this solution is difficult to generalize, but was quite helpful for my specific project.

To use, create the following type and use in your LINQ query in place of SelectedListItem and use IntValue in place of Value.

public class BtoSelectedListItem : SelectListItem
{
    public int IntValue
    {
        get { return string.IsNullOrEmpty(Value) ? 0 : int.Parse(Value); }
        set { Value = value.ToString(); }
    }
}

Answer 12

if you use entity framework and you want to make the only int acceptable then you can use this in linq query you can try this

var items = from c in contacts
        select new ListItem
        {
            Value = (int)ContractId 
            Text = c.Name
        };

it will work because using (int) will cast your value to the int so you don't need any conversion for string to int and you get the result you want.

this worked for me in my project i think it would be helpful for you

Answer 13

My understanding is that you have to create a partial class to "extend" your model and add a property that is readonly that can utilize the rest of the class's properties.

public partial class Contact{

   public string ContactIdString
   {
      get{ 
            return this.ContactId.ToString();
      }
   } 
}

Then

var items = from c in contacts
select new ListItem
{
    Value = c.ContactIdString, 
    Text = c.Name
};

Answer 14

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 15

Can you try:

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