Join with comma separated values in SQL Server [Li

Question

I am writing a question with LINQ to join li. How do I get the result table with 3 table combinations? I have to combine the table in one line. Any ideas?

Peole             
--------------- 
Id     | 1            Id      |2
Name   | David        Name    |Ameyy
Surname| David1       Surname |Ameyy2


Appointment
---------------
Id        |19
PeopleId  |1
Subject   |description


Participant
---------------
Id            |1       Id             |2 
AppointmentId |19      AppointmentId  |19
PeopleId      |1       PeopleId       |2

Result
----------------------------------
Id         | 1
Subject    | Subject
Participant| David David1, Ameyy Ameyy2

Linq query;

    IQueryable<AppointmentPoolModel> query = db.Randevu.Join(db.Kisi,
              appointment => appointment .TALEPEDENKISI,
              people=> people.ID,
              (appointment , people)
              => new AppointmentPoolModel
                {
                   Id = appointment.ID,
                   Subject = appointment.Subject,
                   Note = appointment .NOTLAR,
                   NameSurname = people.Name+ " " + people.Surname,
                   RequestedId = people.ID,
                   //Participan = string.Join(",", )
                });


   var result = query.OrderBy(appointment => randevu.AppointmentStartDate).ToList();

Answer 1

You should investigate step by step.

Here a example withtwo dedicated joins and a groupBy (which is missing in your case):

public class People
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
}

public class Appointment
{
    public int Id { get; set; }
    public string Subject { get; set; }
}

public class Participation
{
    public int Id { get; set; }
    public int PeopleId { get; set; }
    public int AppointmentId { get; set; }
}

static void Main(string[] args)
{
    // Example-Data (Would have helped in this format ;))
    List<People> people = new List<People>()
    {
        new People() { Id = 1, Surname = "David1", Name = "David"},
        new People() { Id = 2, Surname = "Ameyy2", Name = "Ameyy"}
    };

    List<Appointment> appointments = new List<Appointment>()
    {
        new Appointment() { Id = 1, Subject = "description" }
    };

    List<Participation> participations = new List<Participation>()
    {
        new Participation() { Id = 1, PeopleId = 1, AppointmentId = 1 },
        new Participation() { Id = 1, PeopleId = 2, AppointmentId = 1 }
    };

    Console.WriteLine("***** JOIN appointment to participation *****");
    // At the beginning we want to join the table 'Appointment' with the n-to-n-Table "Participation".
    var AppointmentsAndParticipations = appointments.Join
    (
        participations, // Other table to connect
        a => a.Id, // Key in 1st table
        p => p.AppointmentId, // Key in 2nd table
        (a, p) => new { Appointment = a, PeopleId = p.PeopleId } // build new row
    );
    foreach (var item in AppointmentsAndParticipations)
    {
        // The result should be out appointment and the peopleId. We got "Appointment.Count*Participations.Count" rows
        Console.WriteLine(item.Appointment.Id.ToString().PadLeft(5) + ", " + item.Appointment.Subject.PadLeft(15) + ", " + item.PeopleId);
    }

    Console.WriteLine("***** JOIN people *****");
    // We need to join the people which belong to the Ids in participation
    var AppointmentsAndPeople = AppointmentsAndParticipations.Join
    (
        people, a => a.PeopleId, // Similar to 1st join...
        p => p.Id,
        (a, p) => new { Appointment = a.Appointment, People = p }
    );
    foreach (var item in AppointmentsAndPeople)
    {
        Console.WriteLine(item.Appointment.Id.ToString().PadLeft(5) + ", " + item.Appointment.Subject.PadLeft(15) + ", " + item.People.Name + " " + item.People.Surname);
    }

    Console.WriteLine("***** Group the rows *****");
    // Now we want to group the rows back to Appointment-Level. We group by Appointment and People will be out elements to be sum-up
    var AppointmentPools = AppointmentsAndPeople.GroupBy
    (
        key => key.Appointment, // Select the 'column' which shall be the keys
        group => group.People, // Select the 'column' which will be converted to a single value (like count, sum, max ..or in your case string.join())
        (key, group) => new // Build the output object..
        {
            Id = key.Id,
            Subject = key.Subject,
            Participants = string.Join(", ", group.Select(s => s.Name + " " + s.Surname))
        }
    );

    foreach (var item in AppointmentPools)
    {
        Console.WriteLine("Id: " + item.Id + ", Subject: " + item.Subject + ", Participants: " + item.Participants);
    }
}

Answer 2

You can try this.

var qPeoples = Participants.Join(Peoples,
                    pr => pr.PeopleId,
                    pe => pe.Id,
                    (pr, pe) => new { Part = pr, People = pe });

var result = Appointments.Select(app => new
    {
        app.Id,
        app.Subject,
        Participant = String.Join(",", qPeoples.Where(q => q.Part.AppointmentId == app.Id)
                                                .Select(s => new 
                                                    { FullName = String.Format( "{0} {1}"
                                                        , s.People.Name, s.People.Surname ) } ) )
    }).ToList();