Concatenate multiple rows into a single row in LINQ

Question

Is is possible to concatenate multiple rows to a single row?

for example:

IEnumerable<sample> sam = new List<sample>()
{
    new sample{ id = 1, name = "sample 1", list = new List<int>{1,5,6}},
    new sample{ id = 2, name = "sample 2", list = new List<int>{2,9}},
    new sample{ id = 3, name = "sample 3", list = new List<int>{8,3,7}},
    new sample{ id = 4, name = "sample 4", list = new List<int>{3,4,8}},
    new sample{ id = 5, name = "sample 5", list = new List<int>{1,5,7}},
    new sample{ id = 6, name = "sample 6", list = new List<int>{6,9,7}}
};

output must:

{
    new sample { id = 1, name = "sample 1", list = "1,5,6" },
    new sample { id = 2, name = "sample 2", list = "2,9" },
    new sample { id = 3, name = "sample 3", list = "8,3,7" },
    new sample { id = 4, name = "sample 4", list = "3,4,8" },
    new sample { id = 5, name = "sample 5", list = "1,5,7" },
    new sample { id = 6, name = "sample 6", list = "6,9,7" }
};

That means from list the new row is now a string.

You won't be able to return samples, because list can be defined as a List<int> or a string, not both, but otherwise this is pretty trivial LINQ. — Rawling
– Rawling, Commented Sep 5, 2012 at 7:53

Daniel Hilgarth · Accepted Answer · 2012-09-05 08:05:22Z

8

Sure:

sam.Select(x => new { x.id, x.name, list = String.Join(",", x.list) });

Note: The result will be an anonymous type. We can't reuse the sample class here, because in that class list is of type List<int> and not string.

If you are stuck with .NET 3.5 or less, use this code instead:

sam.Select(x => new
                {
                    x.id, x.name,
                    list = String.Join(",", x.list.Select(y => y.ToString())
                                                  .ToArray())
                });

If you want to sort the list before getting the string you need to use this code:

sam.Select(x => new
                {
                    x.id, x.name,
                    list = String.Join(",", x.list.OrderBy(y => y))
                });

edited Sep 5, 2012 at 8:05

answered Sep 5, 2012 at 7:52

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Rawling Over a year ago

String.Join takes a string[], not a List<int>.

Daniel Hilgarth Over a year ago

@Rawling: There are several overloads of String.Join. My code uses this one: msdn.microsoft.com/en-us/library/dd992421.aspx

Rawling Over a year ago

@abatishchev/Daniel That's great, then. I can't wait to get my hands on 4/4.5.

abatishchev Over a year ago

@Rawling: Or even starting 3.5 according to the MDSN article Daniel has linked.

Rawling Over a year ago

@abatishchev It says "4.5, 4" for me. I did try this code in my 3.5 project before criticizing :)

|

ekad · Accepted Answer · 2016-10-03 14:18:31Z

As I needed to gather several records in one column (each employee may have many specialties) and then use it in a join, this is the way I resolved:

Having these entities:

1) EMPLOYEEs Entity

|EMPLOYEE_ID|

|001        |

|002        |

2) EMPLOYEE_SPECIALTIES Entity

|EmployeeId|SPECIALTY_CODE

|001       |AAA

|001       |BBB

|002       |DDD

|002       |AAA

I needed to gather specialties by employee in one columun:

|EmployeeId|SPECIALTY_CODE

|001       |AAA, BBB

|002       |DDD, AAA

Solution:

var query = from a in context.EMPLOYEE_SPECIALTIES.ToList()
            group a by a.EMPLOYEE_ID into g
            select new 
            {
                EmployeeId = g.Key,
                SpecialtyCode = string.Join(",", g.Select(x =>  
                x.SPECIALTY_CODE))
            };

var query2 = (from a in query
              join b in context.EMPLOYEEs on a.EmployeeId equals b.EMPLOYEE_ID
              select new EmployeeSpecialtyArea
              {
                  EmployeeId = b.EMPLOYEE_ID,
                  LastName = b.LAST_NAME,
                  SpecialtyCode = a.SpecialtyCode
              });

ViewBag.EmployeeSpecialtyArea = query2;

I hope this may help someone!

Collectives™ on Stack Overflow

Concatenate multiple rows into a single row in LINQ

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related