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.
|
1 2 3 4 5 6 7 |
var query101 = ctx.Clients .Select(c => new { c.Name, phones = string.Join(", ", c.Phones.Select(ph => ph.Number)) } ); |
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.
|
1 2 3 4 5 6 7 |
var query102 = ctx.Clients .Select(c => new { c.Name, phones = c.Phones.Select(ph => ph.Number).ToList() } ); |
Later you should concatenate phone numbers using string.Join() or Aggregate() in your code.
|
1 2 |
string.Join(", ", record.phones) record.phones.Aggregate((a, b) => (a + ", " + b)) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], [Project1].[C1] AS [C1], [Project1].[Number] AS [Number] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent2].[Number] AS [Number], CASE WHEN ([Extent2].[ClientId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [dbo].[Clients] AS [Extent1] LEFT OUTER JOIN [dbo].[Phones] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ClientId] ) AS [Project1] ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC |
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.