How to use LINQ to select from 5 tables?

I have 5 tables:

course_id | course_name    (course)
------------------------------    
    1     | Basic1
    2     | Basic2
    3     | Basic3
    4     | Basic4
    5     | Basic5

course_id | trainer_id (course_trainer)
-----------------------------
    1     |    1
    1     |    2
    2     |    2
    3     |    2
    4     |    3
    4     |    2
    5     |    3


course_id | topic_id (course_topic)
-----------------------------
    1     |    1
    1     |    2
    2     |    2
    3     |    2
    4     |    3
    4     |    2
    5     |    3

trainer_id| trainer_name (trainer)
-----------------------------
    1     |    Tom
    2     |    Thomas
    3     |    Sue

tropic_id | topic_name (topic)
-----------------------------
    1     |    Skill 1
    2     |    Skill 2
    3     |    Skill 3

How can I use LINQ to select with result as below

Course_name    | Trainer_name     | Topic_name
----------------------------------------------
Basic 1        | Tom, Thomas      | Skill 1, Skill 2
Basic 2        | Thomas           | Skill 2
Basic 3        | Thomas           | Skill 2
Basic 4        | Sue, Thomas      | Skill 3, Skill 2
Basic 5        | Sue              | Skill 3

That is my code in C#, but the result isn't correct. Please help me, many thanks !

public class course_datatable
{
    public string course_name {get; set;}
    public string trainer_name {get; set;}
    public string topic_name {get; set;}
}

IQueryable<course_datatable> coursequery =
    from c in db.course
    join ct in db.course_trainer on c.course_id equals ct.course_id
    join t in db.trainers on ct.trainer_id equals t.trainer_id
    join ctopic in db.course_topic on c.course_id equals ctopic.course_id
    join topic in db.topic on ctopic.topic_id equals topic.topic_id
    select new course_datatable()
    {
        course_name = c.course_name,
        trainer = t.tra开发者_运维技巧iner_name,
        topic = topic.topic_name
    };

Get your data from your database:

var result = context.Courses.Select(c => 
    new { Course = c, Trainers = c.Trainers, Skills = c.Skills }).ToList();

and then flatten the Trainers and Skills objects using String.Join:

result.Select(r => new 
{ 
    Course = r.Course.Course_Name, 
    Trainer = String.Join(",", r.Trainers.Select(t => t.TrainerName).ToArray()),
    Skill = String.Join(",", r.Skills.Select(S => S.SkillName).ToArray())
});

edit

Using your schema, I'll rename so that it should work.

var result = db.course.Select(c => new 
{ 
    Course = c, 
    Trainers = c.course_trainer.trainers, 
    Skills = c.course_topic.topic 
}).ToList();

result.Select(r => new 
{ 
    Course = r.Course.course_Name, 
    Trainer = String.Join(",", r.Trainers.Select(t => t.trainer_name).ToArray()),
    Skill = String.Join(",", r.Skills.Select(S => S.topic_name).ToArray())
});

You can do this all in one statement but I've structured it this way so that it's hopefully clearer for you.

Because you seem unable to use my initial answer (which is preferred because doesn't require redundant join conditions), I'll work with your existing code and show you how to group and project.

Starting with this:

var coursequery =
    from c in db.course
    join ct in db.course_trainer on c.course_id equals ct.course_id
    join t in db.trainers on ct.trainer_id equals t.trainer_id
    join ctopic in db.course_topic on c.course_id equals ctopic.course_id
    join topic in db.topic on ctopic.topic_id equals topic.topic_id
    select new course_datatable()
    {
        course_name = c.course_name,
        trainer = t.trainer_name,
        topic = topic.topic_name
    };

You then want to GroupBy the course_name

var groups = coursequery.GroupBy(item => item.course_name);

and then each group needs to project into your new result type

var result = groups.Select(group =>
    new course_datatable
    {
        course_name = group.Key,
        trainer_name = String.Join(",", group.Select(i=> i.trainer_name).ToArray()),
        topic_name = String.Join(",", group.Select(i => i.topic_name).ToArray()),
    }).ToList();

or if you want to try something else (for fun) use LINQs Aggregate method, rarely used:

var result = groups.Select(group =>
    group.Aggregate((initial, next) =>
    {
        initial.topic_name += String.Format(", {0}", next.topic_name);
        initial.trainer_name += String.Format(", {0}", next.trainer_name);
        return initial;
    })).ToList();