CODEWITHSUNDEEP
linqentity-frameworklinq-to-entities
Waheed
Waheed

Reputation: 10216

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
 select 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?

Upvotes: 11

Views: 19182

Answers (8)

Sam Rueby
Sam Rueby

Reputation: 6127

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

Upvotes: 2

flyfisher1952
flyfisher1952

Reputation: 432

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
 }

Upvotes: 24

Mariano Desanze
Mariano Desanze

Reputation: 8163

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

Upvotes: 3

David Espart
David Espart

Reputation: 11780

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
}

Upvotes: 3

Nicholas Murray
Nicholas Murray

Reputation: 13533

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

Upvotes: 7

gingerbreadboy
gingerbreadboy

Reputation: 7769

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.

Upvotes: 5

Dan Diplo
Dan Diplo

Reputation: 25339

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)

Upvotes: -3

Johannes Rudolph
Johannes Rudolph

Reputation: 35741

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

Upvotes: -3

Related Questions