LINQ query with example

 Collapse | Copy Code

// Basic
    // Select * From Products 
    var query1 = from p in db.Products
                 select p;
    // Select ProductID, ProductName, UnitPrice From Products
    var query2 = from p in db.Products
                 select new { 
                     p.ProductID,
                     p.ProductName,
                     p.UnitPrice
                 };

Note: query2 will create a new class which contains three properties that map the ProductId, ProductName, and UnitPrice.

 Collapse | Copy Code

// Where
    // Select * From Products Where ProductID = 1
    var query3 = from p in db.Products
                 where p.ProductID == 1
                 select p;

    // Select * From Products Where SupplierId =5 and UnitPrice > 20
    var query4 = from p in db.Products
                 where p.SupplierID == 5 && p.UnitPrice > 20
                 select p;


    // Select * From Products Where SupplierId =5 Or SupplierId=6 
    var query5 = from p in db.Products
                 where p.SupplierID == 5 || p.SupplierID == 6
                 select p;

Note: The condition in the where block is a logical express, a boolean value is returned just like in if().

 Collapse | Copy Code

// Order By 
    // Select * From Products Order By ProductId
    var query6 = from p in db.Products
                 orderby p.ProductID
                 select p;

    // Select * From Products Order By ProductId Desc
    var query7 = from p in db.Products
                 orderby p.ProductID descending
                 select p;

    // Select * From Products Order By CategoryId, UnitPrice Desc
    var query8 = from p in db.Products
                 orderby p.CategoryID, p.UnitPrice descending
                 select p;

Note: The default order is ascending, the order by p.ProductID is same as order by p.ProductID ascending, just like in T-SQL.

 Collapse | Copy Code

// Top 
    // Select Top 10 * From Products
    var query9 = (from p in db.Products
                 select p).Take(10);

    // Select Top 1 * From Products
    var query10 = (from p in db.Products
                   select p).Take(1);
    // or
    var query11 = (from p in db.Products
                   select p).First();

Note: If it just returns one record, I recommend using First instead of Take(1).

 Collapse | Copy Code

// Top with Order By
    // Select Top 10 * From Products Order By ProductId
    var query12 = (from p in db.Products
                   orderby p.ProductID
                   select p).Take(10);

// Distinct
    // Select Distinct CategoryId From Products
    var query13 = (from p in db.Products
                   select p.CategoryID).Distinct();

// Group By
    // Select CategoryId, Count(CategoryID) As NewField 
    // From Products Group By CategoryId
    var query14 = from p in db.Products
                  group p by p.CategoryID into g
                  select new { 
                      CategoryId = g.Key, 
                      NewField = g.Count() 
                  };

    // Select CategoryId, Avg(UnitPrice) As NewField From Products Group By CategoryId
    var query15 = from p in db.Products
                  group p by p.CategoryID into g
                  select new { 
                      CategoryId = g.Key, 
                      NewField = g.Average(k => k.UnitPrice) 
                  };

    // Select CategoryId, Sum(UnitPrice) As NewField From Products Group By CategoryId
    var query16 = from p in db.Products
                  group p by p.CategoryID into g
                  select new { 
                      CategoryId = g.Key,
                      NewField = g.Sum(k => k.UnitPrice )
                  };

// Union
    // Select * From Products Where CategoryId =1 union Select * 
    // From Products Where CategoryId = 2
    var query17 = (from p in db.Products
                   where p.CategoryID == 1
                   select p).Union(
                       from m in db.Products
                       where m.CategoryID == 2
                       select m
                   );

// Two tables
    // Select A.ProductId, A.ProductName, B.CategoryId, B.CategoryName 
    // From Products A, Categories B 
    //    Where A.CategoryID = B.CategoryID and A.SupplierId =1 
    var query18 = from p in db.Products
                  from m in db.Categories
                  where p.CategoryID == m.CategoryID && p.SupplierID == 1
                  select new {
                      p.ProductID,
                      p.ProductName,
                      m.CategoryID,
                      m.CategoryName
                  };