CODEWITHSUNDEEP
c#sql-serverlinq
Micah Armantrout
Micah Armantrout

Reputation: 7001

Linq with left join to work

I would like supplierCosts to be left join. Currently there is no records .. read zero records in supplier costs but I still want the data from the other joins to show up. What am I doing wrong ? it only pulls back data if there is a record in SupplierCosts and then for only the record that is in that table.

 var query = (from popco in db.prcOpcoes
                 join p in db.products on popco.productID equals p.productID
                 join s in db.suppliers on popco.supplierNumber equals s.supplierNumber
                 join sc in db.SupplierCosts on new { s.supplierNumber, productId = p.productID } equals new { supplierNumber = sc.SupplierNumber, productId = sc.ProductID } into data
                 from x in data.DefaultIfEmpty()
                 where x.SupplierNumber == suppliernumber && popco.opcoID == opco 
                 select new ProductItem() { ProductID = p.productID, Description = p.division, FOB = x.FOB, Freight = x.Freight, DeliveredPrice = x.DeliveredPrice, Comments = x.Comments, Submitted = x.SubmittedDate });
    return query.ToList();

Upvotes: 0

Views: 49

Answers (1)

user6144226
user6144226

Reputation: 633

In the posted expression x is determined to be SupplierCost

Hence the where clause

where x.SupplierNumber == suppliernumber

Will eliminate "rows" if they are not present in SupplierCost

Try this instead:

where s.SupplierNumber == suppliernumber

Upvotes: 1

Related Questions