LINQ (Language Integrated Query)
The queries written in SQL are not understandable by C# compiler. If there are errors in an SQL query, then the C# compiler is not able to detect it, and it simply compiles the SQL query irrespective of whether it is correct or incorrect. For resolving this problem, LINQ was developed for C#. Now LINQ is integrated with C# compiler. So, when the programmer or user makes a mistake in a query the C# compiler detects that mistake and the program is not compiled until the mistake in the query is not corrected. LINQ can be used to query databases, XML files, and Class objects.
Types of LINQ queries:
There are three types of LINQ queries in C#:
- LINQ to Object
- LINQ to DataSet
- LINQ to XML
Language Integrated Query to Object:
This type of query is used to query Collections in C# such as Dictionary, ArrayList, Queue, and Stack. To explain this type of query let us consider the following example:
Now in this example, we are demonstrating Array List in which we are applying LINQ.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
class NumberSelection { static void Main(string[] args) { int[] set = { 1,5,4,2,7,6,4,0}; //In the above line an array of int is defined and the name of that array is set and the data members of this array are defined in the {}. var subSet= from n in set where n < 5 select n; //LINQ needs to be stored in a variable. Now in this example the LINQ is stored in varsubset and the LINQ in this variable is defined as from n in set where n < 5 select n;. Now in this query n is the array of numbers in which only those numbers n will be selected which are <5. Console.WriteLine("Numbers < 5:"); //This line will only print Numbers < 5:. foreach(int n in subSet) //This loop foreach will run according to the LINQ defined in the variable subset (intn in subSet). { Console.Write(n); // This line will display the output of the line foreach(intn in subSet) } Console.ReadLine(); } } |
LINQ Employee Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
public void LinqExample2() { Employee[] employee= new Employee[4]; //The above mentioned line is making an array of Employee where employee is the instance of that array. = new Employee[4]; is assigning an array and giving this array the size of [4].// employee[0]= new Employee("asad", "3434-234344-3", 20, 6); //This line is showing that at index [0] this data is to be saved = new Employee("asad", "3434-234344-3", 20, 6);.// employee[1]= new Employee("khalid", "2323-3454322-3", 44, 5); employee[2]= new Employee("ameen", "2452-67878767-3", 23, 4.9); employee[3]= new Employee("shahid", "4343-4354354-3", 54, 6); var emp= from e in employee where e.Age> 25 select e; //The above mentioned line shows that LINQ is stored in variable varemp. The line = from e in employee where e.Age> 25 select e; is the LINQ where from e in employee shows that e is the instance of employee which will just act like a pointer and this pointer will point to the Age attribute of the employee on condition e.Age> 25. On this condition select e shows that only those records of employees will be selected whose age is greater then 25.// foreach(Employee em in emp) //This line shows that foreach loop will iterate according to the condition Employee em in emp which shows that array Employee will be iterated according according to the query defined in emp variable and em will work as a pointer to the Employee array records which satisfies the query defined in variable emp.// { Console.WriteLine(em.Name+ " at" + em.Age+ " " + em.CNIC); //This line will print the Name , CNIC and Age of the students who satisfies the condition of Age defined in the emp variable. } } |
Language Integrated Query to DataSet:
Language Integrated Query to DataSet is a format of writing the query for datasets. In other words, as we can access data within tables with the help of SQL. We can do the same thing in C# with the help of LINQ. LINQ helps us to search for data in databases, replace data and also remove and insert data in databases. To explain this let us consider the following example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
Using System; Using System.Collection.Generic; Using System.Data //If you want to access data sets or tables then you have to include the above mentioned library in your program// Using System.Data.SqlClient; //For accessing the data base of sql you have to include this library in your program. Otherwise your queries will not be converted from LINQ queries to SQL// Using System.Linq; //You have to include this library in your program to use LINQ queries in your program// Using System.Text; //The above mentioned library contains basic data types like int, float and string. To use these data types you have to include the above mentioned library in your program// Using System.Threading.Tasks; //This library helps you to use Threading in your program otherwise the compiler gives errors// Namespace LINQ_For_Data { Class oneTable { Static void Main(string[] args) { String connection=System.Configuration.ConfigurationManager.ConnectionsStrings["LinqToSQLDBConnectionString"].ToString(); // String connection is a variable in which your connection to the Database server will be stored. System.Configuration.ConfigurationManager.ConnectionsStrings["LinqToSQLDBConnectionString"].ToString(); is basically making the connection.// String CHOOSE="SELECT * From Department;"; // String CHOOSE is a variable in which your LINQ query is saved.// SqlDataAdapter da = new SqlDataAdapter(sqlCHOOSE, connectString); //All the LINQ queries in your program will first be compiled in the C# compiler and then by adapter they will be converted in to sql queries. SqlDataAdapter in the above mentioned line is doing the same thing. da is the instance of(SqlDataAdapter). ( = new SqlDataAdapter(sqlCHOOSE, connection);) This line will make a new SqlDataAdapter which will depend on sqlCHOOSE. The variable in which your LINQ query is stored and your variable connection in which your connection to the sql database is stored.// da.TableMappings.Add("Table", "Department"); //da is the instance of SqlDataAdapter and by .TableMappings mapping of tables in SQL with your LINQ tables are done. After mapping two tables will be added in C# database environment and then in actual sql environment which are Table and Department and this is done by Add("Table", "Department").// DataSet ds = new DataSet(); //In the above mentioned line DataSet is a pointer to one row of your table and ds is the instance of this pointer. = new DataSet(); line will create one pointer for the rows of a table.// DataTable department = ds.Tables["Department"]; //By the above mentioned line which is DataTable department we are selecting a table which is department the instance of real Department table. In = ds.Tables["Department"]; rows of Department table are selected and are assigned to department.// foreach (var q in query) //The above mentioned loop will iterate on the basis of var q defined as query by in query // { Console.WriteLine("Department Id = {0} , Name = {1}", q.DepartmentId, q.DepartmentName); //The above mentioned line will just print DepartmentId and DepartmentName of Department Id = {0} , Name = {1} due to q.DepartmentId, q.DepartmentName } } } } |