-
How to select a record?
Create instance of LINQ Entity Class
LINQEntityClass objEntityClass = new LINQEntityClass(Configurations.ConnectionString);
1.1 How to select all Columns and all the records?
var employees = from emp in objEntityClass.Employees
select emp;
1.2 How to use where clause?
var employees = from emp in objEntityClass.Employees
where emp.FirstName == "Ashish"
select emp;
1.3 How to select particular columns only?
var employees = from emp in objEntityClass.Employees
select new {emp.EmpId, emp.FirstName};
1.4 Display EmpId, FirstName, Designation and Department of the employee.
If we want to select the above record without using LINQ then we will have to Join Employee table with Department and Designation tables and the Sql will look like
Select emp.EmpId, emp.Firstname, designation.Designation, dept.Deptname
FROM Employees emp, Designations designation, Departments dept
WHERE emp.DesignationId = designation.DesignationId
AND emp.DeptId = dept.DeptId
With the help of LINQ we can obtain the same result very easily
var employees = from emp in objEntityClass.Employees
select new
{emp.EmpId, emp.FirstName, emp.Departments.DeptName,
emp.Designations.Designation};
1.5 How to use alias for any column name?
var employees = from emp in objEntityClass.Employees
select new
{ID = emp.EmpId, Name = emp.FirstName};
1.6 How to bind LINQ data with ASP.NET GridView?
var employees = from emp in
objEntityClass.Employees
select new
{ID = emp.EmpId, Name = emp.FirstName};
//Bind the data with Grid View
gvData.DataSource = employees;
1.7 How to use joins?
var employees = from emp in objEntityClass.Employees
join dept in objEntityClass.Departments
on emp.DeptId equals dept.DeptId
join desig in objEntityClass.Designations
on emp.DesignationId equals desig.DesignationId
select new {ID = emp.EmpId, Name = emp.FirstName};
-
How to Update a Record?
Employees employee = objEntityClass.Employees.Single(emp => emp.EmpId == 1);
employee.FirstName = "Ashish";
objEntityClass.SubmitChanges();
-
How to Delete a Record?
//Select the record want to Delete
Employees employee = objEntityClass.Employees.Single(emp => emp.EmpId == 6);
objEntityClass.Employees.DeleteOnSubmit(employee);
objEntityClass.SubmitChanges();
-
How to use Transactions with LINQ?
DbTransaction trx = null;
trx = GetDBTransaction();
objEntityClass.Transaction = trx;
//Add new Department
Departments department = AddDepartments();
//Add New Designation
Designations designation = AddDesignations();
//Use the IDs of Newly Created Dept and Designation to create a New Employee
Employees emp = new Employees();
emp.EmpCode = "EMP00T";
emp.FirstName = "Transaction";
emp.LastName = "Employee";
emp.Email = "[email protected]";
emp.DesignationId = designation.DesignationId;
emp.DeptId = department.DeptId;
emp.ManagerId = 0;
emp.Address = "Transaction Enabled Employee, India";
try
{
objEntityClass.Employees.InsertOnSubmit(emp);
objEntityClass.SubmitChanges();
trx.Commit();
}
catch
{
if(trx != null)
trx.Rollback();
}
-
How to Iterate / Loop through the records?
Iteration of record is very much needed for any developer to implement any software or requirement. Main drawback with the traditional iteration is the dependency on the database table structure. If any columns position (index) is changed entire iteration logic is affected. Using LINQ for iteration help us to overcome from the explained problem.
var employees = from emp in objEntityClass.Employees
select emp;
foreach (var employee in employees)
{
if (employee.FirstName != "")
{
//Do Operation
}
}
-
How to execute or use Stored Procedures?
6.1 Generate Entity Class for Stored Procedures
In order to use Stored Procedures using LINQ you need to create entity classes for the stored procedures in the same way created the entity class for the tables.
Follow the steps below to create LINQ Entity class for Stored Procedures
sqlmetal /server:<SERVER NAME> /database:<DATABASE NAME> /sprocs /namespace:<NAMESPASE> /code:<GENERATED CODE LOCATION> / language:csharp
Note:
-
If you have created Database Diagram then above command will fail to generate the entity class for the Stored Procedures. You need to create a new table into your database with the name dtproperties. This table will contain following columns
-
Above class will contain system stored procedures also. So far it was not possible avoid including system Stored Procedures. May be into recent releases of SQLMetal.exe we may get this flexibility.
-
Using /sprocs will generate the complete entity class which will include Stored Procedures as well as database tables.
6.2 Execute Stored Procedures
Now your newly created entity class will contain a method with the name same as the stored procedure name. You simply need to call the method
var employees = objEntityClass.GetEmployeeByName("");
foreach (var emp in employees)
{
if (emp.EmpCode != "")
{
//Do Operation
}
}
In the example shown above GetEmployeeByName is the name of the Stored Procedure which accepts name of the employee as an argument.
-
How to use Source Code Provided?
Follow the steps below to use the Source Code provided along with the article to understand LINQ
-
xRestore SQL Server Database Backup with the name LINQ
-
Modify App.Config file with the suitable connection string
-
Now you can double click LINQSample.sln file to open the solution.