Show records according to Id from another Table in ASP.Net MVC using Linq

smcdevelopments
 
on Apr 02, 2022 12:29 AM
1078 Views

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?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
smcdevelopments
 
on Apr 05, 2022 08:57 PM

Hi dharmendr,

I did using LEFT OUTER JOIN and as far as the results show as What I wanted.

 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,
                 LastOrders= order.OrderQty ?? "",
                 LastOrderDate = order.OrderDate.ToString()
               }).ToList();

Hope I did it correctly.