Concatenate int and string in LINQ

Question

HI, I am using the following code

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

but following is the error while doing this...

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.

Can u please let me know how to concatenate....

Thanks....

Answer 1

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).

Answer 2

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)

Answer 3

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.

Answer 4

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
}

Answer 5

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
    });

Answer 6

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
});