Linq query problem (too many ado.net request)

Question

Hello! I have trouble in my linq application.

I have Orders, OrderChangeLog and OrderItems tables. In 1 query i want to load orders and dependent tables. I use Linq.

 return from p in _db.dbOrders
 select new Order
 {
 ID = p.ID, 
 OrderStatusChangelog = new List<OrderStatusChangelog>( GetOrderStatusChangelog().Where(x => x.OrderID == p.ID)),
 Items = new List<OrderItem>(GetOrderItems(p.ID)), };

In this variant it takes too many ADO.NET requests (see image1) image1 full size

But. If i comment

Items = new List<OrderItem>(GetOrderItems(p.ID))

Perfect result (image2 full size)

Why one join work so?

P.S. My T-SQL (generated by LINQ):

{SELECT [t0].[ID], [t0].[UserID], [t0].[DateOrder] AS [DateCreated], [t0].[ControlGUID] AS [Guid], [t0].[StatusID], [t1].[ID] AS [ID2], [t1].[OrderID], [t1].[StatusID] AS [OrderStatusID], [t1].[Comment] AS [StatusMessage], [t1].[UserID] AS [UserID2], [t1].[Date], [t2].[FullName] AS [UserName], (
SELECT COUNT(*)
FROM [dbo].[dbOrderStatusChangelog] AS [t3]
INNER JOIN [dbo].[dbUsers] AS [t4] ON [t4].[ID] = [t3].[UserID]
WHERE [t3].[OrderID] = [t0].[ID]
) AS [value], [t0].[ShippingFLP], [t0].[ShippingAddress] AS [ShippingAddressContent], [t0].[ShippingRegionID], [t0].[ShippingCity], [t0].[ShippingZIPCode], [t0].[ShippingPhone], [t0].[ShippingMetroID], [t0].[PaymentFLP], [t0].[PaymentAddress] AS [PaymentAddressContent], [t0].[PaymentRegionID], [t0].[PaymentCity], [t0].[PaymentZIPCode], [t0].[PaymentPhone], [t0].[TrackingNumber], [t0].[DateShipped], [t0].[ShippingCost] AS [Rate], [t0].[ShippingName] AS [Name], [t0].[ShippingTypeID], [t0].[PaymentName] AS [Name2], [t0].[PaymentTypeID], [t0].[SourceID], [t0].[CustomerComment], [t0].[CustomerEmail], [t0].[CustomerFLP], [t0].[DiscountAmount] AS [discountAmount], [t0].[DiscountReason] AS [discountReason], [t0].[Amount]
FROM [dbo].[dbOrders] AS [t0]
LEFT OUTER JOIN ([dbo].[dbOrderStatusChangelog] AS [t1]
    INNER JOIN [dbo].[dbUsers] AS [t2] ON [t2].[ID] = [t1].[UserID]) ON [t1].[OrderID] = [t0].[ID]
WHERE (CONVERT(Int,[t0].[StatusID])) IN (@p0, @p1, @p2)
ORDER BY [t0].[ID] DESC, [t1].[ID], [t2].[ID]}

Table diagram

UPD1

 private IQueryable<OrderItem> GetOrderItems(int orderID)
    {
        return from p in _db.dbOrderItems
               where p.OrderID == orderID
               select new OrderItem
                          {
                              ID = p.ID,
                              ItemPrice = p.Price,
                              OrderID = p.OrderID,
                              Quantity = p.Quantity,
                              Product = new Product
                                            {
                                                ID = p.ProductID,
                                                Name = p.ProductName,
                                                Brand = new Brand { Name = p.dbProduct.dbBrand.Name }
                                            }
                          };
    }


    private IQueryable<OrderStatusChangelog> GetOrderStatusChangelog()
    {
        return from p in _db.dbOrderStatusChangelogs
               select new OrderStatusChangelog
                          {
                              Date = p.Date,
                              ID = p.ID,
                              OrderID = p.OrderID,
                              OrderStatusID = p.StatusID,
                              StatusMessage = p.Comment,
                              UserID = p.UserID,
                              UserName = p.dbUser.FullName
                          };
    }

Answer 1

Items = new List...

This will cause an enumeration of the items you are querying, forcing the call to the database for each list that is created (and elements copied).

You can defer enumeration and the call to the consumer and have them convert the items to a list, or you can create a method that will create a list for them on demand or change the way they will interact by making your definition an IEnumerable and just returning the IQueryable.

The real question is, is it required for each item to be a list and to be fully populated on the main query or can you delay execution until the data is actioned upon?