Entity Framework Navigation Properties are not listed while creating a converter

Question

I have following working Entity Frameowrk code for creating DTO from entity framework result. It created InvoiceDTO object that has a List<ServiceChargeDTO> property inside it. This is done referring to Mapping Linq Query results to a DTO class

public class InvoiceDTO
{
    public int InvoiceID { get; set; }
    public List<ServiceChargeDTO> ServiceChargeLineItems { get; set; }
    public decimal InvoiceTotal { get; set; }
} 

public InvoiceDTO GetInvoiceByID(int invoiceIDParam)
{
    InvoiceDTO invoice=null;
    using (var db = new PortalEntities())
    {
        var invoices = from a in db.Invoices
                       where a.InvoiceID == invoiceIDParam
                       select new InvoiceDTO
                       {
                           InvoiceID = a.InvoiceID,
                           InvoiceTotal = a.InvoiceAmount,
                           ServiceChargeLineItems =
                                (from b in db.InvoiceServiceXrefs 
                                where a.InvoiceID == b.InvoiceID 
                                select new ServiceChargeDTO
                                {
                                    ServiceChargeID = b.ServiceChargeID,
                                    Quantity = b.ServiceCharge.Qty,
                                    UnitPrice=b.ServiceCharge.UnitPrice,
                                    Amount=b.ServiceCharge.Amount
                                }
                                ).ToList()
                       };

        invoice = invoices.FirstOrDefault();
    }

    return invoice;
}

Here data from related tables is successfully retrieved.

InvoiceServiceXrefs.ServiceCharge.Qty

I need to change this approach and make it convert method.

public InvoiceDTO GetInvoiceByID(int invoiceIDParam)
{
    var invoice2 = null;
    using (var db = new PortalEntities())
    {
        var invoices2 = from b in db.Invoices
                        where b.InvoiceID == invoiceIDParam
                        select b;
        invoice2 = ToInvoiceDTO(invoices2.FirstOrDefault());
     }

    return invoice2;
}

But the navigation properties are not getting listed when I type invoice.InvoiceServiceXrefs..

How to get the navigation properties correctly and create InvoiceDTO object with List<ServiceChargeDTO>?

`invoice.InvoiceServiceXrefs` is a collection. Similar to the first code snippet (`from b in db.InvoiceServiceXrefs`), you have to iterate over it to use the entities it contains. Note however that this `ToInvoiceDTO` approach is less efficient: it will trigger multiple queries. — Gert Arnold, Apr 06 '17 at 07:05
the `invoice` argument is of type `Invoice`, not `InvoiceDTO` and you do not show any code for a class called `Invoice` — Eris, Apr 16 '17 at 19:24
@Lijo Have any of the answers helped to answer your question? Can you update please. — Seany84, Apr 17 '17 at 09:52
Frankly, I don't understand the bounty. If you still had questions after my comment above you could have simply asked. Now I don't know what's left unclear to you. — Gert Arnold, Apr 18 '17 at 14:27
You need to interate through your InvoiceServiceXrefs. Bu before that you also need to eager load it before FirstOrDefault() call cause it wont bring you InvoiceServiceXrefs — Chih-Ho Andy Chou, Apr 19 '17 at 18:19

score 3 · Answer 1 · answered Apr 12 '17 at 23:39

It looks like you need to map your List<InvoiceServiceXrefs> to List<ServiceChargeDTO>.

private static InvoiceDTO ToInvoiceDTO(Invoice invoice)
{
    InvoiceDTO e = new InvoiceDTO()
    {
        InvoiceID = invoice.InvoiceID,
        InvoiceTotal = invoice.TotalAmount
    };

    foreach(var invRef in invoice.InvoiceServiceXrefs)
    {
        e.ServiceChargeLineItems.Add(new ServiceChargeDTO
        {
            ServiceChargeID = invRef.ServiceChargeID,
            Quantity = invRef.Qty,
            UnitPrice = invRef.UnitPrice,
            Amount = invRef.Amount
        });
    }

    return e;
}

score 3 · Accepted Answer · edited Apr 21 '17 at 15:41

You can use pretty much the same code to map your ServiceChargeLineItems, that you used in your initial query

private static InvoiceDTO ToInvoiceDTO(Invoice invoice)
{
    InvoiceDTO e = new InvoiceDTO()
    {
        InvoiceID = invoice.InvoiceID,
        InvoiceTotal = invoice.TotalAmount
    };

    e.ServiceChargeLineItems = invoice.InvoiceServiceXrefs.
                               Select(b => new ServiceChargeDTO
                               {
                                   ServiceChargeID = b.ServiceChargeID,
                                   Quantity = b.ServiceCharge.Qty,
                                   UnitPrice = b.ServiceCharge.UnitPrice,
                                   Amount = b.ServiceCharge.Amount
                               }).ToList()

    return e;
}

UPDATE:

Following is how it is called. Note the use of ToList() on invoicesQuery. This approach issues multiple queries.

var invoicesQuery = from a in db.Invoices
             where a.InvoiceID == invoiceIDParam
            select a;

List<InvoiceDTO>  listTest = invoicesQuery.ToList().Select(x => ToInvoiceDTO(x)).ToList();

score -2 · Answer 3 · edited Apr 16 '17 at 17:19

When you ran LINQ query on db.Invoices in your GetInvoiceByID method, it became an IQuerable variable invoices2. If you do not want to do perform nested LINQ query in one shot, better return them as DbSet invoices2 and use it to create your desired DTO like this

public DbSet"<"Invoices">" GetInvoiceByID(int invoiceIDParam)
{
     var invoice2 = null;
     using (var db = new PortalEntities())
     {
          var invoices2 = from b in db.Invoices
                          where b.InvoiceID == invoiceIDParam
                          select b;
          //REMOVE THIS invoice2 = ToInvoiceDTO(invoices2.FirstOrDefault());
      }

      return invoice2;
}

Entity Framework Navigation Properties are not listed while creating a converter

3 Answers3