Querying Entity Model. Part 10 – Group Concatenation

This time I try to generate a list of clients with their phone numbers as a single string from Entity Framework model. The logic is quite simple: make a group by each client, and concatenate all phone numbers with a group into a comma-separated string.

C# has a simple function to do this: string.Join(). Let’s try to run it on EF model.
LINQ query seems very simple. I use the same model like in previous post about Querying Entity Model. string.Join() should concatenate all phone numbers through Navigation property.

But when you execute this query, Entity Framework throws an exception.

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.

After some investigations I discovered that there’s no solution to make group concatenation in SQL Server and get a single string for each client from it. Instead we need to get a result set from SQL Server in a form of a simple join, then to make concatenation in C#. This can be done using ToList() in LINQ query like in the following example.

Later you should concatenate phone numbers using string.Join() or Aggregate() in your code.

You can look also at this wonderful post Using LINQ Group By And string.Join() / Aggregate() In Entity Framework 3.5 where the author investigates different approaches and performance.

When you look at T-SQL code generated by Entity Framework, you’ll see SQL execute a query with LEFT JOIN, so it produces as so many rows as at least the total number of phone numbers.

Positive and negative effects of this solution:

  • SQL Server: good – less computations (no string concatenation), bad – bigger result set;
  • network: bad – bigger result set is sent from SQL Server to client application;
  • C#: bad – more computations to fetch rows and concatenate strings;
  • architecture: good – the computation load can be distributed from SQL Server to application servers.


Leave a Reply

Your email address will not be published. Required fields are marked *