Project Linq Query Result onto complex object

Question

I am trying to project the result of an EF query onto a complex object that includes a child collection. Currently I am doing this in two steps--first query, then loop and create the projection. I'd like to know if it's possible to do it in one step, but can't figure out the syntax for creating the child collection instances.

Current code:

var ctx = uow.Context;   

var invoices = from i in ctx.Invoices.Include(inv => inv.BatchInvoices.Select(imp => imp.ImportBatch))
                join bi in ctx.BatchInvoices on i.Id equals bi.InvoiceId
                join ib in ctx.ImportBatches on bi.ImportBatchId equals ib.Id
                select i;

var invoiceDTOs = new List<InvoiceDTO>();

foreach (Invoice invoice in invoices)
{
    var invoiceDTO = new InvoiceDTO
        {
            DocumentDate = invoice.DocumentDate,
            DocumentNumber = invoice.DocumentNumber,
            DueDate = invoice.DueDate,
            InvoiceId = invoice.Id
        };


    foreach (BatchInvoice batchInvoice in invoice.BatchInvoices)
    {
        var batchInvoiceDTO = new InvoiceDetailDTO
            {
                Current = batchInvoice.Current,
                Days1To30 = batchInvoice.Days1To30,
                Days31To60 = batchInvoice.Days31To60,
                Days61To90 = batchInvoice.Days61To90,
                DaysOver90 = batchInvoice.DaysOver90,
                RunDate = batchInvoice.ImportBatch.FileGeneratedDate
            };
        invoiceDTO.InvoiceDetails.Add(batchInvoiceDTO);
    }
    invoiceDTOs.Add(invoiceDTO);
}

Here is where I get stuck:

var invoices2 = from i in ctx.Invoices.Include(inv => inv.BatchInvoices.Select(imp => imp.ImportBatch))
            join bi in ctx.BatchInvoices on i.Id equals bi.InvoiceId
            join ib in ctx.ImportBatches on bi.ImportBatchId equals ib.Id
            select new InvoiceDTO
                {
                    DocumentDate = i.DocumentDate,
                    DocumentNumber = i.DocumentNumber,
                    DueDate = i.DueDate,
                    InvoiceId = i.Id,
                    InvoiceDetails = ????
                };

Is it possible to do this in one step?

Answer 1

Sure, you can have Linq queries inside Linq queries. Try this:

var invoices2 = 
    from i in ctx.Invoices.Include(inv => inv.BatchInvoices.Select(imp => imp.ImportBatch))
    join bi in ctx.BatchInvoices on i.Id equals bi.InvoiceId
    join ib in ctx.ImportBatches on bi.ImportBatchId equals ib.Id
    select new InvoiceDTO
    {
        DocumentDate = i.DocumentDate,
        DocumentNumber = i.DocumentNumber,
        DueDate = i.DueDate,
        InvoiceId = i.Id,
        InvoiceDetails = 
             from b in i.BatchInvoices
             select new InvoiceDetailDTO
             {
                 Current = b.Current,
                 Days1To30 = b.Days1To30,
                 Days31To60 = b.Days31To60,
                 Days61To90 = b.Days61To90,
                 DaysOver90 = b.DaysOver90,
                 RunDate = b.ImportBatch.FileGeneratedDate
             }
    };

If you really need InvoiceDetails to be a List<T>, you can probably just wrap that part with a .ToList().