An Introduction to LINQ

Link to LINQ

1. What is LINQ?
   
   LINQ stands for Language INtegrated Query. Means query language integrated with Microsoft .NET supporting languages i.e. C#.NET, VB.NET, J#.NET etc. Need not to write\ use explicit Data Access Layer.
   
   Writing Data Access Layer require much proficiency as a Data Access Layer should be capable of at least
   1. Efficient Extraction (Select) \ Add\ Update\ Delete of data.
   2. Support to multiple database, ORACLE\ SQL Server\ MySQL etc.
   3. Transaction Management.
   4. Logging\ Tracing
   5. And many more.

   LINQ enables you to use all the above features in very simple and efficient way with very less codes.
   

2. Why LINQ? \ What are benefits of LINQ?
   
   A simple architecture of any software is like
   
   
    
3. What is LINQ entity class?
   
   A Microsoft DOT NET class which map you to or from database. This class provides you flexibility to access database in very efficient way.
   
   Usually LINQ entity class contains that many number of partial classes how many tables are present into the database. Each partial class contains properties same as columns present into the database table. Each instance of the entity class acts as a single row.
   
4. How to generate LINQ Entity class?
   
   .NET provides a simple utility SQLMetal.exe to generate the LINQ Entity class.
   By default, the SQLMetal file is located at
   
   Drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin
   
   Follow the steps below to generate LINQ entity class-
• Start - > Run
• Write cmd and click on "OK" button.
• Go to the location drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin
• Type

sqlmetal /server:<SERVER NAME> /database:<DATABASE NAME> /namespace:<NAMESPASE> /code:<GENERATED CODE LOCATION> / language:csharp

Example:
sqlmetal /server:myserver /database:northwind /namespace:nwind /code:nwind.cs /language:csharp



SQLMetal to generate LINQ entity class

Start using LINQ:

Suppose we have a simple database containing three tables with Structure/ Relations as follows-



Relational Database Diagram For Sample Database

Generate the LINQ Entity class for the above database (Use step 4.0 to generate entity class)
Add the newly created entity class to your project. For a better architecture LINQ Entity class should be placed into separate class library.

Create instance of the LINQ Entity class. There are various overloads of LINQ entity class.

Use the overload of the entity class which accepts Connection String as an argument.

LINQEntityClass objEntityClass = new LINQEntityClass(Configurations.ConnectionString);

1. 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};
   

2. How to Update a Record?
   
   Employees employee = objEntityClass.Employees.Single(emp => emp.EmpId == 1);
   employee.FirstName = "Ashish";
   objEntityClass.SubmitChanges();
    
3. 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();
    
4. 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();
   
               }

5. 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
               }
         }
    
6. 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
    
   • Start - > Run
   • Write cmd and click on "OK" button.
   • Go to the location drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin
   • Type

   sqlmetal /server:<SERVER NAME> /database:<DATABASE NAME> /sprocs /namespace:<NAMESPASE> /code:<GENERATED CODE LOCATION> / language:csharp

   
   
   Note:

   1. 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
   2. 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.
   3. 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.

7. How to use Source Code Provided?
   
   Follow the steps below to use the Source Code provided along with the article to understand LINQ
   1. xRestore SQL Server Database Backup with the name LINQ
   2. Modify App.Config file with the suitable connection string
      
      
       
   3. Now you can double click LINQSample.sln file to open the solution.