GridView Only populating 1 result

Question

I'm currently working to add Data to a GridView. The data comes from 2 tables that are on different databases. Currently I am able to populate the first entry, but it does not populate past that. here is the code:

        void FillOrder(int inv)
        {
            var _ord = new OrdersContext();
            var _pro = new ProductContext();

            var qryOrder = (from o in _ord.OrderDetails
                            where o.InvNumberId == inv
                            select new
                            {
                                o.ProductID,
                                o.Quantity
                            }).ToList();
            foreach (var order in qryOrder)
            {
                int prodID = order.ProductID;
                int itemCount = qryOrder.Count;
                var qryProducts = (from p in _pro.Products
                                   where p.ProductID == prodID
                                   select new
                                   {
                                       p.ProductID,
                                       p.ProductName
                                   }).ToList();

                var results = (from t in qryOrder
                               join s in qryProducts
                               on t.ProductID equals prodID
                               select new
                               {
                                   t.ProductID,
                                   t.Quantity,
                                   s.ProductName
                               }).ToList();
                OrderItemList.DataSource = results;
                OrderItemList.DataBind();

            }

        }

Can anyone help as to why it's only populating the first entry?

Answer 1

If the number of products involved is relatively small, (and since this query seems to be relate to one invoice, I would think that is true), then you can probably use something like the code below.

This is removing the loop, but the contains method will probably generate a SQL statement something like select ProductID, ProductName from products where productID in (,,,,,,) so may fail if the number of parameters is extremely large.

var _ord = new OrdersContext();
var _pro = new ProductContext();

var qryOrder = (from o in _ord.OrderDetails
                where o.InvNumberId == inv
                select new
                {
                    o.ProductID,
                    o.Quantity
                }).ToList();

// Get the productIDs 
var productIDS = qryOrder.Select(o=>o.ProductID).Distinct().ToList();

// Get the details of the products used. 
var qryProducts = (from p in _pro.Products
                   where productIDS.Contains(p.ProductID)
                   select new
                   {
                       p.ProductID,
                       p.ProductName
                   }).ToList();

// Combine the two in memory lists
var results = (from t in qryOrder
               join s in qryProducts
               on t.ProductID equals s.ProductID
               select new
               {
                   t.ProductID,
                   t.Quantity,
                   s.ProductName
               }).ToList();

OrderItemList.DataSource = results;
OrderItemList.DataBind();