foreach loop getting first record every time while iterate in .Net core dbcontext

Question

hi

please check above image I have attached debugger also .
here getting order id based on orderheaderList . the orderdetails while first time getting data properly (i.e., orderid=11141)
after that loop getting different orderid but binding first data only every time. once check the screenshot here orderdtl.orderid -->11141 is first time what time i am getting but second time also getting same data. Please tell me what thing I should change.
Let me know r u getting my issue

 [HttpGet("SoldOutList/{USERID}/{lng}")]  
        public ActionResult SoldOutList(short USERID, string lng, int OrderStatusID)  
        {  
            Int16 ORDERCLOSED = 2184;  
            try  
            {  
  
                OrderHistroyVM orderHistroyVM = new OrderHistroyVM();  
                orderHistroyVM.orderHeader = new List<usp_OrderHeaderSelect_Result>();  
                orderHistroyVM.orderDetail = new List<usp_OrderDetailList_Result>();  
                orderHistroyVM.orderPayment = new List<usp_OrderPaymentList_Result>();  
                //orderHistroyVM.orderHeader = ((List<usp_OrderHeaderList_Result>)_orderHeader.GetList(USERID, lng));  
                var orderDetail = _context.orderhederSp.FromSqlRaw("[Web].[usp_OrderHeaderListBySeller] {0}", USERID).ToList();  
                //x => x.OrderStatus == Utility.ORDERCLOSED && x.IsDelivered == true  
                orderHistroyVM.orderHeader = orderDetail.Where(x => x.OrderStatus == ORDERCLOSED &&  x.IsDelivered == true) .ToList();  
                if (orderHistroyVM.orderHeader.Count != 0)  
                {  
                    foreach (var item in orderHistroyVM.orderHeader)  
                    {  
                        var language = "en";  
                        var orderDetailList = _context.usp_OrderDetails.FromSqlRaw("[Web].[usp_OrderDetailList] {0},{1}", item.OrderID, language).ToList();  
  
                        // var orderDetailList = _orderDetail.GetList(item.OrderID);  
                        foreach (var orderdtl in orderDetailList)  
                        {  
                            orderHistroyVM.orderDetail.Add(orderdtl);  
                        }  
  
                        var orderpayment = _context.usp_OrderPayments.FromSqlRaw("[Web].[usp_OrderPaymentSelectByOrderID] {0}", item.OrderID).ToList();  
  
                        // for list write foreach other wise getting genericlist to list converstion issue  
                        if (orderpayment != null)  
                        {  
                            foreach (var payment in orderpayment)  
                            {  
  
                                orderHistroyVM.orderPayment.Add(payment);  
                            }  
                        }  
                    }  
                }  
  
                return Ok(orderHistroyVM);  
            }  
            catch (Exception ex)  
            {  
  
                throw ex;  
            }  
        }  

Answer 1

thanks for your replay @AgaveJoe @Michael Taylor @Viorel

1. i am converting MVC application to ASP.net core . so no issues in stored procedures.
2. in the code payment and orderdetails are same functionality Pyament filter working fine but orderdetails getting funny issue(getting first filter list
   every time )
3. ok i will try using local variables

Answer 2

Please use the Insert Code Editor tool to insert code into your posts so we can copy your code.

The problem is with your call to usp_OrderDetails. Let's verify that. Put a breakpoint on that call. Verify item.OrderID has the value you expect. Then F10 to execute the call. When the call returns view orderDetailList in the debugger (if using VS 2022 then use the IEnumerable visualizer). Check each of the items that were returned. Do they all have the same order detail? If they do then your sproc is returning duplicate data. I could see a potential for EF mappings to be wrong but since you're just using SQL here then I would find that odd.

Another possibility is that your orderHeader property has logic in it that is messing things up. Since you didn't post it we can only assume it is an auto-property.

Finally, we are assuming you don't have objects referencing other objects here that could be causing problems. You're using a VM so we have no idea what it is doing. Try switching to a local variable temporarily and see if the problem goes away.