C# linq query from sql server into one table

Question

I have three tables. I would like to used C# linq to turn into one table. For example:

Schedule:
+------+--------+--------+
| Name | DateId | TaskId |
+------+--------+--------+
| John |      2 |     32 |
| John |      3 |     31 |
| Mary |      1 |     33 |
| Mary |      2 |     31 |
| Tom  |      1 |     34 |
| Tom  |      2 |     31 |
| Tom  |      3 |     33 |
+------+--------+--------+

Date:

+----+------------+
| Id |    Date    |
+----+------------+
|  1 | Monday     |
|  2 | Tuesday    |
|  3 | Wednesday  |
|  4 | Thursday   |
|  5 | Friday     |
+----+------------+

Task:

+----+----------+
| Id |   Task   |
+----+----------+
| 31 | School   |
| 32 | Homework |
| 33 | Break    |
| 34 | Teaching |
+----+----------+

I would like to have a table like this:

+--------+----------+----------+-----------+----------+
| Person |  Monday  | Tuesday  | Wednesday | Thursday |
+--------+----------+----------+-----------+----------+
| John   |          | Homework | School    |          |
| Mary   | Break    | School   |           |          |
| Tom    | Teaching | School   | Break     |          |
+--------+----------+----------+-----------+----------+

I could not think of any good way doing this.

Any suggestion would be helpful

Thanks

Please try to format your question in a readable format, this is clearly not how you should ask a question. — Orel Eraki, Sep 25 '16 at 21:09
Possible duplicate of [this](http://stackoverflow.com/questions/9720225/how-to-perform-join-between-multiple-tables-in-linq-lambda). — Han, Sep 25 '16 at 21:20
you need **pivot table** from this and you can refer this [link] (http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq) — Keppy, Sep 26 '16 at 08:31

score 1 · Answer 1 · answered Sep 26 '16 at 00:57

It is called "transpose".

        var persons = new[] {   new { name="John", dateId=2,taskId=32},
                                new { name="John", dateId=3,taskId=31},
                                new { name="Mary", dateId=1,taskId=33},
                                new { name="Mary", dateId=2,taskId=31},
                                new { name="Tom", dateId=1,taskId=34},
                                new { name="Tom", dateId=2,taskId=31},
                                new { name="Tom", dateId=3,taskId=33}
                            };

        var dates = new[] { new { dateId=1, desc="Monday"},
                            new { dateId=2, desc="Tuesday"},
                            new { dateId=3, desc="Wednesday"},
                            new { dateId=4, desc="Thursday"},
                            new { dateId=5, desc="Friday"}
                            };

        var tasks = new[] { new { taskId=31, desc="School"},
                            new { taskId=32, desc="Homework"},
                            new { taskId=33, desc="Break"},
                            new { taskId=34, desc="Teaching"}
                            };

        var qry = from p in (from p in persons
                             join d in dates on p.dateId equals d.dateId
                             join t in tasks on (int)p.taskId equals (int)t.taskId
                             select new { name = p.name, monday = d.dateId == 1 ? t.desc : "", tuesday = d.dateId == 2 ? t.desc : "", wednesday = d.dateId == 3 ? t.desc : "", thursday = d.dateId == 4 ? t.desc : "", friday = d.dateId == 5 ? t.desc : "" })
                  group p by p.name into q
                  select new { q.Key, monday=q.Max(a => a.monday),tuesday=q.Max(a => a.tuesday), wednesday = q.Max(a=>a.wednesday), thursday = q.Max(a => a.thursday), friday=q.Max(a => a.friday)};

        foreach ( var a in qry.ToList())
        {
            Console.WriteLine(String.Format("{0}\t{1}\t{2}\t{3}\t{4}\t{5}",a.Key, a.monday, a.tuesday, a.wednesday, a.thursday, a.friday));
        }

Is there a way to select columns dynamically? For example, If I only want Monday and Friday for now, later I only want to see Wednesday. Am I able to pass in a variable to filter the columns I want. I would like to just select the columns I wanted. — sylanter, Sep 27 '16 at 01:46
@sylanter, as far as I know, the column has to be hard-coded. — Jules, Sep 27 '16 at 04:17

user449689 · Accepted Answer · 2016-09-27T06:38:10.350

Starting from this set of data:

var schedules = new[] {   new { Name = "John", DateId = 2, TaskId = 32},
                          new { Name = "John", DateId = 3, TaskId = 31},
                          new { Name = "Mary", DateId = 1, TaskId = 33},
                          new { Name = "Mary", DateId = 2, TaskId = 31},
                          new { Name = "Tom", DateId = 1, TaskId = 34},
                          new { Name = "Tom", DateId = 2, TaskId = 31},
                          new { Name = "Tom", DateId = 3, TaskId = 33}
                        };

    var dates = new[] { new { DateId = 1, Desc = "Monday"},
                        new { DateId = 2, Desc = "Tuesday"},
                        new { DateId = 3, Desc = "Wednesday"},
                        new { DateId = 4, Desc = "Thursday"},
                        new { DateId = 5, Desc = "Friday"}
                        };

    var tasks = new[] { new { TaskId = 31, Desc = "School"},
                        new { TaskId = 32, Desc = "Homework"},
                        new { TaskId = 33, Desc = "Break"},
                        new { TaskId = 34, Desc = "Teaching"}
                        };

You can do as follows:

var result = schedules
    // First you join the three tables
    .Join(dates, s => s.DateId, d => d.DateId, (s, d) => new {s, d})
    .Join(tasks, s => s.s.TaskId, t => t.TaskId, (sd, t ) => new { Person = sd.s, Date = sd.d, Task = t })
    // Then you Group by the person name
    .GroupBy(j => j.Person.Name)
    // Finally you compose the final object extracting from the list of task the correct task for the current day
    .Select(group => new
    {
        Person = group.Key,
        Monday = group.Where(g => g.Date.DateId == 1).Select(g => g.Task.Desc).FirstOrDefault(),
        Tuesday = group.Where(g => g.Date.DateId == 2).Select(g => g.Task.Desc).FirstOrDefault(),
        Wednesday = group.Where(g => g.Date.DateId == 3).Select(g => g.Task.Desc).FirstOrDefault(),
        Thursday = group.Where(g => g.Date.DateId == 4).Select(g => g.Task.Desc).FirstOrDefault(),
        Friday = group.Where(g => g.Date.DateId == 5).Select(g => g.Task.Desc).FirstOrDefault()
    })
    .ToList();

If you want to select only some days, you can return an object containing a dictionary instead of an object with a property per day.

The dictionary will contain key-value pairs with the key representing the day and the value representing the task.

See the following code:

    var filter = new[] {2, 3};

    var filteredResult = schedules
        .Join(dates, s => s.DateId, d => d.DateId, (s, d) => new{ s, d})
        .Join(tasks, s => s.s.TaskId, t => t.TaskId, (sd, t) => new { Person = sd.s, Date = sd.d, Task = t })
        .Where(x => filter.Contains(x.Date.DateId))
        .GroupBy(x => x.Person.Name)
        .Select(group => new 
                {
                    Person = group.Key, 
                    TasksByDay = group.ToDictionary(o => o.Date.Desc, o => o.Task.Desc)
                })
        .ToList();


    foreach (var item in filteredResult)
    {
        System.Console.WriteLine(item.Person);
        foreach (var keyvaluepair in item.TasksByDay)
        {
            System.Console.WriteLine(keyvaluepair.Key + " - " + keyvaluepair.Value);
        }
        System.Console.WriteLine("---");
    }

Is there a way to select columns dynamically? For example, If I only want Monday and Friday for now, later I only want to see Wednesday. Am I able to pass in a variable to filter the columns I want. I would like to just select the columns I wanted. — sylanter, Sep 27 '16 at 01:46
@sylanter based on your comment I updated my answer, please have a look — user449689, Sep 27 '16 at 06:38

C# linq query from sql server into one table

2 Answers2