Implement SQL EXISTS and IN operator using LINQ

LINQ (Language Integrated Query) is a set of features that extends query capabilities to language syntax of C# and Visual Basic.

In this post, we will see LINQ implementation of EXISTS condition and IN operator used in SQL.

Implement IN using LINQ:

    IN operator is used in SQL to check whether a value is contained in the sequence defined using IN operator. The syntax is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

Two important aspects of this operator is that it needs a column name(column_name in this case) and a set of values(value1, value2,... in this case) with which the column value is compared.

In LINQ, a similar implementation looks like below. It uses the Contains extension method

IEnumerable str = from c in db.table_name
        where new List(){value1,value2,...}.Contains(c.column_name) 
        select c;

Here db is the datacontext this is defined using the LINQ to SQL classes.

Implement EXISTS using LINQ:

    EXISTS operator checks whether at least one value satisfies the condition.

SELECT *
FROM customers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE customers.customer_id = orders.customer_id);

The above query will select all results from customers if the sub query returns at least one result.

In LINQ, a similar implementation looks like below. It uses the Any element operator

if(db.Test1s.Any(s => s.name == "abc"))
{
   var str = from c in db.Test1s
             select c;
}

Here, the condition inside Any is equivalent to subquery and the statement inside if is equivalent to the main query in sql.