Update existing list values with values from another query

Question

I have a linq statement which calls a stored proc and returns a list of items and descriptions.

Like so;

var q = from i in doh.usp_Report_PLC() where i.QTYGood == 0 orderby i.PartNumber select new Parts() { PartNumber = i.PartNumber, Description = i.Descritpion.TrimEnd() };

I then have another SQL statement which returns the quantities on order and delivery date for each of those items. The Parts class has two other properties to store these. How do I update the existing Parts list with the other two values so that there is one Parts list with all four values?

UPDATE

The following code now brings out results.

                var a = from a1 in db.usp_Optos_DaysOnHand_Report_PLC()
                    where a1.QTYGood == 0
                    orderby a1.PartNumber
                    select new Parts() { PartNumber = a1.PartNumber, Description = a1.Descritpion.TrimEnd() };

            var b = from b1 in db.POP10110s
                    join b2 in db.IV00101s on b1.ITEMNMBR equals b2.ITEMNMBR
                    //from b3 in j1.DefaultIfEmpty()
                    where b1.POLNESTA == 2 && b1.QTYCANCE == 0
                    group b1 by new { itemNumber = b2.ITMGEDSC } into g
                    select new Parts() { PartNumber = g.Key.itemNumber.TrimEnd(), QtyOnOrder = g.Sum(x => Convert.ToInt32(x.QTYORDER)), DeliveryDue = g.Max(x => x.REQDATE).ToShortDateString() };

            var joinedList = a.Join(b,
                    usp => usp.PartNumber,
                    oss => oss.PartNumber,
                    (usp, oss) =>
                         new Parts
                         {
                             PartNumber = usp.PartNumber,
                             Description = usp.Description,
                             QtyOnOrder = oss.QtyOnOrder,
                             DeliveryDue = oss.DeliveryDue
                         });

            return joinedList.ToList();

Answer 1

Assuming your "other SQL statement" returns PartNumber, Quantity and DeliveryDate, you can join the lists into one:

var joinedList = q.Join(OtherSQLStatement(), 
                        usp => usp.PartNumber,
                        oss => oss.PartNumber,
                        (usp, oss) => 
                             new Parts
                                    {
                                        PartNumber = usp.PartNumber,
                                        Description = usp.Description,
                                        Quantity = oss.Quantity,
                                        DeliveryDate = oss.DeliveryDate
                                    }).ToList();

You can actually combine the queries and do this in one join and projection:

var joinedList = doh.usp_Report_PLC().
                     Where(i => i.QTYGood == 0).
                     OrderBy(i => i.PartNumber).
                     Join(OtherSQLStatement(), 
                          i => i.PartNumber,
                          o => o.PartNumber,
                          (i, o) => 
                               new Parts
                                    {
                                        PartNumber = i.PartNumber,
                                        Description = i.Description,
                                        Quantity = o.Quantity,
                                        DeliveryDate = o.DeliveryDate
                                    }).ToList();

And again: I assume you have PartNumber in both returned collections to identify which item belongs to which.

Edit

In this case the LINQ Query syntax would probably be more readable:

var joinedList = from aElem in a
                 join bElem in b
                 on aElem.PartNumber equals bElem.PartNumber into joinedAB
                 from abElem in joinedAB.DefaultIfEmpty()
                 select new Part
                            {
                                PartNumber = aElem.PartNumber,
                                Description = aElem.Description,
                                DeliveryDue = abElem == null ? null : abElem.DeliveryDue,
                                QtyOnOrder = abElem == null ? null : abElem.QtyOnOrder
                            };

Your DeliveryDue and QtyOnOrder are probably nullable. If not, replace the nulls by your default values. E.g. if you don't have the element in b and want QtyOnOrder to be 0 in the resulting list, change the line to

QtyOnOrder = abElem == null ? 0 : abElem.QtyOnOrder