Concatenate int and string in LINQ to Entities

I am using the following code:

from c in Country
 where c.IsActive.Equals(true)
 orderby c.CountryName
 selec开发者_开发百科t new
 {
     countryIDCode = c.CountryID + "|" + c.TwoDigitCode,
     countryName = c.CountryName
 }

But I get this error while running it:

Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting Entity Data Model primitive types.

CountryID is int type and TwoDigitCode is string type.

How do I concatenate properly?

---

Use System.Data.Objects.SqlClient.SqlFunctions.StringConvert

 from c in Country
 where c.IsActive.Equals(true)
 orderby c.CountryName
 select new
 {
     countryIDCode = SqlFunctions.StringConvert((double)c.CountryID) 
                     + "|" + c.TwoDigitCode,
     countryName = c.CountryName
 }

---

If this error is preventing you from progressing and is a small dataset you could could hydrate your retrieval from the database by by enumerating the query (call ToList). From that point on, your operations will be against in-memory objects and you may not encounter the error you are receiving.

var countries = (from c in Country
where c.IsActive.Equals(true)
orderby c.CountryName
select c).ToList();


var countryCodes = (from c in countries
where c.IsActive.Equals(true)
    orderby c.CountryName
    select new
    {
        countryIDCode = c.CountryID + "|" + c.TwoDigitCode,
        countryName = c.CountryName
    });

---

This topic contains a list of CLR methods that can be converted to command tree canonical functions and executed on the server:

MSDN

For CLR methods not on this list, you would have to pull the results down to the client using .AsEnumerable() and execute a LINQ to Objects query.

---

This is a limitation of the old version of the Entity Framework. I think that with v4 it is solved. For your version the workaround is to convert the result to an enumerable:

from a in 
(from c in Country
where c.IsActive.Equals(true)
orderby c.CountryName).AsEnumerable()
select new
{
    countryIDCode = a.CountryID + "|" + a.TwoDigitCode,
    countryName = a.CountryName
}

---

There is no mapping to a Canonical Function for the int to string casting.

So just return the Int and the String in 2 different columns and then concatenate them in .NET after using the AsEnumerable method:

var cListTemp = from c in Country
    where c.IsActive.Equals(true)
    orderby c.CountryName
    select new
    {
        countryID = c.CountryID,
        twoDigitCode = c.TwoDigitCode,
        countryName = c.CountryName
    };

var cList = cListTemp.AsEnumerable().Select(c => new {
    countryIDCode = c.countryID + "|" + c.twoDigitCode,
    countryName = c.countryName
});

---

I was able to concatenate strings with the following code:

l.StateName + " " + SqlFunctions.StringConvert( (double?)l.Zip ).Trim()

It seems like just SqlFunctions.StringConvert( (double?)l.Zip ) would be good enough, but the resulting string has a bunch of padding on the left, which causes string comparisons to not match. Turns out, Trim() works to shave off the extra. I believe SqlFunctions.StringConvert( (double?)l.Zip ).Trim() effectively turns into the SQL: LTrim(RTrim(STR(Zip))).

---

Use c.CountryId.ToString() to get a string representation of your CountryId and concatenate that to your TwoDigitCode field:

from c in Country
 where c.IsActive.Equals(true)
 orderby c.CountryName
 select new
 {
     countryIDCode = c.CountryID.ToString() + "|" + c.TwoDigitCode,
     countryName = c.CountryName
 }

I hope your error actually is a compiler error (can't think of any way the compiler will allow this query. I am not sure how it knows about the Linq to Entities part though).

---

If you need to concat them together as a string you could use String.Format method:

countryIDCode = String.Format("{0}|{1}", c.CountryID, c.TwoDigitCode)