Linq nesting relationships and DTOs

Question

I'm getting data from an Oracle database and creating an API for internal use. Some of the classes are (shortened for brevity):

CustomerOrderTab

    Public partial class CustomerOrderTab
    Public string OrderNo { get; set; }
    [ForeignKey("CustomerMV")]
    public string CustomerNo { get; set; }
    public List<CustomerOrderLineTab> CustomerOrderLines { get; set; }
    public List<CustomerOrderChargeTab> CustomerOrderCharge { get; set; }
    public List<ShipmentOrderLineTab> ShipmentOrderLines { get; set; }
    public CustomerMV CustomerMV { get; set; }

CustomerOrderDTO

    Public class OrderDTO
    public string OrderNo { get; set; }
    public string CustomerName { get; set; }
    public List<OrderLinesDTO> OrderLines { get; set; }
    public List<OrderChargesDTO> OrderCharges { get; set; }
    public List<ShipmentOrderLinesDTO> ShipmentOrderLines { get; set; }

ShipmentOrderLineTab

    public partial class ShipmentOrderLineTab
    public decimal ShipmentId { get; set; }
    [ForeignKey("CustomerOrderTab")]
    public string OrderNo { get; set; }
    public ShipmentTab Shipment { get; set; }

ShipmentOrderLineDTO

    public class ShipmentOrderLinesDTO
    public decimal ShipmentId { get; set; }
    public string OrderNo { get; set; }
    public ShipmentTab MyShipment { get; set; }

ShipmentTab

    public partial class ShipmentTab
    [ForeignKey("ShipmentOrderLineTab")]
    public decimal ShipmentId { get; set; }
    public string ShipperAddress1 { get; set; }
    public string ShipperAddress2 { get; set; }
    public List<ShipmentHandlingUnitTab> ShipmentHandlingUnits { get; set; }

ShipmentDTO

    public class ShipmentDTO
    public decimal ShipmentId { get; set; }
    public string ShipViaCode { get; set; }
    public string ShipmentPayer { get; set; }
    public List<ShipmentHandlingUnitDTO> ShipmentHandlingUnitDTOs { get; set; }

This Works:

    var orderLines = from o in _context.CustomerOrderTab
                         .Where(o => o.OrderNo == orderno)
                         select new OrderDTO()
                         {
                             OrderNo = o.OrderNo,
                             CustomerName = o.CustomerMV.CustomerName,

    This is a child of the order     ShipmentOrderLines = o.ShipmentOrderLines
    This works          .Select(ob => new ShipmentOrderLinesDTO
                             {
                                 ShipmentId = ob.ShipmentId,
                                 OrderNo = ob.OrderNo,
                                 MyShipment = ob.Shipment
    Adding this does not work         .Select(y => new ShipmentDTO
    The error says that ShipmentTab
     Doesn’t have a definition for Select    {
                                        }
                             }).ToList()

Shipment is a child of ShipmentOrderLine (there will only be one shipment per line)

ShipmentOrderLine is a child of CustomerOrder (there can be many lines per order)

I think the problem is that there is only one shipment per line but I've tried lots of things and can't get it to map to my DTO.

Answer 1

Fix your model:

ShipmentOrderLineDTO

public class ShipmentOrderLinesDTO
public decimal ShipmentId { get; set; }
public string OrderNo { get; set; }
public ShipmentDTO MyShipment { get; set; }

Select is only used to project a collection. For non-collections, you just create a new object...

var orderLines = _context.CustomerOrderTab
  .Where(o => o.OrderNo == orderno)
  .Select(o => new OrderDTO {
    OrderNo = o.OrderNo,
    CustomerName = o.CustomerMV.CustomerName,
    ShipmentOrderLines = o.ShipmentOrderLines
      .Select(ob => new ShipmentOrderLinesDTO {
        ShipmentId = ob.ShipmentId,
        OrderNo = ob.OrderNo,
        MyShipment = new ShipmentDTO {
          ShipmentId = ob.Shipment.ShipmentId,
          ...
          ShipmentHandlingUnits = ob.Shipment.ShipmentHandlingUnits
            .Select(shu=> new ShipmentHandlingUnitDTO {
              ...
            }).ToList()
        }
      }
    }).ToList()

Now with that all said, I typically have mapping extension methods on my data model classes, which allow things like this:

var orderLines = _context.CustomerOrderTab
  .Where(o=>o.OrderNo == orderno)
  .Select(o=> o.ToDto());

Example class:

public static class CustomerOrderTabExtensions {
  public static CustomerOrderDto ToDto(this CustomerOrderTab cot) {
    return new CustomerOrderDto {
      OrderNo = cot.OrderNo,
      ...
      OrderLines = cot.CustomerOrderLines
        .Select(col=>col.ToDto())
        .ToList()
    }
  }
}

Or use Automapper.