Wednesday, December 15, 2010

SQL to LINQ ( Case 7 - Filter data by using IN and NOT IN clause)

Shout it kick it on DotNetKicks.com
After the the first post SQL to LINQ ( Visual Representation ). In this post I am going to show some SQLqueries and LINQ queries, but not going to show images for all cases.

Case 7 : Filter data by using IN and NOT IN clause

Most of the developer who started working on LINQ queries gets confuse when they got requirement to write IN and NOT IN query using LINQ.

SQL Query

//IN
SELECT [Id], [UserId], [IMEINo]
FROM [UserClients]
WHERE [UserId] IN (3, 4)

or

//NOT IN
SELECT [Id], [UserId],  [IMEINo]
FROM [UserClients]
WHERE [UserId] IN (3, 4)

as you see above query use IN and NOT IN clause to filter from list of records.

LINQ Query
To achieve similar task LINQ make use of Contains function of C#. which do filtering of record form the list of record.
//IN
int[] chosenOnes = { 3, 4 };
var user = from u in UserClients
where chosenOnes.Contains(u.UserId.Value)
select new  { u.id,u.userid, u.ImeiNo};

or 

//NOT IN
int[] chosenOnes = { 3, 4 };
var user = from u in UserClients
where !chosenOnes.Contains(u.UserId.Value)
select u;

Note :
IN and NOT IN use same function in LINQ query but it just use !(Not) symbol for it.

Graphical representation


Summary
Part - 1 : SQL to LINQ ( Visual Representation )  

No comments:

Post a Comment