Getting Column Value from another Subquery

Question

I have a LINQ query. But I need to get value of two columns from another subquery. This is my Linq query:

)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();

Here in select portion of above query, instead of directly taking value of the column t.LOCATION_AD, I need to get it from SELECT LOC_NAME FROM LOCATION_MASTER WHERE LOC_CODE = t.LOCATION_AD

and instead of t.LOCATION_SD, I need to get value from SELECT LOC_NAME FROM LOCATION_MASTER where LOC_CODE = t.LOCATION_SD

How can I write this in LINQ. Is there any way to do this?

SiD · Answer 1 · 2016-02-09T13:12:32.520

2

You can make use of let clause. It is useful to store the result of sub-expression in order to use it in subsequent clauses.

Example:

(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();

Also, you can directly write LINQ without using let clause:

(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();

edited Feb 09 '16 at 13:12

answered Feb 09 '16 at 05:25

SiD

511
4
15

I have tried this. But getting this error: `Cannot implicitly convert type 'System.Linq.IQueryable' to 'string'` – Limna Feb 09 '16 at 11:45
@LimnaD'silva: Updated, however, it is just an example. You're getting that error because the type of `LOC_NAME` is different from `LOCATION_AD`. Check if the type of value fetching from database is `string`, if yes, you need to check type of `LOCATION_AD` property of `ASNPutawayRep` class. – SiD Feb 09 '16 at 13:14
Both are declared as string. – Limna Feb 10 '16 at 03:42

Viru · Answer 2 · 2016-02-10T09:05:50.973

0

You can use AsQueryable to achieve this

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();

edited Feb 10 '16 at 09:05

answered Feb 09 '16 at 05:52

Viru

2,228
2
17
28

When I tried this, I got error `'System.Linq.IQueryable' does not contain a definition for FirstorDefault and no extension method 'FirstorDefault' accepting a first argument of type 'System.Linq.IQueryable' could be found (are you missing a using directive or an assembly reference?)'` – Limna Feb 09 '16 at 11:54
you have Sytem.Linq using statement in file where this code resides right?? – Viru Feb 09 '16 at 11:59
Strange...It should work....I think as you are using EF whole query is passed as expression and when QueryProvider trying to execute the expression it fails...I removed AsQueryable() from answer..Pls try again – Viru Feb 09 '16 at 12:22
Did you try another answer given by SID? – Viru Feb 10 '16 at 07:50
Yeah..That also I have tried. But same error exists. – Limna Feb 10 '16 at 09:01
I have checked out. But it's telling `'System.Linq.IQueryable' does not contain a definition for FirstorDefault...`. I think it is not possible to do so in this case (to get column value using subquery):( – Limna Feb 10 '16 at 09:19

Getting Column Value from another Subquery

2 Answers2