Ich habe 3 Tabellen:testpackage, testpackageReport, testpackagereportdetail mit dieser Struktur:

Mit dieser Abfrage

SELECT        
    dbo.TestPackages.PackageNumber, dbo.TestPackages.Size, 
    dbo.TestPackages.Code, dbo.TestPackageReports.ReportNumber,   
    dbo.TestPackageReportDetails.Step, dbo.TestPackageReportDetails.Status, 
    dbo.TestPackageReports.SubmitDateTime, 
    dbo.TestPackageReportDetails.Id AS ReportDetailId
FROM
    dbo.TestPackages 
INNER JOIN
    dbo.TestPackageReportDetails ON dbo.TestPackages.Id = dbo.TestPackageReportDetails.TestPackageId 
INNER JOIN
    dbo.TestPackageReports ON dbo.TestPackageReportDetails.TestPackageReportId = dbo.TestPackageReports.Id

Das Ergebnis ist das:

PackageNumber   Size    Code    ReportNumber    Step        Status  SubmitDateTime  ReportDetailId
1000220-G-02-TR 1.31    143     LC-0431         LineCheck   Reject  2010-12-12     218
1000220-G-02-TR 1.31    143     LC-0131         LineCheck   Accept  2011-12-12     220
1000220-G-02-TR 1.31    143     PT-0248         Test        Accept  2012-12-12     513
1000220-G-02-TR 1.31    143     DR-0202         Drying      Accept  2013-12-12     625
1000220-G-02-TR 1.31    143     AFP-AG-FL-0030  Flushing    Accept  2015-12-12     745

Aber ich muss diese Daten nur in einer Zeile wie folgt anzeigen:

 PackageNumber   Size   Code    LineCheckReportNumber   LineCheckStep       LineCheckStatus linecheckSubmitDateTime ReportDetailId      tesReportNumber testCheckStep       testStatus  testSubmitDateTime  ReportDetailId

1000220-G-02-TR 1.31   143             LC-0431          LineCheck                Accept       2011-12-12              220                      PT-0248        Test               Accept       2012-12-12              513

Für verrauschte Daten im erwarteten Ergebnis entferne ich trocknende und spülende Säulen. Wie Sie sehen, müssen alle Datensätze in einer Zeile angezeigt werden. Ein weiterer wichtiger Punkt sind die Daten mit dem Maximum ReportDetailId das istaccepted weil jedestestpackage kann mehrere @ hablinecheck odertest oderflsuhing oderdrying reports

Beispieldaten

TestpackageTable

TestpackageReport

Testpackagereportdetail

Ich schreibe die Abfrage mit dem Entity Framework, wie Sie sehen können, aber es ist sehr langsam:

from i in _ctx.TestPackages
join testpackreportdet in _ctx.TestPackageReportDetails on i.Id equals
    testpackreportdet.TestPackageId
join testPackageRepo in _ctx.TestPackageReports on testpackreportdet.TestPackageReportId equals testPackageRepo.Id into g1
from y1 in g1.DefaultIfEmpty()
group new { y1, testpackreportdet } by new { i }
into grouping
let MaxLinecheck = grouping.Select(item => item.testpackreportdet)
    .Where(item => item != null && item.Step == "LineCheck")
    .OrderByDescending(item => item.Id)

let MaxClean = grouping.Select(item => item.testpackreportdet)
    .Where(item => item != null && item.Step == "Clean")
    .OrderByDescending(item => item.Id)

let MaxTest = grouping.Select(item => item.testpackreportdet)
    .Where(item => item != null && item.Step == "Test")
    .OrderByDescending(item => item.Id)

let MaxFlush = grouping.Select(item => item.testpackreportdet)
    .Where(item => item != null && item.Step == "Flushing")
    .OrderByDescending(item => item.Id)

let MaxDrying = grouping.Select(item => item.testpackreportdet)
    .Where(item => item != null && item.Step == "Drying")
    .OrderByDescending(item => item.Id)

let MaxReins = grouping.Select(item => item.testpackreportdet)
    .Where(item => item != null && item.Step == "Reinstatment")
    .OrderByDescending(item => item.Id)

let MaxMono = grouping.Select(item => item.testpackreportdet)
 .Where(item => item != null && item.Step == "Mono")
 .OrderByDescending(item => item.Id)

let MaxPAD = grouping.Select(item => item.testpackreportdet)
 .Where(item => item != null && item.Step == "PADTest")
 .OrderByDescending(item => item.Id)

let MaxVariation = grouping.Select(item => item.testpackreportdet)
 .Where(item => item != null && item.Step == "Variation")
 .OrderByDescending(item => item.Id)
    select new ViewDomainClass.TechnicalOffice.ViewTestPackageState()
    {
        Id = grouping.Key.i.Id,
        PackageNumber = grouping.Key.i.PackageNumber,
        Size = grouping.Key.i.Size.ToString(),
        Code = grouping.Key.i.Code,
        TestPackageOrder = grouping.Key.i.TestPackageOrder,
        LineCheckState = MaxLinecheck.FirstOrDefault().Status,
        LineCheckSubmitDateTime =
            grouping.Where(
                i => i.y1.Id == MaxLinecheck.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.SubmitDateTime.ToString(),
        LineCheckReportNumber =
            grouping.Where(
                i => i.y1.Id == MaxLinecheck.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.ReportNumber,
        CleaningState = MaxClean.FirstOrDefault().Status,
        CleanSubmitDateTime =
            grouping.Where(i => i.y1.Id == MaxClean.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.SubmitDateTime.ToString(),
        CleanReportNumber =
            grouping.Where(i => i.y1.Id == MaxClean.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.ReportNumber,
        TestState = MaxTest.FirstOrDefault().Status,
        TestSubmitDateTime =
            grouping.Where(i => i.y1.Id == MaxTest.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.SubmitDateTime.ToString(),
        TestReportNumber =
            grouping.Where(i => i.y1.Id == MaxTest.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.ReportNumber,

        Drying = MaxDrying.FirstOrDefault().Status,
        DryingSubmitDateTime =
            grouping.Where(i => i.y1.Id == MaxDrying.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.SubmitDateTime.ToString(),
        DryingReportNumber =
            grouping.Where(i => i.y1.Id == MaxDrying.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.ReportNumber,
        Flushing = MaxFlush.FirstOrDefault().Status,
        FlushingSubmitDateTime =
            grouping.Where(i => i.y1.Id == MaxFlush.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.SubmitDateTime.ToString(),
        FlushingReportNumber =
            grouping.Where(i => i.y1.Id == MaxFlush.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.ReportNumber,
        ReInstatement = MaxReins.FirstOrDefault().Status,
        ReInstatementSubmitDateTime =
            grouping.Where(i => i.y1.Id == MaxReins.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.SubmitDateTime.ToString(),
        ReInstatementReportNumber =
            grouping.Where(i => i.y1.Id == MaxReins.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.ReportNumber,
        Mono = MaxMono.FirstOrDefault().Status,
        MonoSubmitDateTime =
            grouping.Where(i => i.y1.Id == MaxMono.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.SubmitDateTime.ToString(),
        MonoReportNumber =
            grouping.Where(i => i.y1.Id == MaxMono.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.ReportNumber,
        Variation = MaxVariation.FirstOrDefault().Status,
        VariationSubmitDateTime =
            grouping.Where(i => i.y1.Id == MaxVariation.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.SubmitDateTime.ToString(),
        VariationReportNumber =
            grouping.Where(i => i.y1.Id == MaxVariation.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.ReportNumber,
        PAD = MaxPAD.FirstOrDefault().Status,
        PADSubmitDateTime = 
            grouping.Where(i => i.y1.Id == MaxPAD.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.SubmitDateTime.ToString(),
        PADReportNumber = 
            grouping.Where(i => i.y1.Id == MaxPAD.FirstOrDefault().TestPackageReportId)
                .FirstOrDefault()
                .y1.ReportNumber
}).ToList();