Natural join in .net

Question

Im trying to do a natural left join in Linq from 2 tables

the 2 tables

| questions |
+-----------+
| id        |
| question  |
+-----------+


|  answers  |
+-----------+
|  id       |
|  q_id (fk)|
|  answer   |
+-----------+

Im trying to retrieve a single row for each Question but with as may additional cols as needed

Im not sure if this is possible

                      ID   | Question   | Answer 1 | Answer 2 | An....
| view      |       |-----------------------------------------------+
+-----------+       |  1   |   question | answer1  | answer2  | ... |
| id        |       |  2   |   question | answer1  | answer2  | ... |
| question  |       |  3   |   question | answer1  | answer2  | ... |
| answer1   |  or   |  4   |   question | answer1  | answer2  | ... |
| answer2   |       |  5   |   question | answer1  | answer2  | ... |
| answer3   |       |  6   |   question | answer1  | answer2  | ... |
| answer... |       |  7   |   question | answer1  | answer2  | ... |
+-----------+       |-----------------------------------------------+

my C# Linq

        var joinedTable =
        from questions in db.Results
        join answers in db.Answers on questions.id equals answers.result_id
            into answers
        select new 
        { 
           questions.id, 
           TOTAL_ANSWERS = answers.Count(), 
           questions.SurveyDateCreated, 
           (answers.ForEach(a=> a.answer) as "Answer" + i++)
        };

possible duplicate of [Pivot data using LINQ](http://stackoverflow.com/questions/963491/pivot-data-using-linq) — Jim G., Nov 15 '12 at 15:37

score 0 · Answer 1 · answered Nov 15 '12 at 19:07

What you are looking for is called PIVOT. Best way to do this is to create a stored procedure in SQL and do your PIVOT in SQL. With LINQ it is possible but complicated.

Pivot With LINQ: http://madhukap.blogspot.com/2011/05/pivot-with-linq.html http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq

Natural join in .net

1 Answers1