LINQ to SQL: Custom Queries

Now I have some of the foundations out of the way, albeit in a rather brief overview that assumes a reasonable level of competency, it is time to move onto some of the more interesting code snippets.
One thing I never liked much about writing SQL in either FoxPro or with PassThrough technologies is how you piece together a complex query from a number of UI selections. The most common occurrence of this is in reporting. The number of ways you can usually slice and dice a sales report makes for some fairly nasty code to build a string based SQL statement. LINQ-to-SQL offers us a new paradigm for dealing with this sort of problem.
First we need to add a few options to our UI in Window1.xaml

<Window x:Class="AdventureWorks.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="Window1" Height="233" Width="534">
    <Grid>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="255*" />
            <ColumnDefinition Width="257*" />
        </Grid.ColumnDefinitions>
        <StackPanel Grid.Column="0">
            <CheckBox Name="chkOnline">Online Orders Only</CheckBox>
            <StackPanel Orientation="Horizontal">
                <Label Width="50">From</Label>
                <TextBox Width="200" Name="dateFrom">01/01/2001</TextBox>
            </StackPanel>
            <StackPanel Orientation="Horizontal">
                <Label Width="50">To</Label>
                <TextBox Width="200" Name="dateTo">31/12/2001</TextBox>
            </StackPanel>
            <StackPanel Orientation="Horizontal">
                <Label Width="50">City</Label>
                <TextBox Name="city" Width="200"></TextBox>
            </StackPanel>
        </StackPanel>
        <Button Name="button1" Click="button1_Click" Grid.Column="1">Button</Button>
    </Grid>
</Window>

Then we replace the click event with the following code.

private void button1_Click(object sender, RoutedEventArgs e)
{
    AdventureWorksDataContext db = new AdventureWorksDataContext();
    db.Log = Console.Out;
    DateTime dFrom;
    DateTime dTo;
    var query = db.SalesOrderHeaders.AsQueryable();

    if ((bool)chkOnline.IsChecked)
        query = query.Where(s => s.OnlineOrderFlag == true);
    if (DateTime.TryParse(dateFrom.Text, out dFrom))
        query = query.Where(s => s.OrderDate >= dFrom);
    if (DateTime.TryParse(dateTo.Text, out dTo))
        query = query.Where(s => s.OrderDate <= dTo);
    if (city.Text.Length > 0)
        query = query.Where(s => s.ShipToAddress.City == city.Text);

    var results = from sale in query
                  select new
                  {
                      OrderID = sale.SalesOrderNumber,
                      OrderValue = sale.SubTotal + sale.Freight,
                      City = sale.ShipToAddress.City
                  };
    if (results.Count() >= 1)
    {
        var rec = results.First();
        Console.WriteLine(rec.OrderID + " - " + rec.City + " - $" + rec.OrderValue);
    }
}

Note: This code snippet assumes you have made the changes to your data model as discussed here.
If we set this up, check the Online Order box and enter the city Seattle we get the following SQL generated. Note that because I only access the first record, LINQ-to-SQL executes a TOP 1. I hope you will agree this is pretty neat.

SELECT TOP (1) [t2].[SalesOrderNumber] AS [OrderID], [t2].[value] AS [OrderValue], [t2].[City]
FROM (
    SELECT [t0].[SalesOrderNumber], [t0].[SubTotal] + [t0].[Freight] AS [value], [t1].[City], [t0].[OrderDate], [t0].[OnlineOrderFlag]
    FROM [Sales].[SalesOrderHeader] AS [t0]
    INNER JOIN [Person].[Address] AS [t1] ON [t1].[AddressID] = [t0].[ShipToAddressID]
    ) AS [t2]
WHERE ([t2].[City] = @p0) AND ([t2].[OrderDate] <= @p1) AND ([t2].[OrderDate] >= @p2) AND ([t2].[OnlineOrderFlag] = 1)
-- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Seattle]
-- @p1: Input DateTime (Size = 0; Prec = 0; Scale = 0) [31/12/2001 12:00:00 AM]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [1/01/2001 12:00:00 AM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8

SO43768 - Seattle - $3667.7268

Now a few cool things to note. First, I did not specify the join. LINQ-to-SQL has seen my usage of the SalesOrderHeader.ShipToAddress.City field, and knows that to access that field it needs to JOIN to the address table using the ShipToAddressID field, because of this had I not used the field due to some set up of the conditions, no join would have been made. Very nice, especially if we are dealing with numerous tables that only become relevant if the user makes a certain selection.
The extension of a code snippet like this is to have multiple "query" variables. These should be viewed more so as filters, so I could have an order filter, customer filter, product filter. Each filter could be optionally whittled down with Where() statements and then the final LINQ query could express the general relationship between each filter and the projection (select) required from it.
There is one very important piece of information that also needs discussing here, the difference between IEnumerable and IQueryable. In the above code snippet, if you use .AsEnumerable() instead of .AsQueryable() although you will see the same result, the performance will be greatly impacted, this is because all of the Where() clauses will be evaluated on the Client instead of passed to the Sever as SQL. You can think of the difference as being immediate or Just-In-Time execution. There is obviously more to it than that and the Help has much to say on the topic. For our purposes however you simply need to be aware that there is a difference and you should watch out for it.

LINQ to SQL: Lambda Expressions

At first glance, Lambda Expressions are bound to confuse most people. Myself included. However, a little digging and experimenting will show they are a simple enough concept. I will cover them now to try remove any confusion in later code snippets.
Lets have a look at one of the overloads for the Where() method.
Note: All clauses in a LINQ expression can also be expressed as methods. They behave identically and are interchangeable, even within a LINQ expression!

You will see the parameter "predicate" is of type Func<SaleOrderHeader,bool>
What this means is that you pass the Where() method an anonymous function that takes a SaleOrderHeader object (representing a row from the table) and returns a bool that indicates is this row is to be included in the results.
The old (cumbersome) way of specifying this function was as follows

AdventureWorksDataContext db = new AdventureWorksDataContext();
Func<SalesOrderHeader, bool> f = delegate(SalesOrderHeader s){return s.OnlineOrderFlag;};
db.SalesOrderHeaders.Where(f);

What Lambda Expressions provide is an inline succinct way of achieving exactly the same result.

AdventureWorksDataContext db = new AdventureWorksDataContext();
Func<SalesOrderHeader, bool> f = s => s.OnlineOrderFlag;
db.SalesOrderHeaders.Where(f);
// or //
db.SalesOrderHeaders.Where(s => s.OnlineOrderFlag);

Reading a Lambda Expression is simple enough, the expression is separated into two parts either side of the "=>" operator. The left hand side defines the parameters, the types of which are usually implied. The right hand side is an expression that uses these parameters and evaluates to the defined return type. The whole expression is type checked, so intellisense will help you out, and the compiler will flag any problems.
Lambda Expressions are not unique to LINQ, they can be used anywhere you might already use an anonymous method. What they provide LINQ however is a far more readable syntax.
One last note, because LINQ uses delegates it gives you an opportunity to build logic into your queries that is quite useful and readable.

AdventureWorksDataContext db = new AdventureWorksDataContext();
Func<SalesOrderHeader, bool> f;
bool b = true;
if (b)
    f = s => s.OrderDate == DateTime.Now;
else
    f = s => s.OrderDate < DateTime.Now;

var q = db.SalesOrderHeaders.Where(f);

As this code snippet shows, applying different filters based on a condition is now a fairly simple task. The variable "q" of type IEnumerable<SalesOrderHeader> will contain all the orders either from today, or before today, depending on the value of b.
I will be using this technique more often in future posts, and it is an important concept to understand, otherwise your queries are going to be difficult to piece together.

LINQ to SQL: Customisation

Before we proceed, this is a good time to see another feature of LINQ-to-SQL which allows you to change the property names of the generated classes to no longer match the underlying field names, this could be useful if you have a strange naming convention for your field names, or in our case with AdventureWorks we have instances like on the SalesOrderHeader class where it has two links to the Address table (BillTo and ShipTo) but these relationships get modelled Address and Address1.
This is obviously undesirable as there is no clear indication which one links to Address based on ShipToAddressID and which on BillToAddressID.
Luckily we have a solution close at hand.
Open up the LINQ-to-SQL diagram and find the two relationships between the SalesOrderHeader table and the Address table (Not a simple task, you may need to drag one of the two tables into the open to find its links, or use the Drop-Down above the property box to select it that way). The property box should look like this

Change it to something more useful.

We now have a SalesOrderHeader.ShipToAddress property available. Do the same for the Address/BillToAddress property. My later code samples will assume this change has been made.
There are other places in the database this would be useful, but we will address them as they come up.
Note: One of my few gripes about LINQ-to-SQL is that if you rebuild the classes from scratch (the easiest way to update changes from the database) you will lose these edits. It would be nice to be able to save them away somehow or have a "Scan for changes" feature. It would also be nice if the wizard could detect the situation above (which is quite common) and handle it cleanly.

LINQ to SQL: A Step Back

In this, my third article on LINQ-to-SQL, I am going to be taking a step away from the code and delve into a discussion about what I will think most people will miss when they first approach this new technology.
What LINQ-to-SQL does is abstract the database away from the developer. It then provides a truly OO interface to your data.
To take the view that LINQ-to-SQL lets you integrate a query straight into your code is to miss the point entirely.
Briefly, the wizard creates a new type for each table that represents a single record in the table with a property for each field. A top-level Data Context object is then created that acts as the starting point for your data access. To it is added a Collection for every table in the database. These Collections are typed to only accept objects of the appropriate record type. The Orders collection will only work with Order objects.
What this achieves is data access that is type checked every step of the way. Tables, Fields, Stored Procs. Every read/write/delete is properly type checked. Something sorely missing from 1st party data access in .NET all these years.
Where things get really special though, is in the mapping of relationships. If your database has an Order table with a relationship defined to a Customer table using a CustomerID field. Your Order type in LINQ-to-SQL will have two properties, a CustomerID property (likely you have used an int) and a Customer property that is of type Customer.
This allows you to find the name of the customer from an order, in a very OO manner myOrder.Customer.CustomerName
LINQ-to-SQL uses the relationship information to generate and execute a SQL statement that joins these two tables based on CustomerID and pull off the appropriate field. Take a moment here to contrast this with what you would need to do to achieve this with your current data access technology.
(This is where someone writes in to tell me about Framework X and its amazing data layer. Glad to hear it.)
The important mental hurdle people will face is being able to stop thinking in SQL and start thinking in Objects. The benefits of doing so will be faster development, and thanks to type checking it will also be safer. I also believe it will greatly reduce the time it takes to bring a new developer up to speed on the database layout, they will be able to learn through intellisense as they go and avoid learning each table name, field name and join condition the old way (with a giant diagram).
I am reminded of an old lecturer of mine that promoted storing data in serialised collections as a better mechanism than databases for some convoluted reason involving ease-of-use and a total lack of understanding indexes. I promptly pointed out the complete lack forethought in this notion, but I guess he could now have the best of both worlds.
Viewing a database as a giant connected set of objects and collections, brings it into line with how all other data is stored and accessed in applications. Passing objects around an application especially between a Presentation and Business Layer is very natural. Adding an object to a collection instead of serialising the object into an INSERT statement is equally natural. The same applies for deleting, sorting, filtering and many other standard SQL operations. We "get" objects, and although we may also "get" SQL there has long been a disconnect, two mind sets we needed to use.
I am only scratching the surface on how LINQ-to-SQL will change the way you code. I am going to dig into building custom queries from UI selections (think about building the queries behind a complex reports using the old SQL string building methods), extending the base functionality of LINQ-to-SQL with Partial Classes and Partial Methods plus a whole host of other examples and techniques.

LINQ to SQL: Getting Started

I am going to start by showing a few new language features that are important to LINQ (and hence LINQ-to-SQL) that you may not have come across yet if you are not already playing around with this stuff.
A great place to start is to bring up intellisense and have a look at the structure of the classes that have been created for you.

Each table in the database is given a property on the Data Context object. The type of this property (using Addresses as the example) will be System.Data.Linq.Table<Address>. This class implements the IEnumerable<> and IQueryable<> interfaces that provides the underlying LINQ functionality.

You will note a Count() method here, as well as a number of other interesting functions such as Average(), Contains() and DeleteAllOnSubmit(). We will take a look at a number of these later. First however I want to talk about what the <> symbols that are being scattered around.
When we define a class, for example List, as List<MyCustomClass> it tells the compiler that this List instance should substitute MyCustomClass for object in its definition. The actual definition of the List class decides where and how the Type is used, but we will not go into that detail here. What this means is that you can have a List whose methods will take/return the objects of type MyCustomClass without messing around with casting to/from object or creating a custom sub-class.
This technique is used heavily in LINQ-to-SQL to provide strong compile time type checking.
What this means for you in practice is that if you try to add an Order record to the Address table, it will pick this mistake up at compile-time, not with a run-time error.
Now lets put a few things together and add a record to one of the tables.

private void button1_Click(object sender, RoutedEventArgs e)
{
    var db = new AdventureWorksDataContext();
    System.Windows.MessageBox.Show(db.AddressTypes.Count().ToString()); // 6
    var x = new AddressType()
    {
        Name= "New Address Type",
        ModifiedDate= DateTime.Now
    };
    db.AddressTypes.InsertOnSubmit(x);    // Queues up an insert
    db.SubmitChanges();    // Submits all the changes in a single transaction
    System.Windows.MessageBox.Show(db.AddressTypes.Count().ToString()); // 7
}

The first question, some may ask, is what is that "var" keyword. Well rest easy, C# has not introduced some new variant type, var tells the compiler to look at the assignment expression and determine the type to assign to the new variable.
var x = new AddressType() is absolutely identical to AddressType x = new AddressType(), its just cleaner to read and easier to write. It has one other use, that we will see later.
Also of interest is the way I have set the property values for my new AddressType record. Again this is just a cleaner way of setting up a class and assigning properties to it.
You gain no benefit from using either of these new tricks other than cleaner (in my opinion) code.
Let's now put together something a little more complex (and contrived)

private void button1_Click(object sender, RoutedEventArgs e)
{
    var db = new AdventureWorksDataContext();
    db.Log = Console.Out;
    var query = from c in db.Customers
                where c.CustomerAddresses.Count(a => a.Address.City == "Seattle") > 0
                select new 
                { 
                    AccountNumber= c.AccountNumber,
                    SaleValue = c.SalesOrderHeaders.Sum(o => o.SubTotal)
                };
    foreach (var customer in query)
    {
        Console.WriteLine(customer.AccountNumber + " - " + customer.SaleValue.ToString());
    }
}

First things first, by hooking up db.Log to Console.Out, we can see the SQL statements that are being executed. You can just as easily hook this up to output to a file.
What this statement does is find customers with an address in Seattle, and sum up their orders. There are two new features here that need to be discussed.
First we have the Lambda Expression a => a.City == "Seattle". Lambda Expressions basically offer and in-line delegate method. In the case of the Count() method, you can pass it an expression that will take one parameter (in this case "a") of type "CustomerAddress" (CustomerAddresses is an IEnumerable<CustomerAddress>) and return a boolean value to indicate whether this element in the collection should be counted. Because intellisense knows the type of "a" from the definition of the class, it gives you full support. Exactly how a.Address works, we will see later, suffice to say there is no Address field in the CustomerAdress table, but there is a relationship from CustomerAddress to Address defined in the database schema.
Second we have the select new {} piece of code, you will note this uses a similar constructor to what I used above when creating my AddressType object, in this case we are actually creating an in-line type definition. Our type will have two properties, that we use in the foreach loop below, and comes with full intellisense support!!
This is where the "var" keyword really comes into play, because we are defining an Anonymous Type, we do not actually have a type we can provide up-front when declaring our object, but the compiler can use the expression to determine the type and provide us with intellisense and type checking.
Another point worth noting is that the LINQ syntax is just a wrapper for the underlying IEnumerable/IQueryable methods and that statements can be written using either.

var query = db.Customers.Where(c => c.CustomerAddresses.Count(a => a.Address.City == "Seattle") > 0).Select(c => new { AccountNumber = c.AccountNumber, SaleValue = c.SalesOrderHeaders.Sum(o => o.SubTotal) });

Last of all, lets see the output generated. I hope you will agree they have done a fantastic job converting a rather obtuse query like the one above into solid SQL.

SELECT [t0].[AccountNumber], (
    SELECT SUM([t3].[SubTotal])
    FROM [Sales].[SalesOrderHeader] AS [t3]
    WHERE [t3].[CustomerID] = [t0].[CustomerID]
    ) AS [SaleValue]
FROM [Sales].[Customer] AS [t0]
WHERE ((
    SELECT COUNT(*)
    FROM [Sales].[CustomerAddress] AS [t1]
    INNER JOIN [Person].[Address] AS [t2] ON [t2].[AddressID] = [t1].[AddressID]
    WHERE ([t2].[City] = @p0) AND ([t1].[CustomerID] = [t0].[CustomerID])
    )) > @p1
-- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Seattle]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

AW00000001 - 102351.7966
AW00000146 - 872520.1608
AW00000236 - 649987.2813
AW00000397 - 178854.5230
.
.
.

That will cover us for this article. Next I am going to take a step back from the code and talk a little about the mind set you should be using with LINQ-to-SQL and hopefully help you understand just why I am so excited by it.

LINQ to SQL: Prep Work

Time for a series of technical articles I think.
There are a number of great articles and blogs online for LINQ-to-SQL already. A great place to start is Scott Guthrie’s blog
However, I am going to wade into the discussion with my own thoughts.
There are three pre-requisites for the examples I will be showing off
1. Visual Studio C# 2008 Express Edition (link)
2. SQL Server 2005 Express Edition (link)
3. AdventureWorks Sample Database (link)
All three should install in a straight forward manner. You can then create a new C# project inside Visual Studio and in the Database Explorer add a new connection to the AdventureWorks database file.

Note: Due to the connectivity restrictions of the Express editions, you will need to connect to the database file with a user instance. So do not attach the AdventureWorks database to your SQL Server/Express instance.
The next step is to add a set of LINQ-to-SQL classes to your project.
If you are new to all this you might not have made the distinction between LINQ and LINQ-to-SQL yet. So here is my 5 second overview.
LINQ is a set of extensions that provide a unified query syntax for various data structures such as Arrays and Collections among many others.
LINQ-to-SQL can pretty much be viewed as a Data Layer for a SQL Server back-end. There is a "wizard" that creates a set of classes by examining the structure of your database. This is what you need to do next and you fire it up by adding a New Item to your project.

Open up the Database Explorer, drill into the AdventureWorks tables, select them all and drag/drop onto the design area. When prompted about copying the database file into the project, I tend to answer no as it creates deployment issues in test. However, if you want to keep the original copy clean, this might not hurt. You could also take a copy of the original and connect to it.
Now finally we want to hook up a button on our XAML page to a method in the code behind page so we have a hook to run some code on.
Window1.xaml

<Window x:Class="AdventureWorks.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="Window1" Height="233" Width="534">
    <Grid>
        <Button Name="button1" Click="button1_Click">Button</Button>
    </Grid>
</Window>

Window1.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace AdventureWorks
{
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    public partial class Window1 : Window
    {
        public Window1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            AdventureWorksDataContext db = new AdventureWorksDataContext();

        }
    }
}

With the shell of our testing application now put together we can move on to actually seeing what LINQ-to-SQL is and why you should care about it.

Updates..coming soon

So my blog has been a bit quiet lately. The usual mix of being lazy, busy and just generally lacking anything exciting to blog about has kept me quiet.

I have however penned the first in a series of posts I have been planning on LINQ-to-SQL. Microsoft have put together something very special here and coupled with the numerous language changes they have introduced to support LINQ, I think they have made a major step forward in terms of how we deal with a database from the programming language.
LINQ-to-SQL is far more than just embedding SQL-like statements into your code, and besides I have been doing that for years in FoxPro, so it is not terribly exciting. It's a truly exciting first-party data layer for SQL Sever.

Once I have the first two or three articles put together I will post them up, I want to ensure decent continuity between posts hence the slight hold off on posting the already completed first post.

As a side note, I have started using Live Writer, and it looks great, so bear with me if things get a little screwy around here while posting my first few articles with it. Fingers crossed it goes smooth as silk.
On that note, excuse a quick test of the image upload/hosting from Live Writer, I'd rather know now if this is going to be an issue.

Edit: Looks like it worked! (now to test an edit)