LINQ to SQL Samples

• Download Northwind database scripts - 238.89 KB

Introduction

LINQ is one of the most important features in .NET Framework 3.5 (Visual Studio 2008). It's the new way to mapping database tables to classes, and as we know, we call this O/R Mapping. An article on how to write LINQ code quickly is always welcome for beginners, and I think that reading samples is the best way to learn a new technique.

These are samples created while I was learning and using LINQ, and I want to share them now. Hope they will be helpful. I will use Northwind database as a sample, which you can download from the link at the top of this article.

I recommend that you read 101 LINQ Samples if you would like to learn more.

Samples

// 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.

// 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().

// 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.

// 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).

// 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
                  };

History

• Ver 1.0 - 2008-03-18: Article created