How to include a subquery inside .where in c#?

Question

My simple SQL query is

select * 
from deletedCards 
where cardNumber in (select cardNum from cardInformation 
                     where country = 'NP')

and I need to write it in C#. I've come this far :

var Query1 = dbContext.deletedCards;
Query1.Where(x => dbContext.cardInformation.Where(a => a.country== 'NP')).Select(a => a.cardNum).Contains(x => x.cardNumber);

but this is showing an error

Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type

Can anybody help me write the query correctly? Thanks!

score 1 · Accepted Answer · answered Dec 17 '18 at 05:30

1

You didn't filter anything in your Where method also your Contains method is not right, try this way.

var npCardNums = dbContext.cardInformation.Where(a => a.country == 'NP')
                                          .Select(a => a.cardNum);
var query1 = dbcontext.deletedCards.Where(x => npCardNums.Contains(x.CardNumber));

I will suggest you to read Language Integrated Query (LINQ) section on MSDN.

answered Dec 17 '18 at 05:30

SᴇM

7,024
3
24
41

Looks like it worked ! Thanks. And I'll surely look into the MSDN link you referred. :) – Shiwa Dec 17 '18 at 05:38

Vinoth · Answer 2 · 2018-12-18T08:36:08.600

1

You can use join also

    var ans= from dc in deletedcards 
    join CI in cardInformation on dc.cardnum equals CI.cardNum
    where CI.coumtry='NP' select dc

edited Dec 18 '18 at 08:36

answered Dec 18 '18 at 08:25

Vinoth

851
7
23

1

Although a subquery is what the OP asked for, taking the alternative approach of using a join, as suggested in this answer, will result in a significantly faster SQL query, especially if the appropriate indices are set up on the tables being queried. Curious folks can look here for a very quick explanation of why joins are better than subqueries: https://stackoverflow.com/questions/141278/subqueries-vs-joins – jdnew18 Dec 19 '18 at 19:40

Nitesh Kumar · Answer 3 · 2018-12-17T05:38:52.443

0

Try like this.

var Query1 = dbContext.deletedCards;
Query1.Where(x=> dbContext.cardInformation.Where(a=>a.country== 'NP').Contains(x=>x.cardNumber)).Select(a=>a.cardNum).ToList();

edited Dec 17 '18 at 05:38

answered Dec 17 '18 at 05:25

Nitesh Kumar

1,774
4
19
26

I tried that and the error is gone but I got new error in the end at 'x=>x.cardNumber' as this : "cannot convert lambda expression to type 'string' because it is not a delegate type" – Shiwa Dec 17 '18 at 05:31
I have modified the query. Can you please try this? – Nitesh Kumar Dec 17 '18 at 05:39

How to include a subquery inside .where in c#?

3 Answers3