How to Concatenate Strings in Entity Framework Query

How do I concatenate strings in Entity Framework Query?

You have to execute the query before projecting. Otherwise EF tries to translate the Join method into SQL (and obviously fails).

var results = this.context
                  .Farms
                  .ToList()
                  .Select(f => new
                      {
                          f.Id, 
                          Fruits = string.Join(", ", f.Fruits)
                      });

How to query with Contains on a concatenated string in Entity Framework

Only string.Concat(string, string) overload is supported.

Use + operator instead:

.Where(serial => 
    (serial.ProductClass + serial.ProductNumber.ToString() + serial.CodeNumber.ToString())
    .Contains(searchText))

concatenate strings in LINQ query

you can use it:

FullName = user.UserName + " " + user.FirstName

But I think that it could be better solution (of cource if it's possible for you):

public class User
{
   public int Id {get;set;}
   public string UserName {get;set;}
   public string FirstName {get;set;}
   public string LastName {get;set;}
   public string FullName
   {
      get
      {
         return string.Format("{0} {1}", FirstName, LastName);
      }
   }
}

then in your query build it:

var userList = users.Select(user => new User()
            {
                Id = user.Id,
                UserName = user.UserName,
                FirstName = user.FirstName,
                LastName = user.LastName
            }).ToList();

Concatenating String in LINQ-to-Entities

Ok, here's a guess. I just typed this into LinqPad:

From x in Enumerable.Empty(Of String)().AsQueryable()
Select x + " " + x

And got this as the equivalent expression tree (sorry, LinqPad show this C#-y):

System.String[]
   .Select (x => String.Concat (x, " ", x))

Then I typed this:

From x in Enumerable.Empty(Of String)().AsQueryable()
Select x + " " + x

And the expression tree became:

System.String[]
   .Select (x => String.Concat (new String[] { x, " ", x, " ", x } ))

I'm guessing this has something to do with the fact that Concat has overloads for up to four parameters, and then there is an overload that takes a parameter array. Maybe the LINQ provider does not support that last one.

EDIT: According to this answer LINQ to entities does not current support string.Concat with something other than strings.

efcore 3.1 does not support querying with string concatenation?

Try this

await this.Db.ACoolDbSet.Where(y => y.Plums.ToString() + " " + y.Pears.ToString() == "LOL").ToListAsync();

Entity Framework with LINQ aggregate to concatenate string?

Thanks to moi_meme for the answer. What I was hoping to do is NOT POSSIBLE with LINQ to Entities. As others have suggested, you have to use LINQ to Objects to get access to string manipulation methods.

See the link posted by moi_meme for more info.

Update 8/27/2018 - Updated Link (again) - https://web.archive.org/web/20141106094131/http://www.mythos-rini.com/blog/archives/4510

And since I'm taking flack for a link-only answer from 8 years ago, I'll clarify just in case the archived copy disappears some day. The basic gist of it is that you cannot access string.join in EF queries. You must create the LINQ query, then call ToList() in order to execute the query against the db. Then you have the data in memory (aka LINQ to Objects), so you can access string.join.

The suggested code from the referenced link above is as follows -

var result1 = (from a in users
                b in roles
           where (a.RoleCollection.Any(x => x.RoleId = b.RoleId))
           select new 
           {
              UserName = a.UserName,
              RoleNames = b.RoleName)                 
           });

var result2 = (from a in result1.ToList()
           group a by a.UserName into userGroup
           select new 
           {
             UserName = userGroup.FirstOrDefault().UserName,
             RoleNames = String.Join(", ", (userGroup.Select(x => x.RoleNames)).ToArray())
           });

The author further suggests replacing string.join with aggregate for better performance, like so -

RoleNames = (userGroup.Select(x => x.RoleNames)).Aggregate((a,b) => (a + ", " + b))

Concatenate int and string in LINQ to Entities

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

---

Related Topics