Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I concatenate strings in Entity Framework Query?

How do I concatenate strings in Entity Framework 4 I have a data from a column and I want to save as a string a comma separated string like "value1, value2, value3" Is there a method or an operator do do this in EF4? Example: lets say that I have two columns Fruit and Farms with the following values:

  • Apples
  • Bananas
  • Strawberries

If I do like this

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

Sure I will get this error

LINQ to Entities does not recognize the method 'System.String Join(System.String, System.Collections.Generic.IEnumerable`1[System.String])' method, and this method cannot be translated into a store expression.

Is there any solution to this?

like image 242
Ahmed Magdy Avatar asked Nov 04 '10 10:11

Ahmed Magdy


People also ask

How do you concatenate 2 strings?

Concatenation is the process of appending one string to the end of another string. You concatenate strings by using the + operator. For string literals and string constants, concatenation occurs at compile time; no run-time concatenation occurs.

Which function is used for string concatenation?

Use CONCATENATE, one of the text functions, to join two or more text strings into one string. Important: In Excel 2016, Excel Mobile, and Excel for the web, this function has been replaced with the CONCAT function.


2 Answers

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)
                      });
like image 92
Yakimych Avatar answered Oct 01 '22 17:10

Yakimych


Took @Yakimych answer and thought would provide mine if someone needed:

using (myDBEntities db = new myDBEntities())
            {
                var results = db.Table
                    .ToList()
                    .Where(x => x.LastName.StartsWith("K"))
                    .Select(
                    x => new
                    {
                        x.ID,
                        Name = x.LastName + ", " + x.FirstName
                    }
                    );

                lstCoaches.DataValueField = "ID";
                lstCoaches.DataTextField = "Name";
                lstCoaches.DataSource = results;
                lstCoaches.DataBind();
                ListItem item = new ListItem
                {
                    Value = "0",
                    Text = "-- Make a Selection --"
                };
                lstCoaches.Items.Insert(0,item);
                lstCoaches.SelectedIndex = 0;
            }
like image 21
PCPGMR Avatar answered Oct 01 '22 15:10

PCPGMR