CODEWITHSUNDEEP
c#linq
shogun
shogun

Reputation: 13

Concatenate result of group by into string

I have 2 tables

Patients{
    Id
}

PatientsDoctorAssociation{
    PatientId,
    DoctorId
}

Using LINQ-to-Entities I want to make a group by of the join of this 2 tables, and I would like to concatenate the DoctorId into a single string, like this:

list = (from a in db.Patients
        join b in db.PatientsDoctorAssociation on a.Id equals b.PatientId
        group new {A=a,B=b} by b.PatientId into g
        select new {
            PatientId = g.Key,
            DoctorIds = ??
        });

Can someone help me?

Upvotes: 1

Views: 247

Answers (3)

Maarten
Maarten

Reputation: 22945

Use string.Join().

The catch is, that is cannot be translated to SQL. Therefore you need to do that in-memory, after you have retrieved the query-result. You can use the .AsEnumerable() method for that.

var list = (from a in db.Patients
            join b in db.PatientsDoctorAssociation on a.Id equals b.PatientId
            group new { A = a, B = b } by b.PatientId
            into g
            select new
            {
                PatientId = g.Key,
                DoctorIds = g.Select(z => z.B.DoctorId)
            }
    )
    .AsEnumerable()
    .Select(x => new
    {
        PatientId = x.PatientId,
        DoctorIds = string.Join(",", x.DoctorIds)
    })
    .ToArray();

Upvotes: 1

Rinktacular
Rinktacular

Reputation: 1136

I think this is what you're looking for:

var list = (from a in db.Patients
join b in db.PatientsDoctorAssociation on a.Id equals b.PatientId
select b).toList();

That gets you the PatientId as well as the DoctorID based on your join.

If you mean string manipulation, can we see the format of your doctor names?

Upvotes: 0

Ofir Winegarten
Ofir Winegarten

Reputation: 9355

string.Join will do the trick.

DoctorIds = string.Join(",", g.Select(p=>p.B.DoctorId))

Upvotes: 0

Related Questions