Linq left outer join and a union returning to many records

Question

I'm converting a stored procedure that works well into a linq query but am not getting the correct records. The stored procedure looks like the following:

SELECT isnull(a.CertificationId, cc.CertificationId) as CertificationId, 
    isnull(a.cipcode, cc.CipCode) as CipCode,isnull(a.Credential, lc.Credential) as Credential, 
    lc.LicensingCertificationProgram, lc.IssuingOrganization, isnull(a.ab, '') as ab
FROM     enrCertificationCipCodes CC INNER JOIN
     lkpCertifications lc ON CC.CertificationId = lc.CertificationId 
     left join
        (SELECT pc.CertificationId, 
        cc.CipCode, 
        lc.Credential, 
        lc.LicensingCertificationProgram, 
        lc.IssuingOrganization, ab = 'Yes',
        pc.psn
            FROM     enrCertificationCipCodes CC INNER JOIN
            lkpCertifications lc ON CC.CertificationId = lc.CertificationId
            inner join (select * from enrProgramCertifications where PSN = @PSN) PC on cc.CertificationId = pc.CertificationId ) as a on a.CertificationId = cc.CertificationId 
where cc.CipCode = @CipCode 
union
select pc.CertificationId as CertificationId, p.cipcode as CipCode, other as Credential,'' as LicensingCertificationProgram, 
   '' as IssuingOrganization, 'Yes' as ab
 from (select * from enrProgramCertifications where CertificationId  = '99999') pc join enrProgram p on pc.PSN = p.PSN 
  where p.CIPCode = @CipCode and p.PSN = @PSN
  Union
select '99999' as CertificationId, @CipCode as CipCode, 'Other' as Credential,'' as LicensingCertificationProgram, '' as IssuingOrganization, '' as ab
order by ab desc, Credential

The linq query I have is

var t = ((from a in LkpCertifications 
        join d in EnrProgramCertifications on a.CertificationId equals d.CertificationId into ad
                 from d in ad.DefaultIfEmpty()
                 join c in EnrCertificationCipCodes on a.CertificationId equals c.CertificationId
                 where c.CipCode == "52.1999"
                 select new {
                 CertificationId = a.CertificationId,
                 CipCode = c.CipCode,
                 Credential = a.Credential,
                 Licensing = a.LicensingCertificationProgram,
                 IssuingOrganization = a.IssuingOrganization,
                 Psn = d.PSN != null ? d.PSN : 0,
                 ab = d.PSN != null ? "Yes" : ""
                 })
                 .Union (from k in EnrProgramCertifications
                 join l in LkpCertifications on k.CertificationId equals l.CertificationId where k.PSN == 19480
                 select new{
                 CertificationId = k.CertificationId,
                 CipCode = "52.1999",
                 Credential = k.Other,
                 Licensing = l.LicensingCertificationProgram,
                 IssuingOrganization = l.IssuingOrganization,
                 Psn = k.PSN != null ? k.PSN : 0,
                 ab = k.PSN != null ? "Yes" : ""
                 })).ToList();

t.Dump(); 

The result is that I am getting more rows from the left joined table. If I try to limit the rows from the left joined table by qualifying it with the PSN number, then I don't get the results from the lkpCertifications table. The lkpCertifications holds all the certifications and and the enrProgramCertifications hold only those that have been selected much like an orders and orderdetail set up.

So my current results looks like

I should be getting only a single item 54 with a 0 PSN and a single 55 record with a PSN of 19480.

Any ideas of the issue?

Answer 1

I was able to resolve this issue by using a couple joins into objects that defaultifempty() to return the left outer joins.

                certs = (from a in _context.LkpCertifications
                     join d in _context.EnrProgramCertifications on a.CertificationId equals d.CertificationId into ad
                     from d in ad.DefaultIfEmpty()
                     join c in _context.EnrCertificationCipCodes on a.CertificationId equals c.CertificationId into ac
                     from c in ac.DefaultIfEmpty()
                     where c.CipCode == CIP || d.Psn == Psn
                     select new vmManageCertifications
                         {
                         Psn = d.Psn == Psn ? d.Psn : 0,
                         Cipcode = c.CipCode == null ? "0" : c.CipCode,
                         CertificationId = a.CertificationId,
                         Credential = a.Credential == "Other" ? d.Other : a.Credential,
                         LicensingCertificationProgram = a.LicensingCertificationProgram,
                         IssuingOrganization = a.IssuingOrganization,
                         ProgramCertified = d.Psn == Psn ? true : false
                         }).ToList().GroupBy(i => i.CertificationId).Select(group => group.First()).OrderByDescending(i => i.ProgramCertified);