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