Implement SQL IN and NOT IN operators and sub-queries using LINQ to SQL
SQL IN and NOT IN operators with a combination with sub-queries can be really useful in some cases. An example of this is below. All queries in this post don’t contain real tables.
1: select *
2: from table1 t1
3: where t1.id in (
4: select t2.t1Id
5: from table2 t2
6: where t2.someId = 1234)
7: and t1.id not in (
8: select t2.t1Id
9: from table2 t2
10: where t2.someOtherId = 5678)
In LINQ to SQL we can use Contains<>() extension method in order to ask LINQ to SQL to translate the query in a way that query result will be the same as the result of the above query. We can write a LINQ query like those below.
1: var query = from t1 in dataContext.GetRepository<Table1>()
2: where (from t2 in dataContext.GetRepository<Table2>()
3: where t2.someId == 1234
4: select t2.t1Id).Contains(t1.Id)
5: && !(from t2 in dataContext.GetRepository<Table2>()
6: where t2.someOtherId == 5678
7: select t2.t1Id).Contains(t1.Id)
8: select new
9: {
10: // your temp object’s properties here
11: };
This query will be translated by LINQ in SQL query that uses EXISTS and NOT EXISTS like those below but the result of its execution will be the same like one with IN and NOT IN. Below is the sample of the SQL query that is produced by the above LINQ statement.
1: SELECT <all_fields>
2: FROM [Table1] AS [t1]
3: WHERE (EXISTS(
4: SELECT NULL AS [EMPTY]
5: FROM [Table2] AS [t2]
6: WHERE ([t2].[t1Id] = [t1].[Id]) AND ([t2].[someId] = @p0))
7: )) AND (NOT (EXISTS(
8: SELECT NULL AS [EMPTY]
9: FROM [Table2] AS [t2]
10: WHERE ([t2].[t1Id] = [t1].[Id]) AND ([t2].[someOtherId] = @p2))))
And if you have a big sub-queries you can define them before the main query and then use them in it. It will make your code better to read and will not affect the result of the main query. An example is below.
1: var inSubquery = from t2 in dataContext.GetRepository<Table2>()
2: where t2.someId == 1234
3: select t2.t1Id;
4:
5: var notInSubquery = from t2 in dataContext.GetRepository<Table2>()
6: where t2.someOtherId == 5678
7: select t2.t1Id;
8:
9: var query = from t1 in dataContext.GetRepository<Table1>()
10: where inSubquery.Contains(t1.Id)
11: && !notInSubquery.Contains(t1.Id)
12: select new
13: {
14: // your temp object’s properties here
15: };
Leave a Reply