SQL and Entity Framework Equivalents cheat sheet
SELECT
SQL
SELECT * FROM Employee;
Entity Framework (LINQ Query)
var employees = context.Employees.ToList();
SELECT DISTINCT
SQL
SELECT DISTINCT DepartmentId FROM Employee;
Entity Framework (LINQ Query)
var distinctDepartmentIds = context.Employees .Select(e => e.DepartmentId) .Distinct() .ToList();
WHERE
SQL
SELECT * FROM Employee WHERE Salary > 70000;
Entity Framework (LINQ Query)
var highSalaryEmployees = from e in context.Employees
where e.Salary > 70000
select e;
LINQ Lambda
var highSalaryEmployees = context.Employees
.Where(e => e.Salary > 70000)
.ToList();
ORDER BY (Descending)
SQL
SELECT * FROM Employee ORDER BY Salary DESC;
Entity Framework (LINQ Query)
var orderedBySalary = from e in context.Employees
orderby e.Salary descending
select e;
LINQ Lambda
var orderedBySalary = context.Employees
.OrderByDescending(e => e.Salary)
.ToList();
AND Operator
SQL
SELECT * FROM Employee WHERE Salary > 70000 AND DepartmentId = 1;
Entity Framework (LINQ Query)
var filteredEmployees = from e in context.Employees
where e.Salary > 70000 && e.DepartmentId == 1
select e;
LINQ Lambda
var filteredEmployees = context.Employees
.Where(e => e.Salary > 70000 && e.DepartmentId == 1)
.ToList();
OR Operator
SQL
SELECT * FROM Employee WHERE Salary > 70000 OR DepartmentId = 3;
Entity Framework (LINQ Query)
var filteredEmployees = from e in context.Employees
where e.Salary > 70000 || e.DepartmentId == 3
select e;
LINQ Lambda
var filteredEmployees = context.Employees
.Where(e => e.Salary > 70000 || e.DepartmentId == 3)
.ToList();
NOT Operator
SQL
SELECT * FROM Employee WHERE NOT DepartmentId = 2; SELECT * FROM Employee WHERE DepartmentId != 2; SELECT * FROM Employee WHERE DepartmentId <> 2;
Entity Framework (LINQ Query)
var filteredEmployees = from e in context.Employees
where e.DepartmentId != 2
select e;
LINQ Lambda
var filteredEmployees = context.Employees
.Where(e => e.DepartmentId != 2)
.ToList();
INSERT INTO
SQL
INSERT INTO Employee (Name, DepartmentId, Salary, HireDate)
VALUES ('Eve', 2, 68000, '2024-07-27');
Entity Framework (LINQ Query)
var newEmployee = new Employee
{
Name = "Eve",
DepartmentId = 2,
Salary = 68000,
HireDate = DateTime.Parse("2024-07-27")
};
context.Employees.Add(newEmployee);
context.SaveChanges();
NULL VALUES
SQL
SELECT * FROM Employee WHERE HireDate IS NULL;
Entity Framework (LINQ Query)
var employeesWithNullHireDate = from e in context.Employees
where e.HireDate == null
select e;
LINQ Lambda
var employeesWithNullHireDate = context.Employees
.Where(e => e.HireDate == null)
.ToList();
UPDATE
SQL
UPDATE Employee SET Salary = 85000 WHERE EmployeeId = 2;
Entity Framework (LINQ Query)
// Retrieve the entity
var employee = context.Employees
.FirstOrDefault(e => e.EmployeeId == 2);
// Modify the field
if (employee != null)
{
employee.Salary = 85000;
}
context.SaveChanges();
DELETE
SQL
DELETE FROM Employee WHERE EmployeeId = 4;
Entity Framework (LINQ Query)
var employeeToDelete = context.Employees.Find(4);
if (employeeToDelete != null)
{
context.Employees.Remove(employeeToDelete);
context.SaveChanges();
}
SELECT TOP
SQL
SELECT TOP 2 * FROM Employee ORDER BY Salary DESC;
Entity Framework (LINQ Query)
var topEmployees = (from e in context.Employees
orderby e.Salary descending
select e).Take(2).ToList();
LINQ Lambda
var topEmployees = context.Employees
.OrderByDescending(e => e.Salary)
.Take(2)
.ToList();
AGGREGATE FUNCTIONS (MIN, MAX, COUNT, SUM, AVG)
SQL
--MIN SELECT MIN(Salary) AS MinSalary FROM Employee; --MAX SELECT MAX(Salary) AS MaxSalary FROM Employee; --COUNT SELECT COUNT(*) AS TotalEmployees FROM Employee; --SUM SELECT SUM(Salary) AS TotalSalary FROM Employee; --AVG SELECT AVG(Salary) AS AverageSalary FROM Employee;
Entity Framework (LINQ Query)
var minSalary = (from e in context.Employees
select e.Salary).Min();
var maxSalary = (from e in context.Employees
select e.Salary).Max();
var totalEmployees = (from e in context.Employees
select e).Count();
var totalSalary = (from e in context.Employees
select e.Salary).Sum();
var averageSalary = (from e in context.Employees
select e.Salary).Average();
LINQ Lambda
var minSalary = context.Employees
.Min(e => e.Salary);
var maxSalary = context.Employees
.Max(e => e.Salary);
var totalEmployees = context.Employees.Count();
var totalSalary = context.Employees
.Sum(e => e.Salary);
var averageSalary = context.Employees
.Average(e => e.Salary);
LIKE
SQL
SELECT * FROM Employee WHERE Name LIKE '%o%';
Entity Framework (LINQ Query)
var employeesWithO = from e in context.Employees
where e.Name.Contains("o")
select e;
LINQ Lambda
var employeesWithO = context.Employees
.Where(e => e.Name.Contains("o"))
.ToList();
WILDCARDS
SQL
SELECT * FROM Employee WHERE Name LIKE 'C%';
Entity Framework (LINQ Query)
var employeesStartingWithC = from e in context.Employees
where e.Name.StartsWith("C")
select e;
LINQ Lambda
var employeesStartingWithC = context.Employees
.Where(e => e.Name.StartsWith("C"))
.ToList();
IN
SQL
SELECT * FROM Employee WHERE DepartmentId IN (1, 3);
Entity Framework (LINQ Query)
var employeesInDepartments = from e in context.Employees
where e.DepartmentId == 1 || e.DepartmentId == 3
select e;
LINQ Lambda
var departmentIds = new[] { 1, 3 };
var employeesInDepartments = context.Employees
.Where(e => departmentIds.Contains(e.DepartmentId))
.ToList();
BETWEEN
SQL
SELECT * FROM Employee WHERE Salary BETWEEN 70000 AND 75000;
Entity Framework (LINQ Query)
var salaryRangeEmployees = from e in context.Employees
where e.Salary >= 70000 && e.Salary <= 75000
select e;
LINQ Lambda
var salaryRangeEmployees = context.Employees
.Where(e => e.Salary >= 70000 && e.Salary <= 75000)
.ToList();
ALIASES
SQL
SELECT Name AS EmployeeName, Salary FROM Employee AS E;
Entity Framework (LINQ Query)
var employeeNamesAndSalaries = from e in context.Employees
select new
{
EmployeeName = e.Name,
e.Salary
};
LINQ Lambda
var employeeNamesAndSalaries = context.Employees
.Select(e => new
{
EmployeeName = e.Name,
e.Salary
})
.ToList();
JOIN
SQL
SELECT e.EmployeeId, e.Name, d.DepartmentName FROM Employee e INNER JOIN Department d ON e.DepartmentId = d.DepartmentId;
Entity Framework (LINQ Query)
var employeeDepartments = from e in context.Employees
join d in context.Departments
on e.DepartmentId equals d.DepartmentId
select new
{
e.EmployeeId,
e.Name,
d.DepartmentName
};
LINQ Lambda
var employeeDepartments = context.Employees
.Join(context.Departments,
e => e.DepartmentId,
d => d.DepartmentId,
(e, d) => new
{
e.EmployeeId,
e.Name,
d.DepartmentName
})
.ToList();
LEFT JOIN
SQL
SELECT e.EmployeeId, e.Name, d.DepartmentName FROM Employee e LEFT JOIN Department d ON e.DepartmentId = d.DepartmentId;
Entity Framework (LINQ Query)
var employeeDepartments = from e in context.Employees
join d in context.Departments
on e.DepartmentId equals d.DepartmentId into empDept
from d in empDept.DefaultIfEmpty()
select new
{
e.EmployeeId,
e.Name,
DepartmentName = d != null ? d.DepartmentName : null
};
LINQ Lambda
var employeeDepartments = context.Employees
.GroupJoin(context.Departments,
e => e.DepartmentId,
d => d.DepartmentId,
(e, deptGroup) => new { e, deptGroup })
.SelectMany(x => x.deptGroup.DefaultIfEmpty(),
(x, d) => new
{
x.e.EmployeeId,
x.e.Name,
DepartmentName = d != null ? d.DepartmentName : null
})
.ToList();
RIGHT JOIN
SQL
SELECT e.EmployeeId, e.Name, d.DepartmentName FROM Employee e RIGHT JOIN Department d ON e.DepartmentId = d.DepartmentId;
Entity Framework (LINQ Query)
var departmentsEmployees = from d in context.Departments
join e in context.Employees
on d.DepartmentId equals e.DepartmentId into deptEmp
from e in deptEmp.DefaultIfEmpty()
select new
{
e != null ? e.EmployeeId : (int?)null,
e != null ? e.Name : null,
d.DepartmentName
};
LINQ Lambda
var departmentsEmployees = context.Departments
.GroupJoin(context.Employees,
d => d.DepartmentId,
e => e.DepartmentId,
(d, empGroup) => new { d, empGroup })
.SelectMany(x => x.empGroup.DefaultIfEmpty(),
(x, e) => new
{
EmployeeId = e != null ? e.EmployeeId : (int?)null,
Name = e != null ? e.Name : null,
x.d.DepartmentName
})
.ToList();
FULL JOIN
SQL
SELECT e.EmployeeId, e.Name, d.DepartmentName FROM Employee e FULL OUTER JOIN Department d ON e.DepartmentId = d.DepartmentId;
Entity Framework (LINQ Query)
var leftJoin = from e in context.Employees
join d in context.Departments
on e.DepartmentId equals d.DepartmentId into empDept
from d in empDept.DefaultIfEmpty()
select new
{
e.EmployeeId,
e.Name,
DepartmentName = d != null ? d.DepartmentName : null
};
var rightJoin = from d in context.Departments
join e in context.Employees
on d.DepartmentId equals e.DepartmentId into deptEmp
from e in deptEmp.DefaultIfEmpty()
select new
{
EmployeeId = e != null ? e.EmployeeId : (int?)null,
Name = e != null ? e.Name : null,
d.DepartmentName
};
var fullJoin = leftJoin.Union(rightJoin).ToList();
SELF JOIN
SQL
SELECT e1.EmployeeId AS EmployeeId1, e1.Name AS Name1, e2.EmployeeId AS EmployeeId2, e2.Name AS Name2 FROM Employee e1 INNER JOIN Employee e2 ON e1.DepartmentId = e2.DepartmentId AND e1.EmployeeId != e2.EmployeeId;
Entity Framework (LINQ Query)
var selfJoin = from e1 in context.Employees
join e2 in context.Employees
on e1.DepartmentId equals e2.DepartmentId
where e1.EmployeeId != e2.EmployeeId
select new
{
EmployeeId1 = e1.EmployeeId,
Name1 = e1.Name,
EmployeeId2 = e2.EmployeeId,
Name2 = e2.Name
};
LINQ Lambda
var selfJoin = context.Employees
.Join(context.Employees,
e1 => e1.DepartmentId,
e2 => e2.DepartmentId,
(e1, e2) => new { e1, e2 })
.Where(x => x.e1.EmployeeId != x.e2.EmployeeId)
.Select(x => new
{
EmployeeId1 = x.e1.EmployeeId,
Name1 = x.e1.Name,
EmployeeId2 = x.e2.EmployeeId,
Name2 = x.e2.Name
})
.ToList();
UNION
SQL
SELECT Name FROM Employee UNION SELECT DepartmentName AS Name FROM Department;
Entity Framework (LINQ Query)
var employeeNames = context.Employees
.Select(e => e.Name);
var departmentNames = context.Departments
.Select(d => d.DepartmentName);
var allNames = employeeNames.Union(departmentNames).ToList();
LINQ Lambda
var employeeNames = context.Employees
.Select(e => e.Name);
var departmentNames = context.Departments
.Select(d => d.DepartmentName);
var allNames = employeeNames
.Union(departmentNames)
.ToList();
GROUP BY
SQL
SELECT DepartmentId, COUNT(*) AS NumberOfEmployees FROM Employee GROUP BY DepartmentId;
Entity Framework (LINQ Query)
var employeeCountsByDepartment = from e in context.Employees
group e by e.DepartmentId into g
select new
{
DepartmentId = g.Key,
NumberOfEmployees = g.Count()
};
LINQ Lambda
var employeeCountsByDepartment = context.Employees
.GroupBy(e => e.DepartmentId)
.Select(g => new
{
DepartmentId = g.Key,
NumberOfEmployees = g.Count()
})
.ToList();
HAVING
SQL
SELECT DepartmentId, COUNT(*) AS NumberOfEmployees FROM Employee GROUP BY DepartmentId HAVING COUNT(*) > 1;
Entity Framework (LINQ Query)
var departmentsWithMoreThanOneEmployee = from e in context.Employees
group e by e.DepartmentId into g
where g.Count() > 1
select new
{
DepartmentId = g.Key,
NumberOfEmployees = g.Count()
};
LINQ Lambda
var departmentsWithMoreThanOneEmployee = context.Employees
.GroupBy(e => e.DepartmentId)
.Where(g => g.Count() > 1)
.Select(g => new
{
DepartmentId = g.Key,
NumberOfEmployees = g.Count()
})
.ToList();
EXISTS
SQL
SELECT * FROM Employee WHERE EXISTS (SELECT 1 FROM Department WHERE DepartmentId = Employee.DepartmentId);
Entity Framework (LINQ Query)
var employeesWithDepartments = from e in context.Employees
where context.Departments.Any(d => d.DepartmentId == e.DepartmentId)
select e;
LINQ Lambda
var employeesWithDepartments = context.Employees
.Where(e => context.Departments
.Any(d => d.DepartmentId == e.DepartmentId))
.ToList();
ANY
SQL
SELECT * FROM Employee WHERE Salary > ANY (SELECT Salary FROM Employee WHERE DepartmentId = 1);
Entity Framework (LINQ Query)
var salariesInDepartment1 = (from e in context.Employees
where e.DepartmentId == 1
select e.Salary).ToList();
var employeesWithHigherSalary = from e in context.Employees
where e.Salary > salariesInDepartment1.Max()
select e;
LINQ Lambda
var salariesInDepartment1 = context.Employees
.Where(e => e.DepartmentId == 1)
.Select(e => e.Salary)
.ToList();
var employeesWithHigherSalary = context.Employees
.Where(e => e.Salary > salariesInDepartment1.Max())
.ToList();
ALL
SQL
SELECT * FROM Employee WHERE Salary > ALL (SELECT Salary FROM Employee WHERE DepartmentId = 1);
Entity Framework (LINQ Query)
var salariesInDepartment1 = (from e in context.Employees
where e.DepartmentId == 1
select e.Salary).ToList();
var employeesWithHigherSalary = from e in context.Employees
where e.Salary > salariesInDepartment1.Min()
select e;
LINQ Lambda
var salariesInDepartment1 = context.Employees
.Where(e => e.DepartmentId == 1)
.Select(e => e.Salary)
.ToList();
var employeesWithHigherSalary = context.Employees
.Where(e => e.Salary > salariesInDepartment1.Min())
.ToList();
SELECT INTO
SQL
SELECT * INTO NewEmployeeTable FROM Employee;
Entity Framework (LINQ Query)
var employees = context.Employees.ToList();
// Create a new table and add the data
// Assuming you have a DbSet<NewEmployeeTable> in your context
context.NewEmployeeTable.AddRange(employees.Select(e => new NewEmployeeTable
{
EmployeeId = e.EmployeeId,
Name = e.Name,
DepartmentId = e.DepartmentId,
Salary = e.Salary,
HireDate = e.HireDate
}));
context.SaveChanges();
INSERT INTO SELECT
SQL
INSERT INTO NewEmployeeTable (EmployeeId, Name, DepartmentId, Salary, HireDate) SELECT EmployeeId, Name, DepartmentId, Salary, HireDate FROM Employee;
Entity Framework (LINQ Query)
var employees = context.Employees.ToList();
context.NewEmployeeTable.AddRange(employees.Select(e => new NewEmployeeTable
{
EmployeeId = e.EmployeeId,
Name = e.Name,
DepartmentId = e.DepartmentId,
Salary = e.Salary,
HireDate = e.HireDate
}));
context.SaveChanges();
CASE
SQL
SELECT Name,
CASE
WHEN Salary > 75000 THEN 'High'
WHEN Salary BETWEEN 60000 AND 75000 THEN 'Medium'
ELSE 'Low'
END AS SalaryLevel
FROM Employee;
Entity Framework (LINQ Query)
var employeesWithSalaryLevel = from e in context.Employees
select new
{
e.Name,
SalaryLevel = e.Salary > 75000 ? "High" :
e.Salary >= 60000 && e.Salary <= 75000 ? "Medium" :
"Low"
};
LINQ Lambda
var employeesWithSalaryLevel = context.Employees
.Select(e => new
{
e.Name,
SalaryLevel = e.Salary > 75000 ? "High" :
e.Salary >= 60000 && e.Salary <= 75000 ? "Medium" :
"Low"
})
.ToList();
NULL Function
SQL
SELECT Name, COALESCE(Salary, 0) AS Salary FROM Employee;
Entity Framework (LINQ Query)
var employeesWithSalary = from e in context.Employees
select new
{
e.Name,
Salary = e.Salary.HasValue ? e.Salary.Value : 0
};
LINQ Lambda
var employeesWithSalary = context.Employees
.Select(e => new
{
e.Name,
Salary = e.Salary.HasValue ? e.Salary.Value : 0
})
.ToList();
Pagination
SQL
-- Get page 2 with 10 records per page SELECT * FROM Employee ORDER BY EmployeeId OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Entity Framework (LINQ Query)
int pageNumber = 2;
int pageSize = 10;
var pagedEmployees = context.Employees
.OrderBy(e => e.EmployeeId)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
LINQ Lambda
int pageNumber = 2;
int pageSize = 10;
var pagedEmployees = context.Employees
.OrderBy(e => e.EmployeeId)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
Subquery
SQL
-- Get employees who earn more than the average salary SELECT * FROM Employee WHERE Salary > (SELECT AVG(Salary) FROM Employee);
Entity Framework (LINQ Query)
var averageSalary = context.Employees
.Average(e => e.Salary);
var highEarningEmployees = from e in context.Employees
where e.Salary > averageSalary
select e;
LINQ Lambda
var averageSalary = context.Employees
.Average(e => e.Salary);
var highEarningEmployees = context.Employees
.Where(e => e.Salary > averageSalary)
.ToList();