Linq queries equivalent to SQL queries

In this post I explained Linq queries equivalent to SQL by example to some basic queries. This is an overview that how we can write linq quries from sql queries.

//Select all columns

SELECT * FROM tblProduct

        using (DataClassesDataContext dbContext = new DataClassesDataContext())

        {

            var result = from product in dbContext.Products

                         select product;

        }

        //Select selected columns

SELECT ProductName, Price FROM tblProduct

        using (DataClassesDataContext dbContext = new DataClassesDataContext())

        {

            var result = from p in dbContext.Products

                         select new

                         {

                             p.ProductName,

                             p.Price,

                         };

        }

        //Select selected columns with WHERE condition

SELECT ProductName, Price FROM tblProduct

WHERE ProductId=2

        using (DataClassesDataContext dbContext = new DataClassesDataContext())

        {

            var result = from p in dbContext.Products

                         where p.ProductId==2

                         select new

                         {

                             p.ProductName,

                             p.Price,

                         };

        }

        //Select selected columns with IN condition

SELECT ProductId,ProductName, Price FROM tblProduct

WHERE ProductId IN (1,2,4)

        using (DataClassesDataContext dbContext = new DataClassesDataContext())

        {

            List<int> lstIds = new List<int>();

            lstIds.Add(1);

            lstIds.Add(2);

            lstIds.Add(4);

            var result = from p in dbContext.Products

                         where lstIds.Contains(p.ProductId)

                         select new

                         {

                             p.ProductId,

                             p.ProductName,

                             p.Price,

                         };

        }

        //Select selected columns with sorting

SELECT ProductName, Price FROM tblProduct

ORDER BY ProductName

        using (DataClassesDataContext dbContext = new DataClassesDataContext())

        {

            var result = (from p in dbContext.Products

                          select new

                          {

                              p.ProductName,

                              p.Price,

                          }).OrderByDescending(r => r.ProductName);

        }

        //group by in linq

SELECT SUM(Total) AS Total, ProductID FROM tblOrder

GROUP BY ProductId

        using (DataClassesDataContext dbContext = new DataClassesDataContext())

        {

            var result = from o in dbContext.Orders

                          group o by o.ProductId into grpOrder

                          select new

                          {

                             ProductId= grpOrder.Key,

                             Total=grpOrder.Sum(o=>o.Total)

                             

                          };

        }