Short for Language INtegrated Query, LINQ serves as a means for the .NET languages to retrieve data from the data sources by building suitable queries. First introduced with the .NET Framework, version 3.5 and Visual Studio 2008, the most significant advantage with LINQ vis-a-vis SQL queries is that the former is a lot simpler while someone can integrate a query right within the code itself.

How LINQ scores over SQL?

With LINQ, you can achieve the same result as SQL, but you need to write a lot less code more concisely. Even with complex queries having inner queries and conditions, LINQ has proved to be more productive and efficient. This makes the program a lot more tidy and straightforward, ensuring future program maintenance is more explicit.

What’s more, it also negates the need to create joins between the relations. Another inherent benefit with LINQ is that it allows the query to be divided into several parts, with each piece being further re-usable in the application.

SQL Query Vs. LINQ Query

For instance, a query is written in SQL to find out the name of products priced above $100 and originates from a warehouse in Dallas.

SQL statement

SELECT prdt.*
FROM PrdtStore prdt
LEFT OUTER JOIN
Product p INNER JOIN Location lo ON p.LocationNo = lo.ID
ON prdt.ProductID = p.prdtID
WHERE
lo.City = 'Dallas' AND prdtID in
(
SELECT prdtID FROM Product
GROUP BY prdtID HAVING SUM (price) > 100
)

LINQ Statement

from prdt in db.Inventory
where prdt.Product.Location.City == "Dallas"
where prdt.Product.Sum(pri => pri.Price) > 100
select prdt

The difference should be perceptible right away. With just four code lines, you can achieve the same that SQL needed more than twice the number of lines to achieve. Plus, LINQ is a lot simple as well, given that there are no joins. The more the subqueries, the more is going to be the complexity of the SQL query.

What is also evident is that LINQ uses a FROM > WHERE > SELECT format, which is different from SQL that relies on a SELECT > FROM > WHERE approach.

Also, LINQ does not support the other SQL statements such as CREATE, INSERT, UPDATE, and DELETE.

Up to this point, it should be clear, to some extent at least, what LINQ is and what are its advantages over SQL. With that much knowledge, we can now move on to what we had set out to achieve in the first place, that of converting SQL query to LINQ.

First, we have to use the LINQ class present in the System namespace to do the conversion. After that, here is what we need to do:

  • Creating a database entity using LINQ to SQL class.
  • Towards that, we created a new project and added a new folder to the project folder.
  • Once the database has been created, the next thing we need to do is initialize the connection.
  • Next, we need to use the LinqToSQLDataContext in the LINQ class to access the database.
  • Once that is done, we can also track all the changes that have been made in it at run-time.

Here is a code snippet that puts to work all of these.

            //establishing connection with the database
            string connection = "data source=LAMDA\LAMD;initial catalog=Tablet;
integrated security = True";
LinqToSQLDataContext linqToSql = new LinqToSQLDataContext(connection);
            //Create a new Tablet entry
            Tablet tablet = new Tablet();
            tablet.Name = "Surface Pro";
            tablet.Company = "Microsoft";
            //insert a new tablet device to the database
            linqToSql.Tablet.InsertOnSubmit(tablet);
            //Save the changes
            linqToSql.SubmitChanges();
            //Get Inserted Tablet
            Tablet newTablet = linqToSql.Tablet.FirstOrDefault(b ⇒b.Name.Equals("Surface Pro"));
            Console.WriteLine("Tablet Name = {0}, Company Name = {1}",
            newTablet.Name, newTablet.Company);
            Console.ReadKey();

 

The output of the above code snippet will be:
Tablet Name = Surface Pro
Company Name = Microsoft

Last modified: October 14, 2020

Author

Comments

Write a Reply or Comment

Your email address will not be published.