从另一个子查询获取列值

Question

我有一个LINQ查询。但是我需要从另一个子查询中获取两列的值。这是我的 Linq 查询：

)from t in db.PUTAWAYs
join t0 in db.ASN_ITEM on t.AWB_NO equals t0.AWB_NO
join t1 in db.ASN_MASTER on t0.AWB_NO equals t1.AWB_NO
join t2 in db.ITEM_MASTER on t.ITEM_MASTER.ITEM_CODE equals t2.ITEM_CODE
join t3 in db.ASN_INPUT on t0.AWB_NO equals t3.AWB_NO
where
  t3.ITEM == t2.ITEM_CODE &&
  1 == 1 &&                          
  (fromDate == "" || toDate == "" || (t0.REC_DATE.CompareTo(fromDate) >= 0 && t0.REC_DATE.CompareTo(toDate) <= 0)) &&
  (AWB_NO == "" || (t0.AWB_NO == AWB_NO))
orderby
  t.AWB_NO,
  t0.REC_DATE,
  t0.STYPE,
  t2.PART_NO
select new ASNPutawayRep
{
    AWB_NO = t.AWB_NO,
    REC_DATE = t0.REC_DATE,
    STYPE = t0.STYPE,
    PART_NO = t2.PART_NO,
    //LOCATION_AD = t.LOCATION_AD,
    QNTY = t.QNTY,
    //LOCATION_SD = t.LOCATION_SD,
    REGION_ID = t.REGION_ID
}).Distinct();

在上述查询的选择部分，而不是直接获取列t.LOCATION_AD的值，我需要从SELECT LOC_NAME FROM LOCATION_MASTER WHERE LOC_CODE = t.LOCATION_AD中获取它

而不是t.LOCATION_SD，我需要从SELECT LOC_NAME FROM LOCATION_MASTER where LOC_CODE = t.LOCATION_SD中获取价值

如何在 LINQ 中编写它。有什么办法可以做到这一点吗？

Answer 1

您可以使用let子句。存储子表达式的结果以便在后续子句中使用它很有用。

例：

(from t in db.PUTAWAYs
 ...
 let locAd = from l in LOCATION_MASTER where LOC_CODE = t.LOCATION_SD select l.LOC_NAME
 where
 ...
 orderby
 ...
 select new ASNPutawayRep
 {
     LOCATION_AD = locAd,
 }).Distinct();

此外，您可以直接编写 LINQ，而无需使用let子句：

(from t in db.PUTAWAYs
 ...
 where
 ...
 orderby
 ...
 select new ASNPutawayRep
 {
     LOCATION_AD = from l in LOCATION_MASTER where LOC_CODE = t.LOCATION_SD select l.LOC_NAME
 }).Distinct();

Answer 2

您可以使用 AsQueryable 来实现此目的

from t in db.PUTAWAYs
join t0 in db.ASN_ITEM on t.AWB_NO equals t0.AWB_NO
join t1 in db.ASN_MASTER on t0.AWB_NO equals t1.AWB_NO
join t2 in db.ITEM_MASTER on t.ITEM_MASTER.ITEM_CODE equals t2.ITEM_CODE
join t3 in db.ASN_INPUT on t0.AWB_NO equals t3.AWB_NO
where
  t3.ITEM == t2.ITEM_CODE &&
  1 == 1 &&                          
  (fromDate == "" || toDate == "" || (t0.REC_DATE.CompareTo(fromDate) >= 0 && t0.REC_DATE.CompareTo(toDate) <= 0)) &&
  (AWB_NO == "" || (t0.AWB_NO == AWB_NO))
orderby
  t.AWB_NO,
  t0.REC_DATE,
  t0.STYPE,
  t2.PART_NO
select new ASNPutawayRep
{
    AWB_NO = t.AWB_NO,
    REC_DATE = t0.REC_DATE,
    STYPE = t0.STYPE,
    PART_NO = t2.PART_NO,
    LOCATION_AD = (from l in db.LOCATION_MASTER 
                     where l.LOC_CODE = t.LOCATION_AD
                     select LocName)ToList().FirstorDefault(),
    QNTY = t.QNTY,
    LOCATION_SD = (from l in db.LOCATION_MASTER 
                     where l.LOC_CODE = t.LOCATION_SD
                     select LocName).ToList().FirstorDefault(),
    REGION_ID = t.REGION_ID
}).Distinct();