LINQ stands for Language INtegrated Query which allows writing queries directly into the code. Queries can be written to relational databases and XML databases. Hence, it is used heavily in the .NET platform for in-memory data representation.
Why LINQ over SQL?
When we compared to SQL, LINQ is more productive, simpler and tidier in most of the cases. When we use LINQ, we don’t have to create any joins between the relations. It is a fact that SQL is an old language and has established in the field, even though when it comes to complex queries with inner queries and conditions, LINQ produce results in a more productive way than the SQL. Another important aspect of LINQ is; it can break the query into several parts and re-use those parts throughout the application.
Let’s compare a query written to take employees whose salary is higher than $100 and residence is “NY.”
SELECT emp.*
FROM EmpDetails emp
LEFT OUTER JOIN
Employee e INNER JOIN Address a ON e.AddressNo = a.ID
ON emp.EmployeeID = e.empID
WHERE
a.State = 'NY' AND empID in
(
SELECT empID FROM Employee
GROUP BY empID HAVING SUM (Salary) > 100
)
from emp in db.Company where emp.Employee.Address.State == "NY" where emp.Employee.Sum(sal => sal.Salary) > 100 select emp
From the above example, we can see how easy to use LINQ over the SQL without using any joins. When there are many subqueries, SQL becomes more complex and takes some effort to write the query. For a beginner, LINQ is easier to master than SQL, and as this is extensively used in the .NET platform, it is recommended to learn this unless you are not a .NET programmer.
Format of SQL
- SELECT
- FROM
- WHERE
Format of LINQ
- FROM
- WHERE
- SELECT
Linq is just like the inversion of the SQL query, wherein SQL required fields are projected before the where clause. But in LINQ, a projection happens at the end. Now let’s see how to convert our SQL query to LINQ manually using code. We can insert, update and delete data from SQL to LINQ. For that, we can use the LINQ class available in the System namespace for the conversion. First, we need to create a database entity using LINQ to SQL class. To move on with this, create a new project and add a new item to the project folder. After creating the database, we need to initialize the connection and use LinqToSQLDataContext available in the LINQ class to manage the access to the database and track all the changes done to it at the run time.
//connect with the database
string connection = "data source=VIRAJ\VIRA;initial catalog=Books;
integrated security=True";
LinqToSQLDataContext linqToSql = new LinqToSQLDataContext(connection);
//Create a new Book
Book book = new Book();
book.Name = "Go away";
book.Author = "Martin Cruz";
book.ISBN = "978-3-16-148410-0;
//insert a new book to the database
linqToSql.Books.InsertOnSubmit(book);
//Saving changes
linqToSql.SubmitChanges();
//Get Inserted Book
Book newBook = linqToSql.Books.FirstOrDefault(b ⇒b.Name.Equals("Go away"));
Console.WriteLine("Book Name = {0}, Author Name = {1}, ISBN = {2}",
newBook.Name, newBook.Author, newBook.ISBN);
Console.ReadKey();
However, we need to create a new Book object and set the values to the data members. After setting the values, we need to insert our object into the database. To add a book object to the database, LINQ to SQL instance is used for the conversion of book object into the Linq format. One thing to notice is that we need to submit the changes to the database after any activity. Otherwise, changes will not be committed to the database. After the successful insertion, we need to check whether the data is added to the database or not. For that, we use FirstOrDefault clause related to the database to retrieve the first row that matches with our requirement. In this case, it returns the 1st book which has the book name “Go away.”
Output
Book Name = Go away, Author Name = Martin Cruz, ISBN = 978-3-16-148410-0
Similar way, we can do the update and delete as well. Hence, if you are using .NET framework for the programming (VB, C#, ASP.NET), it is highly recommended to move from SQL to LINQ as Microsoft provides higher support for the LINQ.
Comments