Limit LINQ left outer join to one row

I’m trying to give condition on left join in Linq to limit Row from right table to one row. There is no way out!

The following is left join in LINQ.

var a = from qp in Share.DataContext.POS_factory_QuantityProduce

join qpi in Share.DataContext.POS_factory_InStockBL
on qp.ID equals qpi.QPID into GQP1

join qpo in Share.DataContext.POS_factory_OutStockBL
                             .OrderByDescending(s => s.PreparedDate)
on qp.ID equals qpo.QPID into GQP2                  

from qpi in GQP1.DefaultIfEmpty()
from qpo in GQP2.DefaultIfEmpty()

select new ProductFlowList()
{

  QPID = qp.ID,
  QPItemID = qpi.ID == null ? 0 : qpi.ID,
  QPOutItemID = qpo.ID == null ? 0 : qpo.ID,
  ItemID = qp.ItemID.Value,
  ProductCode = qp.POS_item_Items.ItemCode,
  ProductName = qp.POS_item_Items.ItemName,
  Unit = qp.Unit,
  Quantity = qp.QuantityProduce.Value,
  BeforeDelStock = qpi.BeforDelStock == null ? 0 : qpi.BeforDelStock.Value,
  InStock = qpi.InQty == null ? 0 : qpi.InQty.Value,
  OutStock = qpo.OutQty == null ? 0 : qpo.OutQty.Value,  
  AfterDelStock = qpo.AfterDelStock == null ? 0 : qpo.AfterDelStock.Value
};

To solve the above problem, Let expression Linq can

What is Let in Linq?

With let query clauses, you can introduce a variable into scope and use it in the subsequent query clauses. Similar to local variables in a method body, this gives you a way to avoid evaluating a common expression multiple times by storing it in a variable. This can be very useful even in much simpler queries. Of course, in the query above – let is absolutely critical.

var a = from qp in Share.DataContext.POS_factory_QuantityProduce
        let qpi = Share.DataContext
                       .POS_factory_InStockBL
                       .Where(s=>s.QPID == qp.ID)
                       .OrderByDescending(s=> s.PreparedDate)
                       .FirstOrDefault()                    
        let qpo = Share.DataContext
                       .POS_factory_OutStockBL
                       .Where(s=> s.QPID == qp.ID)
                       .OrderByDescending(s=>s.PreparedDate)
                       .FirstOrDefault()

select new ProductFlowList()
{
  QPID = qp.ID,
  QPItemID = qpi.ID == null ? 0 : qpi.ID,
  QPOutItemID = qpo.ID == null ? 0 : qpo.ID,
  ItemID = qp.ItemID.Value,
  ProductCode = qp.POS_item_Items.ItemCode,
  ProductName = qp.POS_item_Items.ItemName,
  Unit = qp.Unit,
  Quantity = qp.QuantityProduce.Value,
  BeforeDelStock = qpi.BeforDelStock == null ? 0 : qpi.BeforDelStock.Value,
  InStock = qpi.InQty == null ? 0 : qpi.InQty.Value,
  OutStock = qpo.OutQty == null ? 0 : qpo.OutQty.Value, 
  AfterDelStock = qpo.AfterDelStock == null ? 0 : qpo.AfterDelStock.Value
};

Let expression is impressive!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s