Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

NOT IN Sub query with LINQ

Linq equivalent sql query “NOT IN”:

This article describes how to achieve sql “not in” sub query logic using LINQ query with sample example.

SQL not in Sub Query:

SQL Subqueries introduced with the keyword NOT IN also return a list of zero or more values.

The following query finds the names of the products, and main query excludes sub query returned values from result using NOT IN.

SQL sub Query:

SELECT * FROM  Products WHERE
CategoryID NOT IN (SELECT CategoryID FROM Categories WHERE
CategoryName =  "Mobile")   


LINQ sub query:

If we want to get the equivalent of SQL subquery with NOT IN, you need to construct an inner query first, and use the Contains () method in inner query on main query where clause with not equal operator or negate.

Example Using Not equal to (!=)

 //Returns only Records (products) that satisfy where condition
Categoryname<>'Mobile' 
 var CellPhones = (from item in ListProducts
         where item.ProductName != string.Empty
           where item.CategoryId != (from subitem in categories
                                     where subitem.CategoryName.Trim() == "Mobile"
select subitem.Id).First()
                            select item).ToList();

Another Example with negate (!)

 //Returns Records (products) that satisfy where condition Categoryname <>'Mobile'
  var innerQuery = from c in categories
         where c.CategoryName == "Mobile" select c.Id;
  var OuterQuery = from p in ListProducts
                 where !innerQuery.Contains(p.CategoryId) select p;


In this example linq subquery returns single column but it produce multiple values for the column category id and in main query we compare returned category id values with outer query category id values using not equal to operator or negate.

IN Sub query with LINQ

LINQ Self-contained multi valued sub query

Introduction 

My collogue told me LINQ is created to achieve SQL queries in programming language like(C#,VB), my quick reply was “Tell me linq query that achieve linq subquery logic using IN? ”.

Simply my question was convert following SQL query (Self-contained multi valued sub query) into linq query,
SELECT * FROM  Products WHERE
CategoryID in (SELECT CategoryID FROM Categories WHERE
CategoryName='Mobile') 

After some debate he started applying some logic like,

   var CellPhones=  from p in ListProducts
    where p.CategoryId =
            (
                        from c in categories
                        where c.CategoryName == "Mobile"
                        select c.Id
 
            )
    select p;


But this logic was not working (Syntax error), in last his conclusion was we cannot achieve all SQL syntax and logics with LINQ.

What is LINQ? Why LINQ?

These are set features introduced by Microsoft that extends powerful query capabilities to the language syntax of C# and VB.

We can achieve query capabilities with

1. LINQ to SQL

2. LINQ to Dataset

3. LINQ to objects

4. LINQ to XML

5. LINQ to entities.


SQL subquery with LINQ 

SQL subquery:

Select Query inside another query is a sub query, Select statement embedded in DML statement or nested in outer query.

SQL Self-contained multi valued sub query:

Self-contained multi-valued sub query still return a single column but it may produce multiple values for the column.

This multi valued sub query used with IN Predicate and this returns multiple values according to match (true or false).

Sub Query with LINQ:

If we want to get the equivalent of SQL subquery with IN (Self-contained multi valued sub query), you need to construct an inner query first, and then use the Contains () method in outer query or main query.

Following example is multi valued subquery that returns single column but it produce multiple values for the column category id.

LINQ Subquery Example:

//Generic product list data
List<Product> ListProducts = ProductDataAccess.GetProducts();
//Generic Categories list data
List<ProductCategory> categories = ProductDataAccess.GetProductCategories();

 

//Returns Records (products) that satisfy where condition Categoryname='Mobile'
var innerQuery = from c in categories where
c.CategoryName =="Mobile" select c.Id;
var OuterQuery = from p in ListProducts where innerQuery.Contains(p.CategoryId) select p;

 

 

 

 

 

Correlated subquery in Linq

In this article I will explain with sample example how you can achieve sql correlated subquery logic using linq in C#.

Sql Correlated Sub queries:

Sub query depends on the outer query for its values. This means that the sub query is executed repeatedly, once for each row that might be selected by the outer query.

   Like join outer query one or more columns matches with sub query one or more columns, and sub query returns matching records.

Linq Correlated Sub Queries:

With linq query you can achieve correlated functionality using where condition with two linq queries.  

Example:

In below example Outer linq query CategoryId is compared with inner linq(2nd linq query )CategoryId.

 

Linq Inner qury return categoryid if categoryName matches where condition and linq inner query cetagoryid matches with outer query.

 

Finally linq nner query returned categoryid value is used in outer select statement.


Products and Categories Data:


//Generic product list data
 List<Product> ListProducts = ProductDataAccess.GetProducts();
//Generic Categories list data
List<ProductCategory> categories = ProductDataAccess.GetProductCategories();

       

Linq Correlated subquery:


//Returns only Records (products) that satisfy where condition Categoryname='Mobile' 
 var CellPhones = (from item in ListProducts
                      where item.ProductName != string.Empty
                      where item.CategoryId == (from subitem in categories
                      where subitem.CategoryName.Trim() == "Mobile"                                    select subitem.Id).First()
                       select item).ToList();