none
Retrieving a list of lists in a single query RRS feed

  • Question

  • Imagine a "standard" customer/order relationship in a database. I have two DTOs, defined like this:

    OrderDTO
    {
      int OrderNumber;
      float OrderAmount;
    }

    CustomerDTO
    {
      Guid CustomerID;
      string Name;
      List<OrderDTO> Orders;
    }

    I want to query a list of customers and their orders, and I'm trying something like this:

    var customers = (from c in context.Customers
                              select new CustomerDTO {
                                CustomerID = c.CustomerID,
                                Name = c.Name,
                                Orders = (from o in c.Orders
                                               select new OrderDTO {
                                                 OrderNumber = o.OrderNum,
                                                 OrderAmount = o.Total
                                              {).ToList
                             }).ToList();

    but that's not working. The only way I've been able to do this is to retrieve the list of customers first and then loop through each customer and get the list of orders. However, I'm hoping to make a single SQL query rather than multiple.

    If I don't have the inner list (orders) then everything works fine. Can someone please help? Thanks in advance! 

    Wednesday, January 18, 2012 3:39 PM

Answers

  • Hi EricAES;

    The issue is the ToList() method inside the query. Entity Framework can't convert the the query to a valid SQL statement because of it and any attempt to do it that way should have cause a compile time error with a unsupported exception. To get around the issue you will need to make the List<OrderDTO> to a IEnumerable<OrderDTO> and remove the ToList() method in the query as shown below.

    var customers = (from c in context.Customers
                     select new CustomerDTO 
                     {
                       CustomerID = c.CustomerID,
                       Name = c.Name,
                       Orders = (from o in c.Orders
                                 select new OrderDTO 
                                 {
                                   OrderNumber = o.OrderNum,
                                   OrderAmount = o.Total
                                 })
                     }).ToList();
                     
    OrderDTO
    {
       public int OrderNumber { get; set; }
       public float OrderAmount { get; set; }
    }
     
    CustomerDTO
    {
       public Guid CustomerID { get; set; }
       public string Name { get; set; }
       public IEnumerable<OrderDTO> Orders { get; set; }
    }
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Alan_chenModerator Thursday, January 19, 2012 2:11 AM
    • Marked as answer by EricAES Thursday, January 19, 2012 1:07 PM
    Wednesday, January 18, 2012 9:19 PM