How to convert SQL query to LINQ

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 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. LINQ has proved to be more productive and efficient, with complex queries having inner queries and conditions. 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 product’s name is 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.
[adinserter block=”2″] 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 its advantages are 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:

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