Prerequisites:

• The Northwind database installed in SQL Server (for this tutorial, SQL Server 2005 Express was used). [Installation script of the database]

Goals:

• Show some possibilities of the LINQ to SQL.

Steps:

1. Create a new Console Application project and generate the DataContext for the Northwind database as described int the 'LINQ to SQL - Generating DataContext' tutorial or just start from the end of that tutorial (northwind_data_context.src.zip).
2. Add to the project a new class named NorthwindViewer and add a member of the NorthwindDataContext type to it. As a test write a method displaying the number of customers:
   class NorthwindViewer
   {
       NorthwindDataContext northwind;
   
       public NorthwindViewer()
       {
           northwind = new NorthwindDataContext();
       }
   
       public void NumberOfCustomers()
       {
           Console.WriteLine(northwind.Customers.Count());
       }
   }
   Test it in the Program.Main method:
   static void Main(string[] args)
   {
       NorthwindViewer viewer = new NorthwindViewer();
       viewer.NumberOfCustomers();
   }
3. To make the work of adding and testing new methods of the NorthwindViewer class easier, write code which will look for 'special' methods and build a menu for the user to allow to execute these methods.
   • To mark a method as 'special', custom attribute will be used. Create a new class named RunnableMethodAttribute inherited from the Attribute class. Leave this class empty:
     class RunnableMethodAttribute : Attribute
     {
     }
   • Apply this new attribute to the NorthwindViewer.NumberOfCustomers method:
     [RunnableMethod]
     public void NumberOfCustomers()
     {
         Console.WriteLine(northwind.Customers.Count());
     }
   • Add to the Program class code creating and displaying a menu for the user:
     class Program
     {
         static Dictionary<char, MethodInfo> menu;
         static NorthwindViewer viewer;
     
         static void BuildMenu()
         {
             menu = new Dictionary<char, MethodInfo>();
             char c = 'A';
     
             var methods = (IEnumerable<MethodInfo>)viewer.GetType().GetMethods().Reverse();
             foreach (MethodInfo mi in methods)
             {
                 if (mi.GetCustomAttributes(typeof(RunnableMethodAttribute), false).Length > 0)
                 {
                     menu.Add(c, mi);
                     c++;
                 }
             }
         }
     
         static void ShowMenu()
         {
             foreach (KeyValuePair<char, MethodInfo> keyValue in menu)
             {
                 Console.WriteLine("[{0}] {1}", keyValue.Key, keyValue.Value.Name);
             }
         }
     
         static bool ProcessMenu()
         {
             Console.WriteLine("--------------------------------");
             ShowMenu();
             Console.WriteLine("[0] -- exit");
     
             string s;
             char c;
             do
             {
                 s = Console.ReadLine();
                 c = (s.Length > 0) ? char.ToUpper(s[0]) : ' ';
             } while (c != '0' && !menu.ContainsKey(c));
     
             if (c == '0')
             {
                 return false;
             }
             else
             {
                 MethodInfo mi = menu[c];
                 mi.Invoke(viewer, new object[] { });
                 return true;
             }
         }
     
         static void Main(string[] args)
         {
             viewer = new NorthwindViewer();
             BuildMenu();
     
             while (ProcessMenu())
             {
             }
         }
     }
     Run the application and test the menu:
     --------------------------------
     [A] NumberOfCustomers
     [0] -- exit
4. To see the SQL queries used by the LINQ to SQL, add to the constructor the following line:
   northwind.Log = Console.Out;
   --------------------------------

   [A] NumberOfCustomers
   [0] -- exit
   a
   SELECT COUNT(*) AS [value]
   FROM [dbo].[Customers] AS [t0]
   -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
   
   91
5. Display number of customer per country, sorted descending by the number:
   [RunnableMethod]
   public void NumberOfCustomersPerCountry()
   {
       var numbersPerCountries =
           from customer in northwind.Customers
           group customer by customer.Country into g
           orderby g.Count() descending
           select new { Country = g.Key, Count = g.Count() };
   
       foreach (var nc in numbersPerCountries)
       {
           Console.WriteLine("{0}: {1} customer(s)", nc.Country, nc.Count);
       }
   }

   SELECT [t1].[Country], [t1].[value2] AS [Count]
   FROM (
   SELECT COUNT(*) AS [value], COUNT(*) AS [value2], [t0].[Country]
   FROM [dbo].[Customers] AS [t0]
   GROUP BY [t0].[Country]
   ) AS [t1]
   ORDER BY [t1].[value] DESC
   -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
   
   USA: 13 customer(s)
   France: 11 customer(s)
   Germany: 11 customer(s)
   Brazil: 9 customer(s)
   UK: 7 customer(s)
   Spain: 5 customer(s)
   Mexico: 5 customer(s)
   Venezuela: 4 customer(s)
   Italy: 3 customer(s)
   Canada: 3 customer(s)
   Argentina: 3 customer(s)
   Austria: 2 customer(s)
   Belgium: 2 customer(s)
   Denmark: 2 customer(s)
   Finland: 2 customer(s)
   Portugal: 2 customer(s)
   Sweden: 2 customer(s)
   Switzerland: 2 customer(s)
   Norway: 1 customer(s)
   Poland: 1 customer(s)
   Ireland: 1 customer(s)
6. Show customers from Italy:
   [RunnableMethod]
   public void CustomersFromItaly()
   {
       var Italians = northwind.Customers.Where(c => c.Country == "Italy");
   
       foreach (var italian in Italians)
       {
           Console.WriteLine("{0,-30} {1,-10} {2,-20}",
               italian.CompanyName, italian.Country, italian.City);
       }
   }

   SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
   FROM [dbo].[Customers] AS [t0]
   WHERE [t0].[Country] = @p0
   -- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Italy]
   -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
   
   Franchi S.p.A. Italy Torino
   Magazzini Alimentari Riuniti Italy Bergamo
   Reggiani Caseifici Italy Reggio Emilia
7. For each product, display information if the product is available:
   [RunnableMethod]
   public void ProductsAvailability()
   {
       var products =
           from p in northwind.Products
           select new { p.ProductName,
               Availability = p.UnitsInStock - p.UnitsOnOrder < 0 ? "Out Of Stock" : "In Stock" };
   
       foreach (var p in products)
       {
           Console.WriteLine("{0}: {1}", p.ProductName, p.Availability);
       }
   }

   SELECT [t0].[ProductName],
   (CASE
   WHEN ((CONVERT(Int,[t0].[UnitsInStock])) - (CONVERT(Int,[t0].[UnitsOnOrder]))) < @p0 THEN @p1
   ELSE CONVERT(NVarChar(12),@p2)
   END) AS [Availability]
   FROM [dbo].[Products] AS [t0]
   -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
   -- @p1: Input NVarChar (Size = 12; Prec = 0; Scale = 0) [Out Of Stock]
   -- @p2: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [In Stock]
8. List all discounted products from all orders:
   [RunnableMethod]
   public void DiscountedProducts()
   {
       var orders =
           from o in northwind.Orders
           select new
           {
               o.OrderID,
               DiscountedProducts =
                   from od in o.Order_Details
                   where od.Discount > 0.0
                   select new { OrderDetails = od, od.Product.ProductName }
           };
   
       foreach (var order in orders)
       {
           Console.WriteLine("order ID: {0}", order.OrderID);
           foreach (var od in order.DiscountedProducts)
           {
               Console.WriteLine("    product ID: {0}, name: {1}, discount: {2}",
                   od.OrderDetails.ProductID, od.ProductName, od.OrderDetails.Discount);
           }
       }
   }

   SELECT [t0].[OrderID], [t1].[OrderID] AS [OrderID2], [t1].[ProductID], [t1].[UnitPrice], [t1].[Quantity], [t1].[Discount], [t2].[ProductName], (
   SELECT COUNT(*)
   FROM [dbo].[Order Details] AS [t3]
   INNER JOIN [dbo].[Products] AS [t4] ON [t4].[ProductID] = [t3].[ProductID]
   WHERE ([t3].[Discount] > @p0) AND ([t3].[OrderID] = [t0].[OrderID])
   ) AS [value]
   FROM [dbo].[Orders] AS [t0]
   LEFT OUTER JOIN ([dbo].[Order Details] AS [t1]
   INNER JOIN [dbo].[Products] AS [t2] ON [t2].[ProductID] = [t1].[ProductID])
   ON ([t1].[Discount] > @p0) AND ([t1].[OrderID] = [t0].[OrderID])
   ORDER BY [t0].[OrderID], [t1].[ProductID]
   -- @p0: Input Float (Size = 0; Prec = 0; Scale = 0) [0]
9. Calculate the total freight over all orders:
   [RunnableMethod]
   public void TotalFreight()
   {
       var totalFreight = northwind.Orders.Sum(o => o.Freight);
   
       Console.WriteLine("Total freight: {0}", totalFreight);
   }

   SELECT SUM([t0].[Freight]) AS [value]
   FROM [dbo].[Orders] AS [t0]
10. Most expensive products listed for each category:
    [RunnableMethod]
    public void MostExpensiveProductsPerCategory()
    {
        var categories =
            from p in northwind.Products
            group p by p.CategoryID into g
            join c in northwind.Categories on g.Key equals c.CategoryID
            select new {                
                CategoryID = g.Key,
                CategoryName = c.CategoryName,
                MostExpensiveProducts =
                    from p2 in g
                    where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
                    select p2
            };
    
        foreach (var category in categories)
        {
            Console.WriteLine("Category ID: {0}, name: {1}",
                category.CategoryID, category.CategoryName);
            foreach (var product in category.MostExpensiveProducts)
            {
                Console.WriteLine("    Product ID: {0}, name: {1}, unit price: {2}",
                    product.ProductID, product.ProductName, product.UnitPrice);
            }
        }
    }

    SELECT [t1].[CategoryID], [t2].[CategoryName], [t1].[value]
    FROM (
    SELECT MAX([t0].[UnitPrice]) AS [value], [t0].[CategoryID]
    FROM [dbo].[Products] AS [t0]
    GROUP BY [t0].[CategoryID]
    ) AS [t1]
    INNER JOIN [dbo].[Categories] AS [t2] ON [t1].[CategoryID] = ([t2].[CategoryID])
    
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
    FROM [dbo].[Products] AS [t0]
    WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
    -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
    -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [263.5000]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    Category ID: 1, name: Beverages
    Product ID: 38, name: Côte de Blaye, unit price: 263,5000
    SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
    FROM [dbo].[Products] AS [t0]
    WHERE ([t0].[UnitPrice] = @x2) AND (((@x1 IS NULL) AND ([t0].[CategoryID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[CategoryID] IS NOT NULL) AND (@x1 = [t0].[CategoryID])))
    -- @x1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
    -- @x2: Input Money (Size = 0; Prec = 19; Scale = 4) [43.9000]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    Category ID: 2, name: Condiments
    Product ID: 63, name: Vegie-spread, unit price: 43,9000
11. List numbers of orders, grouped by customers:
    [RunnableMethod]
    public void NumberOfOrdersPerCustomer()
    {
        var qq =
            from order in northwind.Orders
            group order by order.CustomerID into orders
            join customer in northwind.Customers on orders.Key equals customer.CustomerID
            select new {
                CustomerID = orders.Key,
                CompanyName = customer.CompanyName,
                Count = orders.Count()
            };
    
        foreach (var q in qq)
        {
            Console.WriteLine("Customer ID: {0}, company name: {1}, number of orders: {2}",
                q.CustomerID, q.CompanyName, q.Count);
        }
    }
    SELECT [t1].[CustomerID], [t2].[CompanyName], [t1].[value] AS [Count]
    FROM (
    SELECT COUNT(*) AS [value], [t0].[CustomerID]
    FROM [dbo].[Orders] AS [t0]
    GROUP BY [t0].[CustomerID]
    ) AS [t1]
    INNER JOIN [dbo].[Customers] AS [t2] ON [t1].[CustomerID] = [t2].[CustomerID]
    Not surprisingly, the used SQL query depends on the LINQ query, the following code displays the same information but using a different query:
    [RunnableMethod]
    public void NumberOfOrdersPerCustomer_Alt()
    {
        var qq =
            from customer in northwind.Customers
            select new
            {
                CustomerID = customer.CustomerID,
                CompanyName = customer.CompanyName,
                Count = northwind.Orders.Where(o => o.CustomerID == customer.CustomerID).Count()
            };
    
        foreach (var q in qq)
        {
            Console.WriteLine("Customer ID: {0}, company name: {1}, number of orders: {2}",
                q.CustomerID, q.CompanyName, q.Count);
        }
    }

    SELECT [t0].[CustomerID], [t0].[CompanyName], (
    SELECT COUNT(*)
    FROM [dbo].[Orders] AS [t1]
    WHERE [t1].[CustomerID] = [t0].[CustomerID]
    ) AS [Count]
    FROM [dbo].[Customers] AS [t0]
12. List customer without orders:
    [RunnableMethod]
    public void CustomersWithoutOrders()
    {
        var customers =
            from c in northwind.Customers
            where !c.Orders.Any()
            select c;
    
        foreach (var customer in customers)
        {
            Console.WriteLine(customer.CustomerID);
        }
    }
    SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
    FROM [dbo].[Customers] AS [t0]
    WHERE NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Orders] AS [t1]
    WHERE [t1].[CustomerID] = [t0].[CustomerID]
    ))
13. Some of SQL Server functions can be used, e.g. LIKE:
    [RunnableMethod]
    public void SqlMethodsLike()
    {
        var names = from c in northwind.Customers
            where SqlMethods.Like(c.CompanyName, "%a%e%o%")
            select c.CompanyName;
    
        foreach (var name in names)
        {
            Console.WriteLine(name);
        }
    }
    SELECT [t0].[CompanyName]
    FROM [dbo].[Customers] AS [t0]
    WHERE [t0].[CompanyName] LIKE @p0
    -- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [%a%e%o%]
14. Add a new customer:
    [RunnableMethod]
    public void InsertNewCustomer()
    {
        Console.WriteLine("Before: {0}", northwind.Customers.Count());
    
        var newCustomer = new Customer
        {
            CustomerID = "MCSFT",
            CompanyName = "Microsoft",
            ContactName = "John Doe",
            ContactTitle = "Sales Manager",
            Address = "1 Microsoft Way",
            City = "Redmond",
            Region = "WA",
            PostalCode = "98052",
            Country = "USA",
            Phone = "(425) 555-1234",
            Fax = null
        };
        northwind.Customers.InsertOnSubmit(newCustomer);
        northwind.SubmitChanges();
    
        Console.WriteLine("After: {0}", northwind.Customers.Count());
    }

    SELECT COUNT(*) AS [value]
    FROM [dbo].[Customers] AS [t0]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    Before: 91
    INSERT INTO [dbo].[Customers]([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])
    VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)
    -- @p0: Input NChar (Size = 5; Prec = 0; Scale = 0) [MCSFT]
    -- @p1: Input NVarChar (Size = 9; Prec = 0; Scale = 0) [Microsoft]
    -- @p2: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [John Doe]
    -- @p3: Input NVarChar (Size = 13; Prec = 0; Scale = 0) [Sales Manager]
    -- @p4: Input NVarChar (Size = 15; Prec = 0; Scale = 0) [1 Microsoft Way]
    -- @p5: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Redmond]
    -- @p6: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [WA]
    -- @p7: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [98052]
    -- @p8: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]
    -- @p9: Input NVarChar (Size = 14; Prec = 0; Scale = 0) [(425) 555-1234]
    -- @p10: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    SELECT COUNT(*) AS [value]
    FROM [dbo].[Customers] AS [t0]
15. Delete a customer:
    [RunnableMethod]
    public void DeleteCustomer()
    {
        Console.WriteLine("Before: {0}", northwind.Customers.Count());
    
        northwind.Customers.DeleteAllOnSubmit(
            northwind.Customers.Where(c => c.CustomerID == "MCSFT"));
        northwind.SubmitChanges();
    
        Console.WriteLine("After: {0}", northwind.Customers.Count());
    }

    SELECT COUNT(*) AS [value]
    FROM [dbo].[Customers] AS [t0]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    Before: 92
    DELETE FROM [dbo].[Customers] WHERE ([CustomerID] = @p0) AND ([CompanyName] = @p1) AND ([ContactName] = @p2) AND ([ContactTitle] = @p3) AND ([Address] = @p4) AND ([City] = @p5) AND ([Region] = @p6) AND ([PostalCode] = @p7) AND ([Country] = @p8) AND ([Phone] = @p9) AND ([Fax] IS NULL)
    -- @p0: Input NChar (Size = 5; Prec = 0; Scale = 0) [MCSFT]
    -- @p1: Input NVarChar (Size = 9; Prec = 0; Scale = 0) [Microsoft]
    -- @p2: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [John Doe]
    -- @p3: Input NVarChar (Size = 13; Prec = 0; Scale = 0) [Sales Manager]
    -- @p4: Input NVarChar (Size = 15; Prec = 0; Scale = 0) [1 Microsoft Way]
    -- @p5: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Redmond]
    -- @p6: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [WA]
    -- @p7: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [98052]
    -- @p8: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]
    -- @p9: Input NVarChar (Size = 14; Prec = 0; Scale = 0) [(425) 555-1234]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
    
    SELECT COUNT(*) AS [value]
    FROM [dbo].[Customers] AS [t0]
    -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
16. Modify a customer:
    [RunnableMethod]
    public void ModifyCustomer()
    {
        Console.WriteLine("[1]: {0}", northwind.Customers.First().ContactTitle);
    
        string old = northwind.Customers.First().ContactTitle;
        northwind.Customers.First().ContactTitle = "-- modified --";
        northwind.SubmitChanges();
    
        northwind.Customers.First().ContactTitle = "-- modified (2) --";
        Console.WriteLine("[2]: {0}", northwind.Customers.First().ContactTitle);
    
        // clear the cache by recreating the DataContext object
        northwind = new NorthwindDataContext() { Log = northwind.Log };
        Console.WriteLine("[3]: {0}", northwind.Customers.First().ContactTitle);
    
        northwind.Customers.First().ContactTitle = old;
        northwind.SubmitChanges();
    
        Console.WriteLine("[4]: {0}", northwind.Customers.First().ContactTitle);
    }
    [1]: Sales Representative
    [2]: -- modified (2) --
    [3]: -- modified --
    [4]: Sales Representative

More examples for LINQ to SQL can be found in 'C# Samples for Visual Studio 2008'.

[Source code]