LINQ: NOT IN

I would definitely not recommend using complex SQL Statements in your codes, because you should already have written those in your Stored Procedures; But if for some strange reasons, you really need to, here’s how you can do it:

Your NOT IN statement

SELECT * FROM dbo.MAIN_TABLE

WHERE ID NOT IN (SELECT ROLEID FROM FILTER_TABLE WHERE EMPLID = ‘0230’)

Pretty simple, eh? Here’s the LINQ version

Dim _mainTableClass = From mainTable

         In _myEntities.MAIN_TABLE

         Where Not (From userTable In _myEntities.FILTER_TABLE

                    Where userTable.EMPLID = ‘0230’

                    Select userTable.ROLEID).Contains(mainTable.ID)

     Select New MainTableClass With {

                              .ID = mainTable.ID,

                              .SiteID = mainTable.SITEID,

                              .Descp = mainTable.DESCP,

                              .RoleCode = mainTable.ROLECODE

                              }

Return _mainTableClass

I also returned it as a new type as i didn’t want to be playing with it as an Anonymous type.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s