SQL Server STRING_AGG() : equivalent in Linq

Question

This is my table tbl_emp:

enter image description here

This is the result I need:

enter image description here

In SQL Server 2017, I can use STRING_AGG to get the desired result:

SELECT 
    name, 
    STRING_AGG(email,'') 'email', STRING_AGG(email2,'') 'email2'
FROM
    tbl_emp
GROUP BY 
    name

How can I get the same result in Linq? Any help would be greatly appreciated.

Based on the **images** of the data we have, are you not just after the `MAX` value? — Thom A, Jul 07 '21 at 08:49
No, not MAX value. I need to GROUP BY name and CONCAT email columns. That is why I mentioned STRING_AGG function. — TaKaNa, Jul 07 '21 at 08:55
You might want to give examples where you have *other* values then `NULL` then, as `MAX` would give the same result here. — Thom A, Jul 07 '21 at 08:56
Why not leave it as SQL? Why do you want to do it with LINQ? — mjwills, Jul 07 '21 at 09:27

Mansur Kurtov · Accepted Answer · 2021-07-07T13:02:31.823

1

In question not given any information about which ORM you are using, so i try to show one example to this case in linq to objects in c#, not fully solution for question

String_Agg aggregation function takes a set of values and combines them into a string, using a supplied separator. In LINQ (linq to objects) you may do it by using Group By, i think this example helps you:

class Program
    {
        static void Main(string[] args)
        {
            var emails = new List<EmailData>() { 
                new EmailData() {Name="Sam", Email="Email1" },
                new EmailData() {Name="Sam", Email="Email2" },
                new EmailData() {Name="Ted", Email="Email3" },
                new EmailData() {Name="Sam", Email="Email4" },
                new EmailData() {Name="Sam", Email="Email5" }
            };
            var grouping = emails.GroupBy(x => x.Name);
            foreach (var group in grouping)
            {
                Console.WriteLine(
                    $"{group.Key}: {string.Join(", ", group.Select(x => x.Email))}");
            }
            Console.ReadKey();
        }
    }
    public class EmailData
    {
        public string Name { get; set; }
        public string Email { get; set; }
    }

Result:

edited Jul 07 '21 at 13:02

answered Jul 07 '21 at 08:53

Mansur Kurtov

726
1
4
12

Answer not for the original question. – Svyatoslav Danyliv Jul 07 '21 at 12:29
1

i showed example, i didn't give fully solution )) – Mansur Kurtov Jul 07 '21 at 12:34
LINQ to Objects != LINQ to Entities. Your code not generates SQL query. – Svyatoslav Danyliv Jul 07 '21 at 12:35
Not with EF Core. linq2db has this support. – Svyatoslav Danyliv Jul 07 '21 at 12:47
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234620/discussion-between-mansur-kurtov-and-svyatoslav-danyliv). – Mansur Kurtov Jul 07 '21 at 12:49
How does this answer the question (I also ask @takana)? This doesn't deal with two email columns or null values, nor does it worry about SQL translation. – Gert Arnold Jul 09 '21 at 12:20

SQL Server STRING_AGG() : equivalent in Linq

1 Answers1