how to left join two inner joined tables?

Question

how can i convert this plain sql to linq to sql?

select m.Id, m.Name, m.RedLight, m.OrangeLight, m.GreenLight, count(pos.HealthModuleId) as NumberOfBookingsPerDate
from tdHealthModules m
    left join (
        HealthEventPos pos
        join tdHealthEvents e on pos.HealthEventId = e.Id
    ) on m.Id = pos.HealthModuleId and convert(date,e.Starttime) = CONVERT(DATE,'20180410')
group by m.Id, m.Name, m.RedLight, m.OrangeLight, m.GreenLight
having count(pos.HealthModuleId) < m.RedLight

i have actually following, which delivers not the same result

var m = (from modules in _appContext.HealthModule
                 join pos in _appContext.HealthEventPos on modules.Id equals pos.HealthModuleId into posGroup
                 from posItem in posGroup.DefaultIfEmpty(new HealthEventPos { Id = 0, HealthEventId = 0, HealthModuleId = 0 })
                 join e in _appContext.HealthEvent on posItem.HealthEventId equals e.Id into eventGroup
                 from eItem in eventGroup.DefaultIfEmpty(new HealthEvent { Id = 0, StartTime = eventDate })
                 where eItem.StartTime.Date.Equals(eventDate)
                 group modules by new { modules.Id, modules.Name, modules.RedLight, modules.OrangeLight, modules.GreenLight, count = eventGroup.Count() } into g
                 where g.Count() < g.Key.RedLight
                 select new HealthModule
                 {
                     Id = g.Key.Id,
                     Name = g.Key.Name,
                     RedLight = g.Key.RedLight,
                     OrangeLight = g.Key.OrangeLight,
                     GreenLight = g.Key.GreenLight,
                     NumberOfBookingsPerDate = g.Key.count
                 });

how can i write a left join to two inner joined tables?

See my [SQL Conversion Recipe](https://stackoverflow.com/a/49245786/2557128) — NetMage, Apr 05 '18 at 22:40

score 0 · Answer 1 · answered Apr 10 '18 at 09:37

finally i came to following solution for the issue with the left outer join on two inner joined tables

var ep = from pos in _appContext.HealthEventPos
                 join e in _appContext.HealthEvent on pos.HealthEventId equals e.Id
                 where e.StartTime.Date.Equals(eventDate)
                 select pos;

a extra variable, which contains the joined table, which will be then used for the left join

var m = (from modules in _appContext.HealthModule
                 join ePos in ep on modules.Id equals ePos.HealthModuleId into ePosGroup
                 from ePos in ePosGroup.DefaultIfEmpty(defaultPos)
                 group new { modules, ePos } by new { modules.Id, modules.Name, modules.Owner, modules.RedLight, modules.OrangeLight, modules.GreenLight } into g
                 where g.Count(t => t.ePos.HealthModuleId != 0) < g.Key.RedLight
                 select new HealthModule
                 {
                     Id = g.Key.Id,
                     Name = g.Key.Name,
                     Owner = g.Key.Owner,
                     RedLight = g.Key.RedLight,
                     OrangeLight = g.Key.OrangeLight,
                     GreenLight = g.Key.GreenLight,
                     NumberOfBookingsPerDate = g.Count(t => t.ePos.HealthModuleId != 0)
                 });

how to left join two inner joined tables?

1 Answers1