Linq Query || Joins

Best Linq Link by Chitranjan daas
https://vslapp.files.wordpress.com/2011/11/linq-cheatsheet.pdf


Left join
from emp in entity.EMployee.Where(i => i.IsActive == true)
 from deptin entity.dept.Where(i => i.deptno == emp.deptno).DefaultIfEmpty()
 select new tbl_Employee
{


}
==========================================
Nice Query MUST WATCH

select * from
Employee TR
and TR.id in(
   select Max(id) from
     Employee TRR where TRR.Deptid=Tr.Deptid
)

 OR CAN WE WRITTEN AS IN SQL SERVER
============================
select * from emp e1 where e1.Empno in(
   select max(empno) from emp e2
   group by e2.Deptno
)
=============================

TopicList = entity.Employee.Where(k =>   k.Id == entity.Employee.
                                               Where(m => m.Deptid == k.Deptid)

                                              .Max(m => m.Id)).Select(c => c).ToList();

=======================================================
SubQUery


SELECT     ID, Name, Phone
FROM         Employee
WHERE     (DepartmentID IN
                          (SELECT     ID
                            FROM          Department
                            WHERE      (Name = 'IT')))


var emp = from e in dbContext.Employees
where dbContext.Departments.Any(d => d.ID == e.DepartmentID && d.Name.Equals("IT"))
          select new { e.ID, e.Name, e.Phone};

===========================================
Note:Right click your solution/project. Click Add Reference and search for System.Data.Linq and add the reference there and it should compile.

 using System.Data.Linq.SqlClient;
SELECT *
FROM Users
WHERE Users.lastname LIKE '%fra%'
    AND Users.Id IN (
         SELECT UserId
         FROM CompanyRolesToUsers
         WHERE CompanyRoleId in (2,3,4) )

List<int> IdsToFind = new List<int>() {2, 3, 4};

db.Users
.Where(u => SqlMethods.Like(u.LastName, "%fra%"))
.Where(u =>
    db.CompanyRolesToUsers
    .Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId))
    .Select(crtu =>  crtu.UserId)
    .Contains(u.Id)
)

==================================================
SELECT f.*
FROM Foo f
WHERE f.FooId IN (
    SELECT fb.FooId
    FROM FooBar fb
    WHERE fb.BarId = 1000
)

var innerQuery = from fb in FoorBar where fb.BarId = 1000 select fb.FooId;
var result = from f in Foo where innerQuery.Contains(f.FooId) select f;
===================================================
LIKE Operator
 using System.Data.Linq.SqlClient;
Select * from table name where columnname like 'ab_c'

var emplist = from emp in dbcontext.Employees
where SqlMethods.Like(emp.FirstName, "pr_nay")
select emp;


All Date Methods
https://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods(v=vs.110).aspx

DateDiffDay(DateTime, DateTime)

  pc = db.ProductCategories.Where(o =>
SqlMethods.DateDiffYear(o.ModifiedDate, DateTime.Now) > 1).FirstOrDefault();
}

===============================================
Join  
Best Link for joins:  -------          http://www.dotnettricks.com/learn/linq/sql-joins-with-csharp-linq

Inner Join

SELECT     E.ID, E.Name, E.Phone, D.Name AS Department
FROM         Employee AS E INNER JOIN
                      Department AS D ON E.DepartmentID = D.ID


var emp = (from e in dbContext.Employees
                       join d in dbContext.Departments
                       on e.DepartmentID equals d.ID
                       select new { e.ID, e.Name, e.Phone, Department = (d.Name) });
-----------------------------------------------------------------------------------------

Left Join

SELECT     E.ID, E.Name, E.Phone, D.Name AS Department
FROM         Employee AS E LEFT JOIN
                      Department AS D ON E.DepartmentID = D.ID


var emp = (from e in dbContext.Employees
        join d in dbContext.Departments on e.DepartmentID equals d.ID into dj
        from d in dj.DefaultIfEmpty()
        select new { e.ID, e.Name, e.Phone, Department = (d.Name)});


---------------------------------------------------------------------------------------
Right Join


SELECT     E.ID, E.Name, E.Phone, D.Name AS Department
FROM         Employee AS E RIGHT OUTER JOIN
                      Department AS D ON E.DepartmentID = D.ID


var emp = (from d in dbContext.Departments
          join e in dbContext.Employees on d.ID equals e.DepartmentID into ej
          from e in ej.DefaultIfEmpty()
          select new {e.Name, e.Phone, Department = (d.Name) });

-------------------------------------------------------------------------------------
LINQ to SQL – Select DISTINCT Values


SELECT DISTINCT Phone, DepartmentID
FROM         Employee



var emp = (from e in dbContext.Employees
          select new { e.Phone, e.DepartmentID}).Distinct();