What LINQ is

In this article, we will learn the following points:

• What LINQ is
• Why we should we use it
• LINQ Architecture
• LINQ Provider
• DEMO without and with LINQ

LINQ

Language Integrated Query (LINQ) enables us to query any type of data stored in SQL Server, XML documents, List, Arrays and so on.

Why we should use LINQ

Using LINQ one can work with various data sources using a similar code style without having the need to know the syntax specific to the data source.

It provides compile-time error checking and intelligence.

LINQ can be written using any .NET supported language.

LINQ Architecture



Role of the LINQ Provider

A LINQ Provider is a component between a LINQ query and the data source that converts the LINQ query into the format that the data source can understand.

For example, the LINQ to SQL provider converts a LINQ query to T-SQL that the SQL Server database can understand.

We will discuss each of the preceding providers one by one in the future articles of this series.

Demo

Let's look at an example without using LINQ.

In SQL Server, I have a database named “db_StudentEntity” in which there is a table named “tblStudent” with five student records.



We will use the preceding table records in our console application.

In the table above, we have three different columns, in other words StudentId, StudentName and StudentGender.

So, first let’s add a new class file and name it “Student”. In that class file add three auto-implemented properties.

Add a class file as in the following:



Give it the name “Student.cs”.



Click Add.

Your class will look like this.



Write the following code inside your Student class.

using System;
namespace WhatIsLinq {
    class Student {
        public int StudentId { get; set; }
        public string StudentName { get; set; }
        public string StudentGender { get; set; }
    }
}

So, we have created our Student class and added some properties that will hold the data that we will get from the database.

The next step is to add the connection string in the app.config file.

In the ASP.NET web application, we specify the connection string in the web.config file but in the Console and Windows application, we specify the connection strings in the app.config file.

<connectionStrings>
  <add name="DBCS" connectionString="server=.; database=db_studentEntity; Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>

Where the name DBCS is a key or the index value(0) that we will retrieve the connection string in our main project using the ConfigurationManager class.

Integrated Security: Means we are using Windows authentication to connect to the SQL Server.



Until now we have created our Student class and we have also specified the connection string.

The next step is to write some ADO.NET code in our main class Program.cs.

Since we are connecting to the SQL Server database. So, we will import the System.Data.SqlClient namespace.

We will also import the System.Configuration namespace because there is a class named “ConfigurationManager” that will help us in retrieving the connection string from the app.config file. But to use this class, first we need to add a reference to our project.



Select and Click OK.

In the Program.cs, create a new method whose return type will be List<Student> and in that we will write the following code:

public List<Student> GetStudentDetails(){
   List<Student> studentList = new List<Student>();
          //retrieve the connection string
          string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

      }

Note

The ConnectionStrings (with an “s”) is used to get the specified string name or the index value connection string from the app.config file and the ConnectionString (without the “s”) property returns the required credentials.



The next step is to add a new SqlConnection object.

public void GetStudentDetails() {
  string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            //create an instance of the SqlConnection class and pass the string cs variable (which holds the connection string data) as a parameter argument in the SqlConnection constructor
            using (SqlConnection con = new SqlConnection(cs)) {

            }
        }

Note

If you use the using keyword then you won’t need to close the connection in the end because the connection is closed automatically when the using block is out of scope.

The next step is to create a new SQLCommand object.

//create a sqlcommand object
SqlCommand cmd = new SqlCommand();
//pass the query that you want to execute using con SqlConnection
cmd.CommandText = "SELECT StudentId,StudentName, StudentGender FROM tblStudent";
cmd.Connection = con;//using this con SqlConnection object, the above query will execute.

Then we must open the connection using the Open method.

//open the connection
con.Open();

We will invoke the ExecuteReader method that will send the command text to the SQLConnection that returns a SQLDataReader object back from where we can read all the records of the table.

/*the ExecuteReader is used to when you want to retrieve data from the database and the return type of ExecuteReader() is SqlDataReader object.
 */
SqlDataReader rdr = cmd.ExecuteReader();

The next step is to read the data and assign it to the Student properties.

//it is always a good practice to read the records one by one before doing something with it and for that we can use Read() Boolean method
while (rdr.Read()) {
    //create the Student object and assign the values
    Student student = new Student();
    student.StudentId = Convert.ToInt32(rdr["StudentId"]);
    student.StudentName = (rdr["StudentName"]).ToString();
    student.StudentGender = (rdr["StudentGender"]).ToString();
    studentList.Add(student);
}
return studentList;

Return studentList;

The entire GetStudentDetails look like this.

public List<Student> GetStudentDetails() {
   List<Student> studentList = new List<Student>();
   string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

      using (SqlConnection con = new SqlConnection(cs)) {

         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "SELECT StudentId,StudentName, StudentGender FROM tblStudent";
         cmd.Connection = con;
         con.Open();
         SqlDataReader rdr = cmd.ExecuteReader();
         while (rdr.Read()) {
            Student student = new Student();
            student.StudentId = Convert.ToInt32(rdr["StudentId"]);
            student.StudentName = (rdr["StudentName"]).ToString();
            student.StudentGender = (rdr["StudentGender"]).ToString();
            studentList.Add(student);
         }
         return studentList;
      }
   }
}

The next step and the final step is to create an instance of our Program class in our Main method and we will invoke the GetStudentDetails() method that will return a List<Student> back and then we will loop through each Student.

static void Main(string[] args) {
    Program p = new Program();
    List<Student> students = p.GetStudentDetails();
    Console.Write("Id");
    Console.Write("\t" + "\t");
    Console.Write("Name");
    Console.Write("\t" + "\t");
    Console.Write("Gender");
    Console.Write("\t" + "\t");
    Console.WriteLine();
    Console.WriteLine();
    foreach (Student s in students) {
        Console.Write(s.StudentId);
        Console.Write("\t" + "\t");
        Console.Write(s.StudentName);
        Console.Write("\t" + "\t");
        Console.Write(s.StudentGender);
        Console.WriteLine();
    }
}

Run the application.



We have retrieved the records from the table and printed it on the console window.

But there is a problem in our application. In the following command, we don’t get any intelligence. What will happen if I change StudentId to sId?

cmd.CommandText = "SELECT StudentId,StudentName, StudentGender FROM tblStudent";

We will get an exception.



Now let’s look at the same example but this time we will use LINQ.

The first step is to add a LINQ to SQL provider in our project and for that right-click on the project then select Add -> new item.



Under the visual C# item, select Data and choose LINQ to SQL classes.



Click Add.

It will give you this blank window.



Click on the Server Explorer link. It will open the Server Explorer window.

Click on the marked section that will allow you to connect to the database.



Specify the server name and select the database.



Click OK.

You will see in the Solution Explorer that the data connection has been established.

Expand the Data Connection then expand win-db_StudentIdentity then expand Tables then select tblStudent then drag and drop it to the blank window.



That will provide us the following layout in the Student.dbml:



From the Solution Explorer, if you open Student.Designer.cs, you will see a partial class StudentDataContext. This class is the gateway to the database. We need to create the instance of this class in order to retrieve the data from the database.



But first remove the GetStudentDetails method from the Program.cs.

Write the following highlighted code in the main method.

Import the System.Linq namespace to write the LINQ query.

class Program {
static void Main(string[] args) {
    StudentDataContext studentDataContext = new StudentDataContext();
    var students = from stud in studentDataContext.tblStudents
                   select stud;
    Program p = new Program();

    Console.Write("Id");
    Console.Write("\t" + "\t");
    Console.Write("Name");
    Console.Write("\t" + "\t");
    Console.Write("Gender");
    Console.Write("\t" + "\t");
    Console.WriteLine();
    Console.WriteLine();
    foreach (var s in students) {
        Console.Write(s.StudentId);
        Console.Write("\t" + "\t");
        Console.Write(s.StudentName);
        Console.Write("\t" + "\t");
        Console.Write(s.StudentGender);
        Console.WriteLine();
    }
}

Run the application:



You can even retrieve data based on some conditions using the where clause and now you even get the intelligence.

 

StudentDataContext studentDataContext = new StudentDataContext();
var students = from stud in studentDataContext.tblStudents where stud.StudentGender == "Female" select stud;

So, we have seen how simple and easy it is to work with LINQ and what role is played by the LINQ provider.

Later articles of this series explains some important extension methods that are useful.

Until then keep learning.
Thank you