Hello,

I am trying to create an outer join in Linq using DefaultIfEmpty. It does not seem to be working. Basically I want c.TotalAmount to be 0 if it does not exist in the second/right table. Any ideas as to what I might be doing wrong? The query does execute, but it seems to still work as an innner join. I do not get any results where the join fails. Thanks very much.

        var rtaloBySystem = (from r in RTLO
                               from c in contractCoverageBySystem.DefaultIfEmpty()
                               where r.System_ID == c.System_ID && r.Month == c.Month && r.Year == c.Year
                               select new Allocator() {Month = r.Month, Year = r.Year, System_ID = r.System_ID, Amount = r.Amount - (c.TotalAmount == null ? 0 : c.TotalAmount)}).ToArray();

Ok.. cancel that. I did some digging around the internet and this clicked. This is how I did it if anyone cares...

        var rtaloBySystem = (from r in RTLO
                             join c in contractCoverageBySystem
                             on new { System_ID = r.System_ID, Month = r.Month, Year = r.Year } equals new { System_ID = c.System_ID, Month = c.Month, Year = c.Year } into dataJoined
                             from c in dataJoined.DefaultIfEmpty(new Allocator { Month = 0, Year = 0, System_ID = 0, Amount = 0, TotalAmount = 0 })
                             select new Allocator() { Month = r.Month, Year = r.Year, System_ID = r.System_ID, Amount = r.Amount - c.TotalAmount, TotalAmount = 0 }).ToArray();
This article has been dead for over six months. Start a new discussion instead.