Inner joins in LINQ

technieken

Om een view op product releases te kunnen genereren, heb ik een inner-join nodig.

Deze werkt in LINQ anders dan in SQL:

Het model:

Compositie ProductRelease

De originele query:

queryResults = (from pr in db.ProductReleases
                join prsr in db.ProductReleasesInSystemReleases on pr.Id equals prsr.ProductRelease.Id
                join prd in db.Products on pr.Product.Id equals prd.Id
                join swr in db.SoftwareReleases on pr.SoftwareRelease.Id equals swr.Id
                join hwr in db.HardwareReleases on pr.HardwareRelease.Id equals hwr.Id
                select new ProductReleaseView
                {
                    Id = pr.Id,
                    ReleaseDate = pr.ReleaseDate,
                    ReleaseDescription = pr.ReleaseDescription,
                    ReleaseName = pr.ReleaseName,
                    ReleaseStatus = pr.ReleaseStatus,

                    Product = prd,
                    SoftwareRelease = swr,
                    HardwareRelease = hwr
                });

Het probleem bij deze query is, dat een ProductRelease zonder HardwareRelease helemaal geen resultaten geeft. Dit terwijl er best een ProductRelease kan bestaan met alleen een SoftwareRelease.

De oplossing:

queryResults = (from pr in db.ProductReleases
                from prsr in db.ProductReleasesInSystemReleases.Where(prsr => prsr.ProductRelease.Id == pr.Id).DefaultIfEmpty()
                from prd in db.Products.Where(prd => prd.Id == pr.Product.Id).DefaultIfEmpty()
                from swr in db.SoftwareReleases.Where(swr => swr.Id == pr.SoftwareRelease.Id).DefaultIfEmpty()
                from hwr in db.HardwareReleases.Where(hwr => hwr.Id == pr.HardwareRelease.Id).DefaultIfEmpty()
                select new ProductReleaseView
                {
                    Id = pr.Id,
                    ReleaseDate = pr.ReleaseDate,
                    ReleaseDescription = pr.ReleaseDescription,
                    ReleaseName = pr.ReleaseName,
                    ReleaseStatus = pr.ReleaseStatus,

                    Product = prd,
                    SoftwareRelease = swr,
                    HardwareRelease = hwr
                });

vrijdag 13 maart 2015