Extension Methods

In this article I am going to look at Extension Methods. Although this is not a specific to LINQ post, Extension Methods are the compiler concept that LINQ was built on.

The basic premise is to allow the developer to add functionality to any class they like without the need to subclass it or wrap it in a wrapper class. This is a great new technique because it allows you to continue using a class like String or DateTime and not need to remember to use MyString and MyDateTime or fall back on static helper classes.

So how do we go about doing this?
I have recently been working on a tool that constantly needs to convert DateTime values from my local time zone here in Australia (GMT +10) to Central US time (GMT -6). Traditionally you would probably create a static helper class with a method that takes the time in one form and passes it back in the other, just to save you writing the conversion code in every place that uses it. Ignore the simplicity of the time conversion here, this is simply to keep the code short.

using System;

namespace Test_Application
{
    static class ExtensionMethods
    {
        public static DateTime LocalTimeToCentral(DateTime dt)
        {
            return dt.ToUniversalTime().AddHours(-6);
        }
    }
}

To call this in our code would look like this

DateTime dt = ExtensionMethods.LocalTimeToCentral(DateTime.Now);
Console.WriteLine("Central - " + dt.ToString());

It works, but it could be nicer. With a very simple tweak we can now attach this method to the DateTime class itself. Check this out.

using System;

namespace Test_Application
{
    static class ExtensionMethods
    {
        public static DateTime ToCentral(this DateTime dt)
        {
            return dt.ToUniversalTime().AddHours(-6);
        }
    }
}

By simply add a new keyword "this" in front of the first parameter (I renamed the method simply to have a name consistent with it's new usage) we have created an Extension method. Now any DateTime class used where the Test_Application namespace is in use will have a ToCentral() method.
Note that the parameter with "this" does not convert into a parameter on the new method, it maps to the instance you are working on. Subsequent parameters will flow onto the method though. You will see this later.

We can now use this method like this

DateTime dt = DateTime.Now.ToCentral();
Console.WriteLine("Central - " + dt.ToString());

Of course this comes with full intellisense and compile time type checking.
Extension Methods add a whole new level to what component developers can offer us, but you should not pass up opportunities to use them in your day to day coding.

For one last code sample I am going to implement a very basic subset of the functionality of the Visual FoxPro StrExtract() method. I have talked about it before and how much I like it for basic string parsing. Without much work you could use Extension Methods to add an Extract() method to the string class in the .NET. Here is a start.

using System;

namespace Test_Application
{
    static class ExtensionMethods
    {
        public static string Extract(this string s, string start, string end)
        {
            string ret = "";
            int startPos = s.IndexOf(start) + start.Length;
            int endPos = s.IndexOf(end, startPos);
            if (startPos >= 0 && endPos >= 0)
            {
                ret = s.Substring(startPos, endPos - startPos);
            }
            return ret;
        }
    }
}

And to call it.

string s = "<text><first>First Test</first><second>Second Test</second></text>";
Console.WriteLine(s.Extract("<first>", "</first>")); // First Test
Console.WriteLine(s.Extract("<second>", "</second>")); // Second Test

Fantastic!

 

I have been running an eye over the keywords people are searching for that are landing them on my blog in the hope of getting an idea of what people want to know more about. Overwhelmingly the hottest keyword is insert, so I will see what I can do about going more in-depth on inserts in LINQ-to-SQL with a special focus on handling many-to-many relationships and foreign keys. I have also noticed a few people looking for ways to keep their data model up to date. This is an issue I feel is outstanding in LINQ-to-SQL and I am still searching for what I feel is a good solution, so I will keep you posted as I develop ideas in this area.

LINQ to SQL: Extending Data Classes

Next we are going to look at how you can use partial classes and partial methods to add functionality to your generated LINQ-to-SQL classes. One of the nice things about partial classes and the new partial methods is that you can extend the generated classes into a separate file that is not destroyed when you update the underlying data model. See my complaint here about other ways of changing the classes that does not have this benefit.
First things first is to create a new C# class file, I have called it AdventureWorks_Extra.cs to sort it below the AdventureWorks.dbml

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AdventureWorks
{
    partial class Address
    {
        
    }
}

Here we will be extending the Address class, but you can extend any of the generated classes. If we type partial we are presented with a list of partial methods we can implement.

Each property (or field in the database) gets its own Changed() and Changing() property and the class itself has OnCreated(), OnLoaded() and OnValidate().
So what exactly are partial methods and how do they differ from events or overwriting a base class method.
Here is a code snippet from AdventureWorks.designer.cs from the Address class to show the definition and calling of a partial method. I have cut out a bunch of other definitions to keep the snippet small.

public partial class Address : INotifyPropertyChanging, INotifyPropertyChanged
{
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    
    public Address()
    {
        OnCreated();
    }
}

What the compiler does when it sees a partial method is go looking for an implementation. Not one of the partial methods generated by the LINQ-to-SQL designer has any code associated with it by default, that is for you to do. If the compiler can not find an implementation it actually removes the definition and all the calls from the compiled class. This offers component designers in particular a way to offer thousands of binding points to their code, that will have absolutely no impact on performance or code size unless the developer adds some code, and this is exactly what the LINQ-to-SQL team have done. Its quite a nifty new feature of the language.
Going back to actually implementing some of these methods on our Address class. Lets add a partial method to the class that will uppercase our City property for us.

public partial class Address
{
    partial void OnCityChanged()
    {
        _City = _City.ToUpper();
    }
}

We can then check this from our code

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;

Address a = new Address();
a.City = "Seattle";
Console.WriteLine(a.City); // SEATTLE

Alternatively we can use the OnValidate() method to apply some business logic checks.

partial void OnValidate(System.Data.Linq.ChangeAction action)
{
    if (action == ChangeAction.Delete && CustomerAddresses.Count() != 0)
    {
        throw new Exception("Can not delete an address that is in use");
    }
}

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;

Address addr = db.Addresses.Where(a => a.CustomerAddresses.Count() > 0).First();
db.Addresses.DeleteOnSubmit(addr);
db.SubmitChanges(); // Throws Exception

Another use for this technique is to add helper functions to your underlying data object. For example we are going to add a method to our Address class that can format up an address label (suitable for an envelope)

public string GetAddressLabel()
{
    return AddressLine1 + "\n"
        + (AddressLine2 == null ? "" : AddressLine2 + "\n")
        + City + " " + PostalCode + " " + StateProvince.StateProvinceCode + "\n"
        + StateProvince.CountryRegion.Name;
}

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;

Address addr = db.Addresses.First();
Console.WriteLine(addr.GetAddressLabel());

// 1970 Napa Ct.
// Bothell 98011 WA
// United States

There is just a couple of options available to you for extending the generated data model. One area of LINQ I have not investigated thoroughly yet is adding functionality to LINQ itself to provide methods on IQueryable that can alter the expression tree that gets turned in SQL. If these sorts of changes are possible it will open all sorts of possibilities.

LINQ to SQL: GroupBy()

One aspect of LINQ I have not covered yet is the equivalent of a GROUP BY in SQL. The GroupBy() function (which of course can be used from a LINQ expression as well as from the method syntax and I will show both) provides this functionality. One of the interesting things about grouping however is that there is a new interface introduced that you will want to understand, lets take a look at the method signature for GroupBy().

The function takes a single Lambda Expression which returns the value to group by, this can be any basic data type and will flow through to the returned object.
The return type is a little complicated IQueryable<IGrouping<TKey, Address>>
What this means is you will have an IQueryable collection of IGrouping objects.
An IGrouping object is a collection of data records (in this case of type Address) with a special property added "Key", which holds the grouped value that associated all of the records in that collection together. Confused? Lets take a look at an example. I will start by using the GroupBy() function as I find it easier to read than LINQ Expressions.

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;

var g = db.Addresses.GroupBy(a => a.PostalCode);
foreach (var pc in g)
{
    Console.WriteLine(pc.Key + " - " + pc.Count().ToString());
}

Now this is a terribly inefficient way to access this data, but it shows you the general structure of the the result of a GroupBy().
As you can see we are returned a Collection (of Groups) which we can iterate through, each element in that Collection is also a Collection (of Records) which we can perform standard LINQ functions on, so we can actually write something like pc.Where(a => a.City == "Seattle").
How does this work in a more traditional (and SQL efficient) manner? Well the best way is to drop it into a LINQ Expression. Here we get a list of Postal Codes, a Count() of all the orders shipped to that code and a Sum() of the value of these orders.
You will need to make the changes I discussed here to your DataContext before this example will work.

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;

var pcs = from a in db.Addresses
          group a by a.PostalCode into g
          select new { 
              PostalCode = g.Key, 
              Orders = (int?)g.Sum(addr => addr.SalesOrderHeaders_ShipTo.Count) ?? 0, 
              OrderValue = (decimal?)g.Sum(addr => addr.SalesOrderHeaders_ShipTo.Sum(o => o.SubTotal + o.Freight)) ?? 0m 
          };
foreach (var pc in pcs)
    Console.WriteLine(pc.PostalCode + " - " + pc.Orders.ToString() +  " orders - $" + pc.OrderValue.ToString());

SELECT [t5].[PostalCode], COALESCE([t5].[value],@p0) AS [Orders], COALESCE([t5].[value2],@p1) AS [OrderValue]
FROM (
    SELECT SUM([t4].[value2]) AS [value], SUM([t4].[value]) AS [value2], [t4].[PostalCode]
    FROM (
        SELECT (
            SELECT SUM([t3].[SubTotal] + [t3].[Freight])
            FROM [Sales].[SalesOrderHeader] AS [t3]
            WHERE [t3].[ShipToAddressID] = [t2].[AddressID]
            ) AS [value], [t2].[PostalCode], [t2].[value] AS [value2]
        FROM (
            SELECT (
                SELECT COUNT(*)
                FROM [Sales].[SalesOrderHeader] AS [t1]
                WHERE [t1].[ShipToAddressID] = [t0].[AddressID]
                ) AS [value], [t0].[AddressID], [t0].[PostalCode]
            FROM [Person].[Address] AS [t0]
            ) AS [t2]
        ) AS [t4]
    GROUP BY [t4].[PostalCode]
    ) AS [t5]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
'AdventureWorks.vshost.exe' (Managed): Loaded 'Anonymously Hosted DynamicMethods Assembly'
-- @p1: Input Decimal (Size = 0; Prec = 33; Scale = 4) [0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

RG41 1QW - 53 orders - $68570.0109
53131 - 52 orders - $34293.5160
80074 - 132 orders - $365231.6172
31770 - 46 orders - $564563.9205
78100 - 69 orders - $78081.7795
SL4 1RH - 56 orders - $222467.7091
92173 - 4 orders - $173634.3922
...

Note how the Null values are handled both in the LINQ Expression and the resulting SQL. You may not have seen the ?? operator before, it checks if the left hand side is null, if it is then it returns the right hand side, otherwise it returns the left hand side.
This example is a little convoluted thanks to the way Addresses are joined to Orders (Sum(Count()) and Sum(Sum())), but I think it serves a good example of just how good LINQ-to-SQL is at building SQL from your LINQ Expression.
You will always be using anonymous types when playing around with GroupBy() so its a good idea to have a feel for how they work. var will soon be your new best friend. Have a look here if you have missed them.

LINQ to SQL: Aggregate Functions and more

In this post I am going to cover off how functions such as Count(), Average() and Sum() work, plus the different ways to call them. Then I will move onto some functions that are not from the domain of SQL but add great features.
Count() The Count() function is used to count the number of rows in a table or returned from a query. Count() can optionally take a single Lambda Expression as a parameter that will evaluate to a bool and indicate whether to count each record.
Here are three examples.

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;

// Count all records in a table
Console.WriteLine(db.SalesOrderHeaders.Count());

// Count all records in a table that match a condition
Console.WriteLine(db.SalesOrderHeaders.Count(s => s.OrderDate.Year == 2002));

// Count the results from a query
var query = from sales in db.SalesOrderHeaders
            where sales.OrderDate.Year == 2002
            select sales;
Console.WriteLine(query.Count());

Sum() and Average() The Sum() and Average() functions are as simple to use as Count(). Both take a Lambda Expression that evaluates into one of the numeric types (int, decimal, long...), this expression is calculated for each record. Note that Min() and Max() work in the same way also.
Here are some examples using the in-line syntax.

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;

// Sum SubTotal + Freight for all rows
Console.WriteLine(db.SalesOrderHeaders.Sum(s => s.SubTotal + s.Freight));
// Sum SubTotal + Freight for all orders from 2002
Console.WriteLine(db.SalesOrderHeaders.Where(s => s.OrderDate.Year == 2002).Sum(s => s.SubTotal + s.Freight));
// Average SubTotal + Freight for all rows
Console.WriteLine(db.SalesOrderHeaders.Average(s => s.SubTotal + s.Freight));
// Average SubTotal + Freight for all orders from 2002
Console.WriteLine(db.SalesOrderHeaders.Where(s => s.OrderDate.Year == 2002).Average(s => s.SubTotal + s.Freight));

It is important to note the SQL code that is generated from these queries

SELECT SUM([t0].[SubTotal] + [t0].[Freight]) AS [value]
FROM [Sales].[SalesOrderHeader] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

'AdventureWorks.vshost.exe' (Managed): Loaded 'Anonymously Hosted DynamicMethods Assembly'
130520610.3644
SELECT SUM([t0].[SubTotal] + [t0].[Freight]) AS [value]
FROM [Sales].[SalesOrderHeader] AS [t0]
WHERE DATEPART(Year, [t0].[OrderDate]) = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2002]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

36988590.6876
SELECT AVG([t0].[SubTotal] + [t0].[Freight]) AS [value]
FROM [Sales].[SalesOrderHeader] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

4148.1204
SELECT AVG([t0].[SubTotal] + [t0].[Freight]) AS [value]
FROM [Sales].[SalesOrderHeader] AS [t0]
WHERE DATEPART(Year, [t0].[OrderDate]) = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2002]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

10018.5781

LINQ-to-SQL ensures the processing is done on the SQL Server not on the client and keeps your performance up to scratch. Even in the case where I apply a filter it puts all my requests together and keeps the SQL statement clean.
All() and Any() LINQ adds two functions that you may not be too used to using from a pure SQL perspective. All() and Any() both take a Lambda Expression that evaluates to a bool and returns a bool based whether All or Any of the records meet the expression. This is shown here with the resulting SQL code.

// See if all the orders have a SubTotal > 0            
Console.WriteLine(db.SalesOrderHeaders.All(s => s.SubTotal > 0));

// See if any of the order have an order date of today
Console.WriteLine(db.SalesOrderHeaders.Any(s=> s.OrderDate == DateTime.Now));

SELECT 
    (CASE 
        WHEN NOT (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [Sales].[SalesOrderHeader] AS [t1]
            WHERE (
                (CASE 
                    WHEN [t1].[SubTotal] > @p0 THEN 1
                    ELSE 0
                 END)) = 0
            )) THEN 1
        WHEN NOT NOT (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [Sales].[SalesOrderHeader] AS [t1]
            WHERE (
                (CASE 
                    WHEN [t1].[SubTotal] > @p0 THEN 1
                    ELSE 0
                 END)) = 0
            )) THEN 0
        ELSE NULL
     END) AS [value]
-- @p0: Input Decimal (Size = 0; Prec = 33; Scale = 4) [0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

True
SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [Sales].[SalesOrderHeader] AS [t0]
            WHERE [t0].[OrderDate] = @p0
            ) THEN 1
        ELSE 0
     END) AS [value]
-- @p0: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 2:55:40 PM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

False

Skip() and Take() The last two functions I will show are Skip() and Take(). These functions are most useful for a paging system. Both augment the resulting SQL statement in a way that allows a paging system to pull back just the records that are required. An example of this is given here where we generate 3 pages of 10 records per page from the SalesOrderHeader table.

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;
int recPerPage = 10;

for (int page = 1; page <= 3; page++)
{
    var recs = db.SalesOrderHeaders.Skip((page - 1) * recPerPage).Take(recPerPage);
    Console.WriteLine("Page " + page.ToString() + ":");
    foreach (var rec in recs)
    {
        Console.Write(rec.SalesOrderID.ToString() + " ");
    }
}

Page 1:
SELECT TOP (10) [t0].[SalesOrderID], [t0].[RevisionNumber], [t0].[OrderDate], [t0].[DueDate], [t0].[ShipDate], [t0].[Status], [t0].[OnlineOrderFlag], [t0].[SalesOrderNumber], [t0].[PurchaseOrderNumber], [t0].[AccountNumber], [t0].[CustomerID], [t0].[ContactID], [t0].[SalesPersonID], [t0].[TerritoryID], [t0].[BillToAddressID], [t0].[ShipToAddressID], [t0].[ShipMethodID], [t0].[CreditCardID], [t0].[CreditCardApprovalCode], [t0].[CurrencyRateID], [t0].[SubTotal], [t0].[TaxAmt], [t0].[Freight], [t0].[TotalDue], [t0].[Comment], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Sales].[SalesOrderHeader] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
43659 43660 43661 43662 43663 43664 43665 43666 43667 43668

Page 2:
SELECT [t1].[SalesOrderID], [t1].[RevisionNumber], [t1].[OrderDate], [t1].[DueDate], [t1].[ShipDate], [t1].[Status], [t1].[OnlineOrderFlag], [t1].[SalesOrderNumber], [t1].[PurchaseOrderNumber], [t1].[AccountNumber], [t1].[CustomerID], [t1].[ContactID], [t1].[SalesPersonID], [t1].[TerritoryID], [t1].[BillToAddressID], [t1].[ShipToAddressID], [t1].[ShipMethodID], [t1].[CreditCardID], [t1].[CreditCardApprovalCode], [t1].[CurrencyRateID], [t1].[SubTotal], [t1].[TaxAmt], [t1].[Freight], [t1].[TotalDue], [t1].[Comment], [t1].[rowguid], [t1].[ModifiedDate]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[SalesOrderID], [t0].[RevisionNumber], [t0].[OrderDate], [t0].[DueDate], [t0].[ShipDate], [t0].[Status], [t0].[OnlineOrderFlag], [t0].[SalesOrderNumber], [t0].[PurchaseOrderNumber], [t0].[AccountNumber], [t0].[CustomerID], [t0].[ContactID], [t0].[SalesPersonID], [t0].[TerritoryID], [t0].[BillToAddressID], [t0].[ShipToAddressID], [t0].[ShipMethodID], [t0].[CreditCardID], [t0].[CreditCardApprovalCode], [t0].[CurrencyRateID], [t0].[SubTotal], [t0].[TaxAmt], [t0].[Freight], [t0].[TotalDue], [t0].[Comment], [t0].[rowguid], [t0].[ModifiedDate]) AS [ROW_NUMBER], [t0].[SalesOrderID], [t0].[RevisionNumber], [t0].[OrderDate], [t0].[DueDate], [t0].[ShipDate], [t0].[Status], [t0].[OnlineOrderFlag], [t0].[SalesOrderNumber], [t0].[PurchaseOrderNumber], [t0].[AccountNumber], [t0].[CustomerID], [t0].[ContactID], [t0].[SalesPersonID], [t0].[TerritoryID], [t0].[BillToAddressID], [t0].[ShipToAddressID], [t0].[ShipMethodID], [t0].[CreditCardID], [t0].[CreditCardApprovalCode], [t0].[CurrencyRateID], [t0].[SubTotal], [t0].[TaxAmt], [t0].[Freight], [t0].[TotalDue], [t0].[Comment], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [Sales].[SalesOrderHeader] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
43669 43670 43671 43672 43673 43674 43675 43676 43677 43678

Page 3:
SELECT [t1].[SalesOrderID], [t1].[RevisionNumber], [t1].[OrderDate], [t1].[DueDate], [t1].[ShipDate], [t1].[Status], [t1].[OnlineOrderFlag], [t1].[SalesOrderNumber], [t1].[PurchaseOrderNumber], [t1].[AccountNumber], [t1].[CustomerID], [t1].[ContactID], [t1].[SalesPersonID], [t1].[TerritoryID], [t1].[BillToAddressID], [t1].[ShipToAddressID], [t1].[ShipMethodID], [t1].[CreditCardID], [t1].[CreditCardApprovalCode], [t1].[CurrencyRateID], [t1].[SubTotal], [t1].[TaxAmt], [t1].[Freight], [t1].[TotalDue], [t1].[Comment], [t1].[rowguid], [t1].[ModifiedDate]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[SalesOrderID], [t0].[RevisionNumber], [t0].[OrderDate], [t0].[DueDate], [t0].[ShipDate], [t0].[Status], [t0].[OnlineOrderFlag], [t0].[SalesOrderNumber], [t0].[PurchaseOrderNumber], [t0].[AccountNumber], [t0].[CustomerID], [t0].[ContactID], [t0].[SalesPersonID], [t0].[TerritoryID], [t0].[BillToAddressID], [t0].[ShipToAddressID], [t0].[ShipMethodID], [t0].[CreditCardID], [t0].[CreditCardApprovalCode], [t0].[CurrencyRateID], [t0].[SubTotal], [t0].[TaxAmt], [t0].[Freight], [t0].[TotalDue], [t0].[Comment], [t0].[rowguid], [t0].[ModifiedDate]) AS [ROW_NUMBER], [t0].[SalesOrderID], [t0].[RevisionNumber], [t0].[OrderDate], [t0].[DueDate], [t0].[ShipDate], [t0].[Status], [t0].[OnlineOrderFlag], [t0].[SalesOrderNumber], [t0].[PurchaseOrderNumber], [t0].[AccountNumber], [t0].[CustomerID], [t0].[ContactID], [t0].[SalesPersonID], [t0].[TerritoryID], [t0].[BillToAddressID], [t0].[ShipToAddressID], [t0].[ShipMethodID], [t0].[CreditCardID], [t0].[CreditCardApprovalCode], [t0].[CurrencyRateID], [t0].[SubTotal], [t0].[TaxAmt], [t0].[Freight], [t0].[TotalDue], [t0].[Comment], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [Sales].[SalesOrderHeader] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [20]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
43679 43680 43681 43682 43683 43684 43685 43686 43687 43688 

Note the difference in form between the Page 1 SQL statement which does a Skip(0) and hence just uses TOP to achieve the Take(10) compared with Page 2 and 3 that need to get a little more complicated. There are two companion functions SkipWhile() and TakeWhile() that allow you to apply a bool condition mix as well.
Having such simple-to-use functions abstract away the complexity of the SQL underneath provides a real boost to developer productivity.
Keep in mind that all the above functions are part of LINQ, not special to LINQ-to-SQL and as such will work with all LINQ data sources. Very Nice!

LINQ to SQL: Insert/Update/Delete

I have been looking at my web stats for the recent run of LINQ-to-SQL posts, and it seems a lot of people are making their way here from searches about some of the more standard features of LINQ-to-SQL. In the interest of addressing these visitors I am going to put together a post that covers the basics of data access.
Make sure you see my earlier post about setting up the AdventureWorks database here.
First we will look at a complicated INSERT, adding a new customer to the AdventureWorks database. 
Note: This is not a very good example from the standpoint of keeping the AdventureWorks database clean and correct, we are only interested in meeting each of the SQL Constraints, not the business logic.

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;

// LINQ query to get StateProvince
StateProvince state = (from states in db.StateProvinces
                       where states.CountryRegionCode == "AU" && states.StateProvinceCode == "NSW"
                       select states).FirstOrDefault();
// LINQ function to get AddressType
AddressType addrType = db.AddressTypes.FirstOrDefault(s => s.Name == "Home");

Customer newCustomer = new Customer()
{
    ModifiedDate= DateTime.Now,
    AccountNumber= "AW12354", 
    CustomerType='I',
    rowguid= Guid.NewGuid(),
    TerritoryID= state.TerritoryID    // Relate record by Keys
};
Contact newContact = new Contact()
{
    Title = "Mr",
    FirstName = "New",
    LastName = "Contact",
    EmailAddress = "newContact@company.com",
    Phone = "(12) 3456789", 
    PasswordHash= "xxx",
    PasswordSalt= "xxx",
    rowguid = Guid.NewGuid(),
    ModifiedDate = DateTime.Now
};
Individual newInd = new Individual()
{
    Contact= newContact,    // Relate records by objects (we dont actually know the Keys for the new records yet)
    Customer= newCustomer,
    ModifiedDate= DateTime.Now
};
Address newAddress = new Address()
{
    AddressLine1= "12 First St",
    City= "Sydney",
    PostalCode= "2000", 
    ModifiedDate=DateTime.Now,
    StateProvince= state,
    rowguid = Guid.NewGuid()
};

// Link our customer with their address via a new CustomerAddress record
newCustomer.CustomerAddresses.Add(new CustomerAddress() { Address = newAddress, Customer = newCustomer, AddressType = addrType, ModifiedDate = DateTime.Now, rowguid = Guid.NewGuid() });

// Save changes to the database
db.SubmitChanges();

Console.WriteLine("Customer ID - " + newCustomer.CustomerID.ToString());

This code generates and executes the following SQL.

SELECT TOP (1) [t0].[StateProvinceID], [t0].[StateProvinceCode], [t0].[CountryRegionCode], [t0].[IsOnlyStateProvinceFlag], [t0].[Name], [t0].[TerritoryID], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Person].[StateProvince] AS [t0]
WHERE ([t0].[CountryRegionCode] = @p0) AND ([t0].[StateProvinceCode] = @p1)
-- @p0: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [AU]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [NSW]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT TOP (1) [t0].[AddressTypeID], [t0].[Name], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Person].[AddressType] AS [t0]
WHERE [t0].[Name] = @p0
-- @p0: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Home]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

INSERT INTO [Person].[Address]([AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [rowguid], [ModifiedDate])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [12 First St]
-- @p1: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p2: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Sydney]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [50]
-- @p4: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [2000]
-- @p5: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [75061158-10f5-4fbc-8ab8-afaac45432ec]
-- @p6: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

INSERT INTO [Sales].[Customer]([TerritoryID], [CustomerType], [rowguid], [ModifiedDate])
VALUES (@p0, @p1, @p2, @p3)

SELECT [t0].[CustomerID], [t0].[AccountNumber]
FROM [Sales].[Customer] AS [t0]
WHERE [t0].[CustomerID] = (SCOPE_IDENTITY())
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [9]
-- @p1: Input NChar (Size = 1; Prec = 0; Scale = 0) [I]
-- @p2: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [6aa7321f-97ed-4374-bb4f-1dbade6c54b3]
-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

INSERT INTO [Sales].[CustomerAddress]([CustomerID], [AddressID], [AddressTypeID], [rowguid], [ModifiedDate])
VALUES (@p0, @p1, @p2, @p3, @p4)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [29487]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [32529]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p3: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [7b475a95-eb2b-42bb-9291-2f75d3afb9c6]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

INSERT INTO [Person].[Contact]([NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailAddress], [EmailPromotion], [Phone], [PasswordHash], [PasswordSalt], [AdditionalContactInfo], [rowguid], [ModifiedDate])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
-- @p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [Mr]
-- @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [New]
-- @p3: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p4: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Contact]
-- @p5: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p6: Input NVarChar (Size = 22; Prec = 0; Scale = 0) [newContact@company.com]
-- @p7: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p8: Input NVarChar (Size = 12; Prec = 0; Scale = 0) [(12) 3456789]
-- @p9: Input VarChar (Size = 3; Prec = 0; Scale = 0) [xxx]
-- @p10: Input VarChar (Size = 3; Prec = 0; Scale = 0) [xxx]
-- @p11: Input Xml (Size = 0; Prec = 0; Scale = 0) [System.Data.SqlTypes.SqlXml]
-- @p12: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [85ae7a1f-fdc9-4b20-b8aa-0ca6a8007022]
-- @p13: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

INSERT INTO [Sales].[Individual]([CustomerID], [ContactID], [Demographics], [ModifiedDate])
VALUES (@p0, @p1, @p2, @p3)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [29487]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [19980]
-- @p2: Input Xml (Size = 0; Prec = 0; Scale = 0) [System.Data.SqlTypes.SqlXml]
-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 11:34:04 AM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Customer ID - 29487

One of the first things to know is that SubmitChanges() will wrap up all of the changes you have made to the database and submit them as a single transaction, this is good news because if any of them changes fail, they all fail. (Exceptions are thrown to catch failures)
You may note the lack of any explicit InsertOnSubmit() calls, these are largely optional, though there are benefits from using them that you will see further down in the DELETE example.
Running through the code snippet, you will see that the first thing we do is find a StateProvince record and an AddressType record, these are required to give the appropriate foreign keys to our Address record.
Creating a new record keeps with the mind set of working with objects, so you simply create a new object of the appropriate record type and set its properties.
When linking two records together, you have two choices, both of which I have given an example of. If you know the keys involved in the relationship you can explicitly set the Foreign Key field eg "TerritoryID = state.TerritoryID".
However, you do not always know the key, especially if the record has just been created, so you can actually link two objects together and LINQ-to-SQL will work out the keys for you. You can see this best when I create the Individual record and attach it to newContact and newCustomer, neither of which is in the database yet, and hence neither has a Primary Key.
The CustomerAddress table is a Many-to-Many relationship table between Customer and Address. We can add a new record to it directly or via the CustomerAddresses collection that is on both Customer and Address records. I have used in-line syntax to create this record simply to show off another way of structuring your code.
Right at the end we write out the Primary Key for the Customer record, to show that LINQ-to-SQL will automatically query this value after it has inserted the record and pop it onto the object. Very useful.
The other cool feature here is that the order of the SQL statements is decided for you to ensure that all of the keys can be correctly set without tripping up the constraints along the way.
Update statements are even simpler, again keeping with the mindset of working with objects we simply get an object representing a row, changes it properties and save it back.

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;

// Get hte first customer record
Customer c = (from cust in db.Customers select cust).FirstOrDefault();
Console.WriteLine(c.CustomerType);
c.CustomerType = 'I';
db.SubmitChanges(); // Save the changes away

SELECT TOP (1) [t0].[CustomerID], [t0].[TerritoryID], [t0].[AccountNumber], [t0].[CustomerType], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Sales].[Customer] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

S
UPDATE [Sales].[Customer]
SET [CustomerType] = @p5
WHERE ([CustomerID] = @p0) AND ([TerritoryID] = @p1) AND ([CustomerType] = @p2) AND ([rowguid] = @p3) AND ([ModifiedDate] = @p4)

SELECT [t1].[AccountNumber]
FROM [Sales].[Customer] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CustomerID] = @p6)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p2: Input NChar (Size = 1; Prec = 0; Scale = 0) [S]
-- @p3: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [3f5ae95e-b87d-4aed-95b4-c3797afcb74f]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [13/10/2004 11:15:07 AM]
-- @p5: Input NChar (Size = 1; Prec = 0; Scale = 0) [I]
-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Now for a Delete, first we will create a record that we can later delete. This will show you why using InsertOnSubmit() explicity can be a good idea.

AdventureWorksDataContext db = new AdventureWorksDataContext();
db.Log = Console.Out;
Console.WriteLine("Count Start - " + db.Currencies.Count().ToString());
Currency c = new Currency()
{
    CurrencyCode = "XXX",
    Name = "My Currency",
    ModifiedDate = DateTime.Now
};
db.Currencies.InsertOnSubmit(c);
db.SubmitChanges();
Console.WriteLine("Count Middle - " + db.Currencies.Count().ToString());

db.Currencies.DeleteOnSubmit(c);
db.SubmitChanges();
Console.WriteLine("Count End - " + db.Currencies.Count().ToString());

SELECT COUNT(*) AS [value]
FROM [Sales].[Currency] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Count Start - 105
INSERT INTO [Sales].[Currency]([CurrencyCode], [Name], [ModifiedDate])
VALUES (@p0, @p1, @p2)
-- @p0: Input NChar (Size = 3; Prec = 0; Scale = 0) [XXX]
-- @p1: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [My Currency]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 12:09:17 PM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT COUNT(*) AS [value]
FROM [Sales].[Currency] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Count Middle - 106
DELETE FROM [Sales].[Currency] WHERE ([CurrencyCode] = @p0) AND ([Name] = @p1) AND ([ModifiedDate] = @p2)
-- @p0: Input NChar (Size = 3; Prec = 0; Scale = 0) [XXX]
-- @p1: Input NVarChar (Size = 11; Prec = 0; Scale = 0) [My Currency]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2/01/2008 12:09:17 PM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT COUNT(*) AS [value]
FROM [Sales].[Currency] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

Count End - 105

You may notice here that I have used InsertOnSumbit() when creating my currency record to be deleted, this is because by doing this you "attach" the object to the database, which is required to do some operations with that object later on, such as DeleteOnSubmit(). It was not required in my Insert() example however. If you attempt to use DeleteOnSubmit() with an object that is not attached to your data context, it will throw an exception.
There is also a DeleteAllOnSubmit() method which takes a collection of records to be deleted.
So there you have it, a quick look at three basic functions. The one of real interest should be the INSERT statements and how you link up a complex set of related objects to insert into the database.
I will have another post similar to this next that looks at functions like Count(), Sum(), Average() etc. Again I have had some hits from people looking for examples of these so I feel I should cover them off before moving on to future topics.