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

Linq joins

Linq joins:

In this article I will explain in details with example all possible linq joins.

We can achieve sql joins by calling linq provided methods with proper parameters.

Before we continue with examples, we will list the types of the different JOINs you can use:

1.  INNER JOIN

2.  LEFT JOIN

3.  RIGHT JOIN

4.  FULL JOIN

5.  CROSS JOIN

6.  FULL JOIN

We can achieve more joins like group join, linq non equi join,linq equi join for more details

http://codechef4u.com/post/2016/01/14/linq-non-equi-join

http://codechef4u.com/post/2015/05/30/LINQ-Group-joins

linq join with multiple conditions:

Linq inner join:

In SQL server inner join returns common or matching records/rows from both the database tables (left table and right table).

Using the "join" keyword we can do an inner join using a LINQ query, join returns common or matching records/rows from both tables/collections.

Example:

        /// <summary>
        /// Method returns only matching or common records from product and categories  table, for inner join used linq query
        /// </summary>
        public List<Product> GetProductCatInnerJoin()
        {
        {
 
            List<Product> ProductCategories = new List<Product>();
            
            using (ApplicationServices DbContext = new ApplicationServices())
            {
  {
 
                //inner join linq query returns only matching records
                var InnerJoinProducts = (from p in DbContext.Product
                                         
                                         join c in DbContext.Category
                                         
                                         on new { PID = p.CategoryID } equals new { PID =   c.CategoryID }
                                         
                                         select p
 
                     ).ToList();

                     ).ToList();
                foreach (var p in InnerJoinProducts)
                {
     {
                    Product product = new Product();
                    product.ProductID = p.ProductID;
                    product.ProductName = p.ProductName;
                    product.Description = p.Description;
                    product.ImagePath = p.ImagePath;
                    product.UnitPrice = p.UnitPrice;
                    product.CategoryID = p.CategoryID;
 
                    ProductCategories.Add(product);
                }     }
            
                    product.ProductID = p.ProductID;
                    product.ProductName = p.ProductName;
                    product.Description = p.Description;
                    product.ImagePath = p.ImagePath;
                    product.UnitPrice = p.UnitPrice;
                    product.CategoryID = p.CategoryID;
 
                    ProductCategories.Add(product);
                }     }
            return ProductCategories;
      }}
   }}


Inner join with multiple tables Example

http://codechef4u.com/post/2015/05/24/LINQ-inner-join

LINQ Left outer join:

In SQL LEFT JOIN keyword returns all rows from the left table (Products), with the matching rows in the right table .The result is NULL in the right side when there is no match.

In LINQ to achieve LEFT JOIN on tables/collections data, your require to use "INTO" keyword and "DefaultIfEmpty()" method.

Example:

using (ApplicationServices DbContext = new ApplicationServices())
            {    
     {
                var products = (from p in DbContext.Product
                                
                                join c in DbContext.Category
                                
                                on new { PID = p.CategoryID } equals new { PID = c.CategoryID }
                                
                                into ProductInfo
                                
                                from  productsData in ProductInfo.DefaultIfEmpty()
                                
                                select new
                                {
                                      ProductID = p.ProductID,
                                      ProductName = (p.ProductName == null)? null:p.ProductName,
                                      Description =  (p.Description == 
                                      Description =  (p.Description == null)? null:p.Description,
                                      ImagePath = (p.ImagePath == 
                                      ImagePath = (p.ImagePath == null)? null:p.ImagePath,
                                      UnitPrice = (p.UnitPrice == 
                                      UnitPrice = (p.UnitPrice == null)? 0:p.UnitPrice,
                                      CategoryID = (p.CategoryID == 
                                      CategoryID = (p.CategoryID == null)? 0:p.CategoryID
                                   
                                }
 
                     ).ToList();
                                   
                                }
 
                     ).ToList();
 

Linq right outer join:

In SQL RIGHT JOIN keyword returns all rows from the right table with the matching rows in the left table. The result is NULL in the left side when there is no match.

 A right outer join is not possible with LINQ. LINQ only supports left outer joins. If we swap the tables and do a left outer join then we can get the behavior of a right outer join.
 
In LINQ RIGHT JOIN keyword is not available you can achieve right join using "INTO" keyword and "DefaultIfEmpty()" method.

Example:

using (ApplicationServices DbContext = new ApplicationServices())
            { 
            {
                var categories = (from c in DbContext.Category join p in DbContext.Product
                                
                                on new { PID = c.CategoryID } equals new { PID = p.CategoryID }
                                
                                into ProductCatInfo
                                  
                                  from productsCatData in ProductCatInfo.DefaultIfEmpty()
                                
                                select new
                                {
                                       CategoryID= productsCatData.CategoryID,
                                       CategoryName = (c.CategoryName == null) ? null : c.CategoryName,
                                       Description = (c.Description == 
                                       Description = (c.Description == null) ? null : productsCatData.Description,
                                       CreatedDate = (c.CreatedDate == 
                                       CreatedDate = (c.CreatedDate == null) ? null : c.CreatedDate,
                                      UpdatedDate = (c.UpdatedDate == 
                                      UpdatedDate = (c.UpdatedDate == null)? null:c.UpdatedDate
                                                                        
                                }
 
                     ).ToList();
                                                                        
                                }
 
                     ).ToList();
 


LINQ Full outer join:

In SQL Full outer join returns all the rows from both tables whether it has been matched or not.

To achieve full outer join in LINQ we require performing logical union of a left outer join and a right outer join result. LINQ does not support full outer joins directly, the same as right outer joins.

Example:

public List<Product> GetAllproductsUsingLinqFullOuter()
        {
        {
            var productsAll = new List<Product>();
            
            using (ApplicationServices DbContext = new ApplicationServices())
            {
                
            {
                //left join
                //all records from left table only matching records from right
                var productsLeft = (from p in DbContext.Product
                                    
                                    join c in DbContext.Category
                                        
                                        on new { PID = p.CategoryID } equals new { PID = c.CategoryID }
                                        
                                        into ProductInfo
                                    
                                    from productsData in ProductInfo.DefaultIfEmpty()
                                    
                                    select new
                                    {
                                        ProductID = p.ProductID??0,
                                        ProductName = p.ProductName ?? null,
                                        Description = p.Description ?? 
                                        Description = p.Description ?? null,
                                        ImagePath = p.ImagePath ?? 
                                        ImagePath = p.ImagePath ?? null,
                                        UnitPrice = p.UnitPrice ?? 0,
                                        CategoryID = productsData.CategoryID ?? 0,
                                        CategoryName = productsData.CategoryName 
                                        UnitPrice = p.UnitPrice ?? 0,
                                        CategoryID = productsData.CategoryID ?? 0,
                                        CategoryName = productsData.CategoryName //from right table
 
                                    }
 
                    ).ToList();
 
                //right join
                //all data from right table, only matching records from left table
                var productsRight = (from c in DbContext.Category
                                     
                                     join p in DbContext.Product
                                         
                                         on new { PID = c.CategoryID } equals new { PID = p.CategoryID}
                                         
                                         into ProductInfoRight
                                     
                                     from productsRightData in ProductInfoRight.DefaultIfEmpty()
                                     
                                     select new
                                     {
                                         ProductID = productsRightData.ProductID??0,
                                         ProductName = productsRightData.ProductName ?? null
                                         Description = productsRightData.Description ?? 
                                         Description = productsRightData.Description ?? null,
                                         ImagePath = productsRightData.ImagePath ?? 
                                         ImagePath = productsRightData.ImagePath ?? null,
                                         UnitPrice = productsRightData.UnitPrice ?? 0,
                                         CategoryID = c.CategoryID??0,
                                         CategoryName = c.CategoryName 
                                         UnitPrice = productsRightData.UnitPrice ?? 0,
                                         CategoryID = c.CategoryID??0,
                                         CategoryName = c.CategoryName //from left table
 
                                     }
 
                    ).ToList();
                //union
                //union of left and right join data
                var allProducts = productsLeft.Union(productsRight).ToList();
            }
     }
 
            return productsAll;
        }
        }
 


LINQ Cross Join:

In SQL server a cross joins that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

With LINQ to achieve cross join, no need to use join keyword, using two from clause we can achieve cross join behavior.

Example:

var CrossJoinProducts =        (from p in DbContext.Product
                                
                                from c in DbContext.Category
                                
                                select new
                                {
                                      ProductID = p.ProductID,
                                      ProductName =p.ProductName,
                                      Description =p.Description,
                                      ImagePath = p.ImagePath,
                                      UnitPrice = p.UnitPrice,
                                      CategoryID = (p.CategoryID == null)? 0:p.CategoryID,
                                      CategoryName=c.CategoryName
                                }
 
                     ).ToList();
                                      CategoryName=c.CategoryName
                                }
 
                     ).ToList();
 

Above example returns product of the tables that are involved in the join (product and categories).

 

LINQ self join

In SQL self join is basically when a table is joined to itself. The way you should visualize a self join for a given table is by imagining that a join is performed between two identical copies of that table.

Using the "join" keyword we can do an self join using a LINQ query and table aliases, join returns common or matching records/rows from two identical copies of that table.Check following linq self join example .

Example(Linq Inner self join ):

  /// <summary>
  ///
  /// Method returns user detail with managers from same table using linq self join query 
  /// </summary>
   public List<UserAndManagers> GetUserAndManagers()
     {
          
     {
          List<UserAndManagers> UserManagers = new List<UserAndManagers>();
 
          
 
          using (ApplicationServices DbContext = new ApplicationServices())
          {
 
             
          {
 
             //linq self join query,here two identical copies created u1 and u2 for table USER
              var allUsers = (from u1 in DbContext.Users
                                   
                                   join u2 in DbContext.Users
                                   
                                   on new { PID = u1.ManagerId } equals new { PID = u2.ID }
                                   
                                   select new
                                   {
                                     EmplyeeId=u1.ID,
                                     Emplyeename=  u1.LoginName,
                                     Manager=u2.LoginName
                                   }
                     ).ToList();
                foreach (var u in allUsers)
                {
                    
                {
                    UserAndManagers userDetail = new UserAndManagers();
                    userDetail.userId = u.EmplyeeId;
                    userDetail.UserName = u.Emplyeename;
                    userDetail.ManagerName = u.Manager;
                    UserManagers.Add(userDetail);
                }
 
                
                    userDetail.userId = u.EmplyeeId;
                    userDetail.UserName = u.Emplyeename;
                    userDetail.ManagerName = u.Manager;
                    UserManagers.Add(userDetail);
                }
 
                return UserManagers;
            }
        }
            }
        }
 

unmanaged code advantages and disadvantages

In this article I will try to explain you using unmanaged code advantages and disadvantages.

Unmanaged Code

The program, code written in language that is not supported by .net Framework called as Unmanaged code.  

Code written in C language or C++ which is not supported by .net framework is unmanaged code.

In Detail:

Applications that do not run under the control of the CLR are said to be unmanaged, and certain languages such as C,C++ can be used to write such applications, which, for example, access low - level functions of the operating system. Background compatibility with code of VB, ASP and COM are examples of unmanaged code.

Unmanaged code is executed with help of wrapper classes.

There are two types of Wrapper classes:

1.  CCW (COM Callable Wrapper)
2.  RCW (Runtime Callable Wrapper).

Advantages using:

1.  Easy to learn and write code.
2.  You can execute code in any platform with some helper classes (for example Wrapper classes).
3.  Native code should be faster and have a smaller memory footprint than managed code, reason lot of things are happening on behind with Garbage collection, Type safety.
4. Maintaining native code is easy compared with managed code.

Disadvantages using:

1.  You may face memory leak issue and that’s leads to major performance issues.
2.  Handling explicitly memory management.
 3.  Type safety, array bound and index checking, for example variable data type casting from one to other is hard in unmanaged code.
4.  No default support for exception handling, you require to explicitly handle exceptions.
5. Code security issues.
 

Unmanaged code example:

#include<iostream.h>
#include<stdio.h>
#include<conio.h>
#include<fstream.h>
void main()
{
              char c,myfname[10];
              ofstream out;
              cout<<"Enter File name:";
              cin>> myfname;
              out.open(myfname);
              cout<<"Enter data that’s is to be stored in file (Enter # at end):\n";
              while((c=getchar())!='#')
              {
                            out<<c;
              }
              out.close();
              getch();
}

Choosing managed or unmanaged code:

It is better to use hardware related application using native code.

For Web application, Finance applications better to use managed code.