Tengo 3 mesas:testpackage, testpackageReport, testpackagereportdetail con esta estructura:

Con esta consulta

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

El resultado es este:

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

Pero necesito mostrar estos datos en una fila como esta:

 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

Para obtener datos ruidosos en el resultado esperado, elimino las columnas de secado y lavado. Como puede ver, necesito que todos estos registros se muestren como una fila, otra que es importante son los datos con el máximoReportDetailId es deciraccepted porque cadatestpackage puede tener múltipleslinecheck otest oflsuhing odrying informes

Data de muestra:

TestpackageTable

TestpackageReport

Testpackagereportdetail

Escribo la consulta usando el marco de la entidad como puede ver, pero es muy lento:

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();