An Introduction to LINQ

Link to LINQ

linq1.gif

  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

    linq2.gif
     
  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

    linq3.gif

    SQLMetal to generate LINQ entity class

    Start using LINQ:

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

    linq4.gif

    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

      linq5.gif

      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.

        linq6.gif

      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

        linq7.gif
         
      3. Now you can double click LINQSample.sln file to open the solution.


Similar Articles