Use linked object multiple times in a request

I am trying to get a list of two tables into one generic class list that I have made.

My question is how can I get the fields from a related table without calling firstOrDefault on each field, please see the example code, I need a long string for the Price and Cost fields and I have a few more things to do ...

    Dim items As IQueryable(Of ItemMain)

    Return items.Where(Function(i) i.Status > -1).Select(Function(x) New ItemViewBasic() With {.ItemID = x.ItemID,
                                                                      .Name = x.Name,
                                                                      .BarcodeNumber = x.BarcodeNumber,
                                                                      .Price = x.ItemStores.Where(Function(itemStore) itemStore.StoreNo = GlobalValues.StoreID).FirstOrDefault().Price,
                                                                      .Cost = x.ItemStores.Where(Function(itemStore) itemStore.StoreNo = GlobalValues.StoreID).FirstOrDefault().Cost})



source to share

2 answers

After getting a hint from @ Jeroen-Vannevel in the comments about using the let keyword, I figured out how to do it. I am posting it here for future reference ..

      Return (From i In items Let itemStore = i.ItemStores.Where(Function(its) its.StoreNo = GlobalValues.StoreID And its.Status > -1).FirstOrDefault() Where i.Status > -1
            Select New ItemViewBasic With {.ItemID = i.ItemID,
                                           .Name = i.Name,
                                           .Cost = itemStore.Cost,
                                           .BarcodeNumber = i.BarcodeNumber,
                                           .OnHand = itemStore.OnHand,
                                           .Department = itemStore.DepartmentID,
                                           .Price = itemStore.Price,
                                           .ModalNumber = i.ModalNumber})




The approach I would use (in C #) would first generate a request for the ItemStore entries for the store in question, e.g.

var itemStores = myDbContext.ItemStores.Where(i => i.StoreNo = GlobalValues.StoreID);


Then attach it to whatever elements you already have:

return items.Join(itemStores, i => ItemId, is => is.ItemID, (i, is) => new ItemViewBasic {etc });




All Articles