Show related records according to the Id from another Table-CodePudding

In my ASP.NET MVC application, I have created an HTML Table using the view model.

For this, I have written a query that shows only the data that ReOrderQty is !=0 and AvaQty is less than ReOrderQty.

List < RecognizedPartsViewModel > Reco = new List < RecognizedPartsViewModel > ();

var rData = (from i in db.InventoryMain 
             join p in db.PartCategory on i.PartCatogary equals p.Id 
             where i.ReOrderQty != 0 && i.AvaQty <= i.ReOrderQty && i.PartCatogary != 0
             select new RecognizedPartsViewModel {
             Id = i.Id,
             PartNo = i.PartNo,
             Description = i.PartDescription,
             Model = i.PartModel,
             AvaQty = i.AvaQty,
             ReOrderQty = i.ReOrderQty,
             PartCato = i.PartCatogary,
             ABCD = i.A_B_C_D_Category

             }).ToList();

So as so far, table data is showing according to the query.

There is another table where I store Orders according to the PartId. So I want to show that data on another column in the same HTML Table.

I can get those details by joining the join ord in db.OrderTable on i.Id equals ord.PartNo_Id but when it does results only show the PartNumbers that only contains on the OrderTable only.

This is how I modified it as I mention.

List < RecognizedPartsViewModel > Reco = new List < RecognizedPartsViewModel > ();

var rData = (from i in db.InventoryMain 
             join p in db.PartCategory on i.PartCatogary equals p.Id 
             join ord in db.OrderTable on i.Id equals ord.PartNo_Id 
             where i.ReOrderQty != 0 && i.AvaQty <= i.ReOrderQty && i.PartCatogary != 0

    select new RecognizedPartsViewModel {
        Id = i.Id,
            PartNo = i.PartNo,
            Description = i.PartDescription,
            Model = i.PartModel,
            AvaQty = i.AvaQty,
            ReOrderQty = i.ReOrderQty,
            PartCato = i.PartCatogary,
            ABCD = i.A_B_C_D_Category,
            PastOrders = "Order Qty: "   ord.OrderQty

    }).ToList();

So, when this does like I was said earlier not show every record, it shows only the record in the ordertable.

So how I can show those tables within the same view without losing my main requirement?

CodePudding user response：

That would be

    left outer join

var rData = (from i in db.InventoryMain 
             join p in db.PartCategory on i.PartCatogary equals p.Id 
             join ord in db.OrderTable on i.Id equals ord.PartNo_Id into leftjoin 
             from order in leftjoin.DefaultIfEmpty()
             where i.ReOrderQty != 0 && i.AvaQty <= i.ReOrderQty && i.PartCatogary != 0

             select new RecognizedPartsViewModel {
                  Id = i.Id,
                  PartNo = i.PartNo,
                  Description = i.PartDescription,
                  Model = i.PartModel,
                  AvaQty = i.AvaQty,
                  ReOrderQty = i.ReOrderQty,
                  PartCato = i.PartCatogary,
                  ABCD = i.A_B_C_D_Category,
                  PastOrders = "Order Qty: "   order?.OrderQty ?? string.Empty
             }).ToList();

Reference: Perform left outer joins