GridView Only populating 1 result-CodePudding

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?

CodePudding user response：

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();