Joining 6 tables together with inner joins & a left outer join - LINQ

Question

I have 6 tables. Like this:

Here is the SQL code that I'm trying to re-code within LINQ.

SELECT        dbo.TimeTable.Day, dbo.TimeTable.StartTime, dbo.TimeTable.Duration, dbo.Module.ModuleRef, dbo.Module.ModuleName, dbo.Course.CourseRef, dbo.Room.RoomRef, dbo.Room.RoomName, 
                         dbo.Room.RoomFloor, dbo.Room.RoomNumber, dbo.Building.BuildingRef, dbo.Building.BuildingName
FROM            dbo.Room INNER JOIN
                         dbo.TimeTable INNER JOIN
                         dbo.Module ON dbo.TimeTable.ModuleId = dbo.Module.Id ON dbo.Room.Id = dbo.TimeTable.RoomId INNER JOIN
                         dbo.Building ON dbo.Room.BuildingId = dbo.Building.Id LEFT OUTER JOIN
                         dbo.Course INNER JOIN
                         dbo.CourseModule ON dbo.Course.Id = dbo.CourseModule.CourseId ON dbo.Module.Id = dbo.CourseModule.ModuleId

If anyone could point me in the right direction of converting this to a LINQ statement? I am new to this concept of linq statements. Thanks for any help!

There will be a separate tab for getting the generated LINQ query adjacent to result window — Rahul, Oct 31 '17 at 10:23
@Rahul that tab is just blank when I click on it and try running it again. doesn't show any results — Liam, Oct 31 '17 at 10:29
Hmm ... not sure but you might want to check this post https://stackoverflow.com/questions/12238423/linqpad-convert-sql-to-linq-command — Rahul, Oct 31 '17 at 10:42
LinqPad does not contains any SQL->LINQ translators. LinqPad relies on the .Net Linq-to-Sql library or Entity framework for LINQ->SQL translation — user2321864, Oct 31 '17 at 11:01

TheRock · Answer 1 · 2017-10-31T16:07:51.853

Try and not get overwhelmed with the fact that you have 6 tables and inner and left joins. Try to learn the concepts of joining 2 collections (Inner and Left) and then its just a matter of chaining linq together. Now the code can look a bit complicated but it really isn't.

Given the following in memory objects:

        var rooms = new List<Room>();
        var timeTables = new List<TimeTable>();
        var modules = new List<Module>();
        var buildings = new List<Building>();
        var courses = new List<Course>();
        var courseModules = new List<CourseModule>();

Your linq query might look like the following:

           var result = rooms
            .Join(timeTables,
                room => room.Id,
                table => table.RoomId,
                (room, table) => new {room, table})
            .Join(modules,
                arg => arg.table.ModuleId,
                module => module.Id,
                (room_table, module) => new {room_table, module})
            .Join(buildings,
                arg => arg.room_table.room.BuildingId,
                building => building.Id,
                (room_table_module, building) => new {room_table_module, building})
            .GroupJoin(courseModules,
                arg => arg.room_table_module.module.Id,
                coursemodule => coursemodule.ModuleId,
                (room_table_module_building, coursemodules) => new { room_table_module_building, coursemodules})
            .SelectMany(arg => arg.coursemodules.DefaultIfEmpty(),
                (arg, coursemodule) => new { arg.room_table_module_building, coursemodule })
            .Join(courses,
                arg => arg.coursemodule.CourseId,
                course => course.Id,
                (room_table_module_building_coursemodule, course) => new { room_table_module_building_coursemodule, course });

The great part of LinqPad is that you have direct access to the db objects and can play around with your linq queries and see the generated sql. You can then take the sql and ensure that the execution plan looks good and can add any indexes that will optimize your queries.

Joining 6 tables together with inner joins & a left outer join - LINQ

1 Answers1