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 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 == null)? null:p.Description,
                                      ImagePath = (p.ImagePath == null)? null:p.ImagePath,
                                      UnitPrice = (p.UnitPrice == null)? 0:p.UnitPrice,
                                      CategoryID = (p.CategoryID == null)? 0:p.CategoryID
                                   
                                }
 
                     ).ToList();
 

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();
 
 
                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);
                }
            }
            return ProductCategories;
 
        }
        }
 


Inner join with multiple tables Example:

using (ApplicationServices DbContext = new ApplicationServices())
            {
                var AllLeaves = (from lt in DbContext.Leave
                                 join u in DbContext.User
                                 on new { PID =lt.UserId } equals new { PID = u.UserId }
                                 join l in DbContext.LeaveType
                                 on new { PID = lt.LeaveType } equals new { PID = l.TypeId }
                                 join us in DbContext.User
                                 on new { PID = lt.SanctionAuthority } equals new { PID = us.UserId }
                                 select new
                                 {
                                     leaveId = lt.LeaveId,
                                     fromDate = lt.FromDate,
                                     toDate = lt.ToDate,
                                     updateBy = lt.UpdateBy,
                                     updateDate = lt.UpdateDate,
                                     type = lt.Type,
                                     leaveReason = lt.LeaveReason,
                                     remark = lt.Remark,
                                     numberOfDays = lt.NumberOfDays,
                                     sanctionAuthority = lt.SanctionAuthority,
                                     optionalSanctionAuthority = lt.OptionalSanctionAuthority,
                                     attchId = lt.AttchId,
                                     toCC = lt.ToCC,
                                     user = u.UserName,
                                     typename = l.LeaveTitle,
                                     leavecat = lt.LeaveCat,
                                     sancrtionuser = us.UserName,
                                     status=lt.LeaveStatus
                                 }
 
                     ).ToList();