Home > ASP.Net > “Not In” Subquery with ASP.Net LINQ in VB

“Not In” Subquery with ASP.Net LINQ in VB

April 3rd, 2010 Leave a comment Go to comments

I looked all over to find a way to do subqueries and “not in” statements using LINQ. This post is to share the simplest way that I’ve found.

Here is the SQL query that I was trying to recreate in LINQ:

SELECT DISTINCT lu.leagueid, lu.id, AS Expr1, l.name
FROM            leagueuser AS lu INNER JOIN
league AS l ON lu.leagueid = l.id
WHERE        (l.name NOT IN
(SELECT        league.name
FROM            league INNER JOIN
leagueuser ON league.id = leagueuser.leagueid
WHERE        (leagueuser.userid = 4)))

(The formatting was autofixed by visual studio to look like this, but the query works)

The vb.net code to create this query in LINQ is actually two parts as follows:

Dim firstquery = From b In dc.leagueusers Where b.userid = currentUser Select b.leagueid
Dim leaguesnotjoined = From p In dc.leagueusers Where Not firstquery.Contains(p.leagueid) Select New With {p.league.id, p.league.name} Distinct

You can see that the second query uses the first query. Again, it’s not the most elegant solution, but it’s pretty straightforward and the only way I’ve successfully gotten it to work.  If you wanted to make it an “in” statement, you’d just have to remove the word “not” from the second query.
Share
Categories: ASP.Net Tags: , , ,
  1. jepoy
    July 20th, 2011 at 03:53 | #1
    Reply | Quote

    Thanks, works like a charm.
    i was looking for IN keyword but don’t find it.
    gud thing you have this post. please keep on posting. :)

  2. dawit
    October 3rd, 2011 at 11:46 | #2
    Reply | Quote

    that was helpful. tnx

  1. No trackbacks yet.