string.Join in Linq to Entity queries

Question

I'm currently shifting my data access code from L2S to Entity framework db first. I have problem with queries like following

var emps = (from emp in DataContext.setupEmployees
                       let contacts = DataContext.setupContacts.Where(x => x.EmployeeID == emp.EmployeeID).Select(x => x.Contact)
                       select new 
                       {
                           EmployeeName = emp.EmployeeName,
                           Contacts = string.Join(", ", contacts.ToArray())
                       }).ToList();

EF tells me it can't convert the method string.join into stored expression. One obvious work-around is to bring the objects in memory (ToList, AsEnumerable etc) and then call string.Join method like

var emps = (from emp in DataContext.setupEmployees
                       let contacts = DataContext.setupContacts.Where(x => x.EmployeeID == emp.EmployeeID).Select(x => x.Contact)
                       select new 
                       {
                           EmployeeName = emp.EmployeeName,
                           Contacts = contacts
                       }).ToList().Select(x=>new{
                            x.EmployeeName,
                            Contacts = string.Join(", ",x.Contacts)
                       });

This solution works perfectly but it's just that it's more verbose and I'd have to duplicate select clause in lots of queries that used to work fine in L2S.

My question: is there any way I can avoid writing this ToList thing and call string.Join and similar methods in EF query?

http://stackoverflow.com/questions/4215066/entity-framework-with-linq-aggregate-to-concatenate-string — Andreas, Feb 16 '12 at 12:13
http://stackoverflow.com/questions/4095658/how-do-i-concatenate-strings-in-entity-framework-query — Andreas, Feb 16 '12 at 12:13

score 3 · Answer 1 · answered Jul 05 '12 at 14:03

I would recommend creating Contacts as an IEnumerable<string> datatype instead of string datatype. You can than use Contacts to produce any output you like comma seperated etc...

You can change your query to look like this:

var emps = (from emp in DataContext.setupEmployees
            select new 
            {
              EmployeeName = emp.EmployeeName,
              Contacts = DataContext.setupContacts.Where(x => x.EmployeeID == emp.EmployeeID).Select(x => x.Contact)
            }).ToList();

string.Join in Linq to Entity queries

1 Answers1