LINQ Query which is using a query in where clause

Question

I want to achieve the below scenario...

Table 1 - tAccessRequest of Database 1 columns - RequestId, CardNo and so on...... Table 2 - tBadge of Database 2 Columns - CardNo, CardStatus and so on...

I created a Request Class as below

public class RequestDetails
{
    public int RequestID { get; set; }

    public int RequestTypeID { get; set; }

    public string PersonID { get; set; }

    public int SectionCode { get; set; }

    public int RequestStateID { get; set; }

    public int ApprovalStatusID { get; set; }
}

Now I am writing two LINQ query

List< RequestDetails > listReq = new List< RequestDetails >();

listReq = (from PP in DB1.tAccessRequests
                               where (PP.RequestStateID == 1 || PP.ApprovalStatusID == 1) && PP.SectionCode != null
                               select new RequestDetails
                               {
                                   RequestID = PP.RequestID,
                                   SectionCode = PP.SectionCode.Value 
                               }).ToList();

In the second LINQ query I want to achieve

var CardNoList = (from BC in prowatchContext.BADGE_C 
                                               where BC.STAT_COD != 'A' && BC.CARDNO in ("Select SectionCodefrom listReq"))

How to write the second LINQ query..

Please help

Check into Linq Inner Join https://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql — Kevin Raffay, Mar 06 '18 at 19:33
Thanks Kevin for the update but as I m hitting two different Databases, the join condition is failing. — user3083221, Mar 06 '18 at 19:51
Sorry forgot to mention, sectionCode in RequestDetail class is cardNo — user3083221, Mar 06 '18 at 21:00

score 0 · Answer 1 · answered Mar 07 '18 at 21:13

You just use Contains for IN:

var CardNoList = (from BC in prowatchContext.BADGE_C 
                  where BC.STAT_COD != 'A' &&
                        listReq.Select(lr => lr.SectionCode).Contains(BC.CARDNO)
                  ).ToList();

Note: If listReq is large this may not work unless you do the Contains test on the client, in which case you may want to use a HashSet:

var reqCardNos = new HashSet<int>(listReq.Select(lr => lr.SectionCode));

var CardNoList = prowatchContext.BADGE_C.Where(BC => BC.STAT_COD != 'A')
                                        .AsEnumerable()
                                        .Where(BC => reqCardNos.Contains(BC.CARDNO))
                                        .ToList();

The AsEnumerable will pull all the matching rows to the client and then filter them, so if BADGE_C is very large, this may not work well either, in which case you may have to push reqCardNos to the prowatchContext database in a temporary table and then do a join.

score -1 · Answer 2 · answered Mar 06 '18 at 20:40

-1

Try this:

var CardNoList = from BC in prowatchContext.BADGE_C 
                                              join lr in listReq on BC.CARDNO equals lr.CardNo where BC.STAT_COD != 'A'
                                              select BC

answered Mar 06 '18 at 20:40

Kevin Raffay

842
5
18

`lr.CardNo` how you get it ? – rahulaga-msft Mar 06 '18 at 20:41
I tried this but I m getting the below error ; "Only primitive types or enumeration types are supported in this context." – user3083221 Mar 06 '18 at 21:01
var CardNoList = from BC in prowatchContext.BADGE_C join lr in listReq on BC.CARDNO equals lr.sectionCode where BC.STAT_COD != 'A' select BC – Kevin Raffay Mar 06 '18 at 21:48
Hi kevin, I tried this but I m getting the above error. The join doesn't work here as we are joining a class object and a entity object – user3083221 Mar 07 '18 at 16:04
You may need to convert BC.CARDNO to an int: ..join lr in listReq on int.parse(BC.CARDNO).. – Kevin Raffay Mar 07 '18 at 18:45

LINQ Query which is using a query in where clause

2 Answers2