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

Subqueries In LINQ

Subqueries using LINQ

Introduction:

Previously I have written article on sql sub quires and sql sub-query types, many users suggested me in email to write article on Linq sub-queries.

Considering users valuable suggestion, in this article I will describe in detail sub queries using linq.

Sql sub-queries:

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

Sub query types:

1. Self contained sub query.

 Sql self contained sub query categorized in three types
a. Self contained scalar sub query
b. Self contained multi valued query
c. Table-valued sub query

2. Co-related sub query.

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.

Sql equivalent  linq sub queries :

Linq Self contained sub query

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

Example:

Following example is multi valued subquery that returns single column but it produce multiple values for the column category id.
  //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;


Linq Co-related sub query

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.
//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();

 

Conclusion:

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

We can achieve all sql equivalent sub-queries in using Linq expression and linq features.

 

Review SQL queries for security vulnerabilities

Introduction:

After lot of code quality related issues and bad code quality blame game our project team started
New magical process called peer programming.
In this game you are more happy to find mistakes from collogue, and according to me that is perfect definition of magic process called peer programming.

In peer programming I found following code with attribute SuppressMessage and detail as Review SQL queries for security vulnerabilities.

 

[SuppressMessage("Microsoft.Security""CA2100:Review SQL queries for security vulnerabilities")]
        private static bool UpdateProducts(string procedureName, Product product)
        {
            var oConn = new SqlConnection(conString);
          
            oConn.Open();
            var oCmdProducts = new SqlCommand(procedureName, oConn);
            oCmdProducts.CommandType = CommandType.StoredProcedure;
            oCmdProducts.Parameters.Add(new SqlParameter("@ProductId"SqlDbType.Int)).Value =
               product.ProductId;
            oCmdProducts.Parameters.Add(new SqlParameter("@Pice"SqlDbType.Int)).Value =
            product.Price;
            if (oCmdProducts.ExecuteNonQuery() > 0)
            {
                return true// success
            }
            else
            {
                return false//fail
            }
        }
 
I started looking what is meaning of that attribute on MSDN I found these are some warning rules provided by Microsoft Managed Code Analysis tool.

What is Managed Code Analysis tool?

The Managed Code Analysis tool provides warnings that indicate rule violations in managed code libraries. The warnings are organized into rule areas such as design, localization, performance, and security. Each warning signifies a violation of a Managed Code Analysis rule.

Rule CA2100: Review SQL queries for security vulnerabilities

This rule assumes that the string argument contains user input. A SQL command string that is built from user input is vulnerable to SQL injection attacks. In a SQL injection attack, a malicious user supplies input that alters the design of a query in an attempt to damage or gain unauthorized access to the underlying database. Typical techniques include injection of a single quotation mark or apostrophe, which is the SQL literal string delimiter; two dashes, which signifies a SQL comment; and a semicolon, which indicates that a new command follows.

Violations of Rule:

Notice that this rule is violated when the ToString method of a type is used explicitly or implicitly to construct the query string.

Example:

ToString():

string query = "SELECT * FROM Products where CategoryId in (Select id from Categories where
CategoryName="+CategoryName.ToString()+")";

 

In this Example the rule is violated because a malicious user can override the ToString() method.

 

Implicit string conversion:

var TopCount = 10; string query = String.Format("SELECT TOP{0}
Productname,price FROM Products", TopCount);

 

In this example the rule is violated when ToString is used implicitly.

How to Fix Violations

To fix these violations use a parameterized query.

Other some suggestion are,

=> Use a stored procedure.
=> Use a parameterized command string.
=> Validate the user input for both type and content before you build the command string.

Safe code and Unsafe Code Example:

Safe Code example:

  public static List<Product> SafeProductsDeatilCall(string CategoryName)
      {
          var products = new List<Product>();
          var con = new SqlConnection(conString);
          try
          {
              con.Open();
              SqlCommand cmd = new SqlCommand("GetProductDetails", con);
              cmd.Parameters.Add("@CategoryName"SqlDbType.NChar).Value = CategoryName;
              string query = "SELECT * FROM Products where CategoryId in (Select id from Categories where CategoryName=@CategoryName";
              cmd.CommandText = query;
              SqlDataReader dr = cmd.ExecuteReader();
              while (dr.Read())
              {
                  Product p = new Product();
                  p.ProductId = Convert.ToInt32(dr["ProductID"]);
                  p.ProductName = dr["ProductName"].ToString();
                  p.Price = Convert.ToDecimal(dr["Price"].ToString());
                  p.CategoryId = (dr["CategoryId"] != DBNull.Value) ? Convert.ToInt32(dr["CategoryId"]) : 0;
                  p.ImageUrl = (dr["Imageurl"] != DBNull.Value) ? dr["Imageurl"].ToString() : string.Empty;
                  products.Add(p);
              }
              return products;
          }
          catch (Exception ex)
          {
              //log ex
              return null;
          }
          finally
          {
              con.Close();
          }
      }

 

     Unsafe Code Example:

 

      public static List<Product> UnsafeProductsDeatilCall(string CategoryName )
      {
          var products = new List<Product>();
          var con = new SqlConnection(conString);
          try
          {
              con.Open();
              SqlCommand cmd = new SqlCommand("GetProductDetails", con);
              string query = "SELECT * FROM Products where CategoryId in (Select id from Categories where CategoryName=" + CategoryName.ToString() + ")";
              cmd.CommandText = query;
              SqlDataReader dr = cmd.ExecuteReader();
              while (dr.Read())
              {
                  Product p = new Product();
                  p.ProductId = Convert.ToInt32(dr["ProductID"]);
                  p.ProductName = dr["ProductName"].ToString();
                  p.Price = Convert.ToDecimal(dr["Price"].ToString());
                  p.CategoryId = (dr["CategoryId"] != DBNull.Value) ? Convert.ToInt32(dr["CategoryId"]) : 0;
                  p.ImageUrl = (dr["Imageurl"] != DBNull.Value) ? dr["Imageurl"].ToString() : string.Empty;
                  products.Add(p);
              }
              return products;
          }
          catch (Exception ex)
          {
              //log ex
              return null;
          }
          finally
          {
              con.Close();
          }
      }

 Conclusion:

Consider Managed Code Analysis tool security rule in database coding, better use stored procedure.
If you are using inline query use parameterized sql query suggested by code analysis tool. 

 reference:

https://msdn.microsoft.com/en-us/library/ms182310.aspx 

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.