LINQ to Entites: Creating comma-delimited string from many-to-one relationship

Question

I have a couple of tables with a many-to-one relationship, and I'm trying to create a string that contains a comma-delimited string as follows.

Let's call them State and City - City table has a FK to State.ID, and States belong to Countries:

  var foo =  from item in _ctx.State
             where item.country_id == country_id
             select 
             new { id = item.ID, 
                 names = item.Name + ": " + String.Join(", ", 
                 (from c in _entity.City
                  where c.State.ID == item.ID 
                  select c.City_Name).ToArray())
             };
  return Json(foo.ToList());

I'm looking for something like:

[{ id = 3, names = "CA: A, B, C" }, { id = 5, names = "OR: D, E" }, etc.

With the String.Join (from this question) in there, I get:

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

Is there a way to do this?

Answer 1

You need to split your query into LINQ to Entities (DB stuff) and LINQ to Objects (String.Join and other methods):

var foo =  from item in _ctx.State
           where item.country_id == country_id
           select 
           new 
           { 
               id = item.ID, 
               names = item.Name,
               cities = from c in item.City select c.Name
           };
var bar = from item in foo.AsEnumerable
          select new
          {
              id = item.id, 
              names = item.names + ": " + String.Join(", ", item.cities.ToArray())
          };
return Json(bar.ToList());

Edit: This was close - I had to change the way cities was selected so that it was an array of strings, not City objects.