Write Less, Do More,
But Keep It Simple : LINQ to SQL

Converting web site projects from Visual Studio 2008 Beta 2 to Visual Studio 2008 RTM

bschooley — Wed, 21 Nov 2007 15:44:38 GMT

The good news is that this conversion was a lot less painful than the move from the final beta of .Net 2.0 to it's released version. The only breaking changes I ran into were minor changes in LINQ to SQL. While I was converting web site projects, I imagine since my breaking changes were limited to LINQ to SQL, that these changes would hold true for other projects as well.

First, this is just one scenario, your personal experience may vary.

LINQ to SQL Designer and the DBML File Encoding

This change is self correcting for the most part. If you attempt to open a .dbml file or compile your app before making this change you will get a "Cannot load ..." error. When you click the "Ok" button, the dbml file will automatically be opened with the xml editor. All you need to do is click save, wait for the pause as it regenerates the classes, and then close the file. The error has to do with the utf encoding of the xml file.

Web.config Assembly Reference Change for System.Data.DataSetExtensions

Open your web.config file and find and change the version number in this line from 2.0:

<add assembly="System.Data.DataSetExtensions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

to 3.5:

<add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>

then save and close.

LINQ to SQL Add and Remove Method Name Changes

From here you can attempt to compile to find the remaining changes you need to make. The first ones you'll run into are the Add and Remove methods of the LINQ to SQL Table<> objects. The Add method was changed to InsertOnSubmit and the Remove method was changed to DeleteOnSubmit. While more wordy, the change was made to make them more explicitly describe their behavior.

LINQ to SQL OnValidate Partial Method Change

If you were using the OnValidate partial method, then you'll need to make a small change. But this time you may want to make some alterations to your code logic as well.

To put it simply, the OnValidate now has a ChangeAction parameter. This is a great change as it basically informs the method about the reason for the call. ChangeAction has four values: Insert, Update, Delete and None.

So, find and change all your:

partial void OnValidate()

to:

partial void OnValidate(ChangeAction action)

Depending on what your doing you may want to consider wrapping the contents of your method in a simple:

if (action == ChangeAction.Insert || action == ChangeAction.Update)
{
...
}

You may want to skip that for now and revisit it when you are ready to think through the implications of this change.

Conclusion

My test applications made use of extension methods, nested master pages and a few other 3.5 features, but so far I haven't run into any other breaking changes. If I run across more, I'll update this blog post.

LINQ : Paging and sorting LINQ Queries with a custom QueryDataSource

bschooley — Sat, 03 Nov 2007 07:48:50 GMT

So you've got a LINQ query and now you want to drop it on a GridView. If you assign it to the datasource and bind it, you'll get data, but if you've define a page size or sort expressions, as soon as you click something you'll get "The GridView fired event PageIndexChanging which wasn't handled."

At this point you have two options, you can punish yourself by writing and wiring up all the paging and sorting events, or you could try using the LinqDataSource.

The LinqDataSource is nice in that it will handle all the paging, sorting and more, but it comes at a cost. For starters, all that great intellisense you get when writing the query is out-the-window. I won't even broach the subject of business versus data versus UI layers. Let's just say the LinqDataSource is the Linq equivalent of the SqlDataSource, something many people avoid at all costs.

There must be another way.

Paging and Sorting are basic application plumbing problems and not something you should be concerned with when working at the page level. After trying to use the LinqDataSource a few times, I figured there must be a better way to do this. I wanted to write my query with all the benefits of Intellisense and in some cases I needed to conditionally add filters for search criteria. What I needed was a DataSource that I could pass a query to and let it handle the paging and sorting as needed by the GridView.

Writing the QueryDataSource control turned out to be easier than I expected. My first break was finding a nice simple example DataSource by Paul Wilson for his WIlsonORMapper. And second, finding this article by Raj Kaimal where he talks about Microsoft's Dynamic Expressions API that was released in the LINQ and C# language samples for Visual Studio 2008 beta 2.

After a couple hours of playing around I came up with the QueryDataSource control.

QueryDataSource

This control makes use of three simple features of LINQ queries, Skip, Take and OrderBy. I ended up not being able to "generically" apply these commands to IQueryable<T> expressions because during processing I don't know anything about <T>. That's where the Dynamic Expressions API library comes in. Quoting from the API docs regarding it's extension methods...

These methods correspond to their System.Linq.Queryable counterparts, except that they operate on IQueryable instead of IQueryable<T> and use strings instead of lambda expressions to express predicates, selectors, and orderings. IQueryable is the non-generic base interface for IQueryable<T>, so the methods can be used even when T isn’t known on beforehand...

Here's the heart of the QueryDataSource control, it's ExecuteSelect method:

protected override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments)

 arguments.RaiseUnsupportedCapabilitiesError(this);

 QueryDataSourceLoadSelectQueryEventArgs e = new QueryDataSourceLoadSelectQueryEventArgs(this._owner.Query);

 this.OnLoadSelectQuery(e);

 IQueryable query = this._owner.Query;

 if (arguments.RetrieveTotalRowCount)

 arguments.TotalRowCount = query.Count();

 if (!string.IsNullOrEmpty(arguments.SortExpression))

 query = query.OrderBy(arguments.SortExpression);

 if (arguments.StartRowIndex > 0)

 query = query.Skip(arguments.StartRowIndex);

 if (arguments.MaximumRows > 0)

 query = query.Take(arguments.MaximumRows);

 return query;

As with any paging function, this hits the database twice. First to get the TotalRowCount by executing "query.Count()" and then when it returns the query after adding the OrderBy, Skip and Take as required. Technically it doesn't actually execute the query a second time here, as it is passes the modified query object to the GridView and basically defers the execution.

Using the QueryDataSource

Here's a simple example web page using the QueryDataSource control.

<%@ Page Language="C#" MasterPageFile="~/Default.Master" %>

<script runat="server">

 void ds_Employees_OnLoadSelectQuery(object source, QueryDataSourceLoadSelectQueryEventArgs e)

 DB.NW.DataContext dc = new DB.NW.DataContext();

 e.Query = dc.Employees;

</script>

<asp:Content ID="c_Body" ContentPlaceHolderID="cph_Body" runat="server">

 <mps:QueryDataSource ID="ds_Employees" OnLoadSelectQuery="ds_Employees_OnLoadSelectQuery" runat="server" />

 <asp:GridView ID="gv_Employees" DataSourceID="ds_Employees" AutoGenerateColumns="false"

 AllowPaging="true" AllowSorting="true" PageSize="5" runat="server">

 <Columns>

 <asp:BoundField HeaderText="First Name" DataField="FirstName" SortExpression="FirstName" />

 <asp:BoundField HeaderText="Last Name" DataField="LastName" SortExpression="LastName" />

 </Columns>

 </asp:GridView>

</asp:Content>

This is about as simple as it gets. In your page markup you only need to provide the name of an event handler for the OnLoadSelectQuery event. And then in your code you create your query or retrieve it from another application layer and assign it to the EventArg's Query property.

I'm purposely not setting the query during OnLoad or other page life-cycle event because I want to wait until the GridView actually requests the data from the QueryDataSource. This should allow all the other page life-cycle events to process first so that if you change any variables or control values along the way, you won't need to rebind your GridView.

QueryDataSource isn't just for LINQ to SQL

Since we're just using standard LINQ expressions for paging and sorting, the QueryDataSource will actually work with any object that implements IQueryable or IQueryable<T>.

Here's an example that runs over a simple string array.

<%@ Page Language="C#" MasterPageFile="~/Default.Master" %>

<script runat="server">

 void ds_Names_OnLoadSelectQuery(object source, QueryDataSourceLoadSelectQueryEventArgs e)

 string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };

 e.Query = from n in names.AsQueryable()

 select new { FirstName = n };

</script>

<asp:Content ID="c_Body" ContentPlaceHolderID="cph_Body" runat="server">

 <mps:QueryDataSource ID="ds_Names" OnLoadSelectQuery="ds_Names_OnLoadSelectQuery" runat="server" />

 <asp:GridView ID="gv_Names" DataSourceID="ds_Names" AutoGenerateColumns="false"

 AllowPaging="true" AllowSorting="true" PageSize="3" runat="server">

 <Columns>

 <asp:BoundField HeaderText="First Name" DataField="FirstName" SortExpression="FirstName" />

 </Columns>

 </asp:GridView>

</asp:Content>

This could be a LINQ to XML, LINQ to Amazon, or just a LINQ over any collection of objects.

The possibilities are endless...

Downloading and Installing

I've created a simple ASP.Net Server Control project which you can compile and then reference in your web project or you can copy the two cs files to your app_code folder.

You can download the zip file here.

If you compile the project and copy the dll to your bin folder or if you add it to your solution and add a project reference, then you'll need to add the following to your web.config file to register the control.

<system.web>

 <pages>

 <controls>

 <add tagPrefix="mps" namespace="mps.WebControls" assembly="mps.WebControls.QueryDataSource" />

 </controls>

 </pages>

</system.web>

If you include the cs files in your app_code folder, then add the line above but leave out the assemply attribute.

You can change the tagPrefix as well, but if you want another namespace, you'll need to alter the source files.

The future of QueryDataSource

If someone else doesn't beat me to it, I'll probably add the ability to provide a TypeName, SelectMethod and SelectParameters much like an ObjectDataSource to allow direct access to an object layer that provides an IQueryable return value.

Beyond that, I may at some point extend the control to support separate Insert, Update and Delete queries. But since I seldom if ever use that type of functionality, I may never get around to it.

If anyone takes and extends this in either of these or other ways, please let me know.

Enjoy...

--Bernal

MSDN's 101 LINQ Samples

bschooley — Thu, 25 Oct 2007 16:24:27 GMT

I've been reading about, studying, and using LINQ to SQL for a couple months now and after all this time I somehow never ran across this MSDN web page.

101 LINQ Samples - C# Version

101 LINQ Samples - VB Version (has lots more content and sections on LINQ to SQL, etc)

After just a few minutes of digging through it's samples, I've already learned more than I care to admit. These samples help to show that LINQ is a lot deeper and more expressive than you might think at first glance.

[Update: 10-26]

After some additional searches and aimless browsing on the MSDN site I discovered there are multiple flavors and versions of the "101 LINQ Samples". I've updated the links above to include what I think are the most current pages.

Here are some other links to similar "101 LINQ Samples" pages that I think are out of date but may still have nuggets of value buried in them:

101 LINQ to Entities Samples - Downloadable Zip from the ADO.Net Team (out-of-date)

LINQ Samples from Orcas March CTP - Another Downloadable Zip (out-of-date)

LINQ and language samples for Visual Studio 2008 Beta 2 - Executable samples package from here

LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

bschooley — Sat, 29 Sep 2007 21:37:13 GMT

Most LINQ to SQL examples you find on the net tend to be written in a way that assumes a connected environment. I've found some blogs and posts that also talk about disconnected operations, but many of those are coming from the perspective of multiple tiers where the entity objects are being passed between tiers.

For this example, I've got a page that handles simple CRUD operations for a single employee record. It takes a query string parameter of "id". When it's not passed or "0" is passed, then it assumes you are going to be adding a new employee record, otherwise it loads the employee and allows you to edit it.

Note: I'm using module (page) level variables that are read from and stored in ViewState by overridden SaveViewState and LoadViewState. See my blog post on the subject.

Here's the OnLoad method...

protected override void OnLoad(EventArgs e)

 if (!IsPostBack)

 _EmployeeID = Convert.ToInt32(Request.QueryString["id"] ?? "0");

 if (_EmployeeID != 0)

 DB.NW.DataContext dc = new DB.NW.DataContext();

 DB.NW.Employee employee = dc.Employees.Single(em => em.EmployeeID == _EmployeeID);

 _Version = employee.Version;

 tb_FirstName.Text = employee.FirstName;

 tb_LastName.Text = employee.LastName;

 tb_HomePhone.Text = employee.HomePhone;

 base.OnLoad(e);

I'm going to assume you seen the reading of a single record from a LINQ to SQL DataContext, if not Scott Guthrie has a good introduction to LINQ to SQL you should read first. I'm also using the null coalescing operator, if that's new to you then again, Scott covered that too.

Notice here that I'm storing two values to local variables (ie ViewState). The _EmployeeID and the _Version. The version field is a row TimeStamp field I've added to the Employee table to facilitate concurrency checking on updates. I'll talk more about this later in this post.

Now for the good stuff. Here's the Save method...

protected void Save(object source, EventArgs e)

 DB.NW.DataContext dc = new DB.NW.DataContext();

 DB.NW.Employee employee = new DB.NW.Employee();

 if (_EmployeeID == 0)

 employee.FirstName = tb_FirstName.Text;

 employee.LastName = tb_LastName.Text;

 employee.HomePhone = tb_HomePhone.Text;

 dc.Employees.Add(employee);

 else

 employee.EmployeeID = _EmployeeID;

 employee.Version = _Version;

 dc.Employees.Attach(employee);

 employee.FirstName = tb_FirstName.Text;

 employee.LastName = tb_LastName.Text;

 employee.HomePhone = tb_HomePhone.Text;

try

 dc.SubmitChanges();

 Response.Redirect("~/Employees.aspx");

 catch (Exception ex)

 lbl_Message.Text = ex.Message;

Let's break it down.

When adding a new employee record.

Start by creating a DataContext and a new Employee object. Then set the properties based on the form control values and "Add" the employee object to the DataContext's Employees table. Finish with a SubmitChanges().

When updating things get a bit more interesting.

Start by setting the id and version properties of the new employee object and then "Attach" the it to the DataContext's Employees table. After it is attached, move the form control values to the employee object and finish by calling SubmitChanges().

It is important to understand why this sequence of steps is required for an update. The attach step is the key and it relates to how the DataContext tracks changes to objects. The "Attach" tells the DataContext that we have this employee object with an id of _EmployeeID and a row version timestamp of _Version and we want you to know about it and track any further changes so you can save them. When calling SubmitChanges(), the DataContext will create it's update statement and only include fields that changed after the object was attached.

Variations on the Update

While the code above is a complete workable approach, there are some assumptions and pre-requisites that led to it. First, the table has a timestamp field to track the row version for concurrency checking. If your table doesn't have one, in most cases I'd suggest adding it. If that's not possible then you have a few options, all of which involve more work on your part.

The easiest option is to turn off concurrency checking by changing the "update check" attribute for each field and setting it to "never". By default, LINQ to SQL sets the attribute to "always". This will allow the above code above to continue to work (just remove the lines dealing with the Version field). But removing concurrency checking isn't a good idea unless you intend to handle it yourself by adding your own code for it to the update process above.

What you shouldn't do is simply re-retrieve the employee record and change its properties. If you are handling the currency checking on your own via a property by property compare or a last updated date time value, you may think you are covered. The problem is that you are either trying to ignore concurrency checking or you are doing your own concurrency checking while still allowing LINQ to SQL to do it's concurrency checks as well so you may get a LINQ to SQL concurrency error if two users try to update a record a the same time, even if you are doing some amount of your own checking. It the end you either need to use LINQ to SQL's concurrency process or to turn it off.

Here's a bit more food for thought on this subject. Below are approaches to updates and the SQL statements generated by LINQ to SQL.

First, the code from above that makes use of a Version field and LINQ to SQL's concurrency checking...

DB.NW.Employee employee = new DB.NW.Employee();

employee.EmployeeID = _EmployeeID;

employee.Version = _Version;

dc.Employees.Attach(employee);

employee.FirstName = tb_FirstName.Text;

employee.LastName = tb_LastName.Text;

employee.HomePhone = tb_HomePhone.Text;

dc.SubmitChanges();

...generates...

UPDATE [dbo].[Employees]

SET [LastName] = @p2, [FirstName] = @p3, [HomePhone] = @p4

WHERE ([EmployeeID] = @p0) AND ([Version] = @p1)

Turning off update checks on your fields and removing the Version field leaves you vulnerable to concurrency issues, but here's the code...

DB.NW.Employee employee = new DB.NW.Employee();

employee.EmployeeID = _EmployeeID;

dc.Employees.Attach(employee);

employee.FirstName = tb_FirstName.Text;

employee.LastName = tb_LastName.Text;

employee.HomePhone = tb_HomePhone.Text;

dc.SubmitChanges();

...generates...

UPDATE [dbo].[Employees]

SET [LastName] = @p1, [FirstName] = @p2, [HomePhone] = @p3

WHERE [EmployeeID] = @p0

And last, if you leave the Update Check on and re-retrieve the employee record the code ...

employee = dc.Employees.Single(em => em.EmployeeID == _EmployeeID);

employee.FirstName = tb_FirstName.Text;

employee.LastName = tb_LastName.Text;

employee.HomePhone = tb_HomePhone.Text;

dc.SubmitChanges();

...generates...

UPDATE [dbo].[Employees]

SET [HomePhone] = @p15

WHERE ([EmployeeID] = @p0) AND ([LastName] = @p1) AND ([FirstName] = @p2)

AND ([Title] = @p3) AND ([TitleOfCourtesy] = @p4) AND ([BirthDate] = @p5)

AND ([HireDate] = @p6) AND ([Address] = @p7) AND ([City] = @p8)

AND ([Region] IS NULL) AND ([PostalCode] = @p9) AND ([Country] = @p10)

AND ([HomePhone] = @p11) AND ([Extension] = @p12) AND ([ReportsTo] = @p13)

AND ([PhotoPath] = @p14)

As you can see in the last of the three update statements, LINQ to SQL is sending every field as part of the update statement's where clause. I'll also point out here that in this case, LINQ to SQL does know that the FirstName and LastName fields were not changed to new values and therefore didn't include those in the update.

A Note on Change Tracking

This is a more advanced and situational concept, but one of the interesting possibilities that LINQ to SQL enables is the ability to build change tracking into the SubmitChanges() process. The basic idea is that any time you make changes to the database, you first go through the list of changes being made and record them in a generic change log table. I'll be writing about this idea in a later post, but for now, I'll throw out a warning that may effect how you handle updates.

When using the model I suggest at the beginning of this post, the DataContext doesn't know the original values of the three fields (FirstName, LastName and HomePhone) that are being set. As far as the DataContext is aware, the original values are null so, even if nothing is really changing, the fields will be added to the change set. This means when you go through the change set during SubmitChanges() the original values will be empty. So, if you want to really know, at a field level what is truly changing, you'll need to take a different approach that what is shown above.

Here I've stored the original values for all fields that can be updated and I set the employee object to those values before attaching it to the Employees table.

DB.NW.Employee employee = new DB.NW.Employee();

employee.EmployeeID = _EmployeeID;

employee.Version = _Version;

employee.FirstName = _FirstName;

employee.LastName = _LastName;

employee.HomePhone = _HomePhone;

dc.Employees.Attach(employee);

employee.FirstName = tb_FirstName.Text;

employee.LastName = tb_LastName.Text;

employee.HomePhone = tb_HomePhone.Text;

dc.SubmitChanges();

...generates...

UPDATE [dbo].[Employees]

SET [HomePhone] = @p2

WHERE ([EmployeeID] = @p0) AND ([Version] = @p1)

In this case, LINQ to SQL knows the original values for the properties and as in the third example I gave earlier it generates the update set statement with the HomePhone, which was the only field that changed. During the SubmitChanges, if you look into the ChangeSet and retrieve the list of changed properties, you'll find that only the HomePhone is given and it has the original and new values. This will enable you to record the changes in your generic change log table.

So, in order to do both change tracking at the field level and concurrency checking in a disconnected environment such as ASP.Net, you need to be able to create your employee object and set all the properties that you may be changing to their original values before you attach and then update those properties.

I'll be writing a post later that goes into more detail and presents the pros and cons on a few different approaches to tracking original values for objects that you will be updating.

LINQ to SQL : Start with a good foundation by defining a custom DataContext

bschooley — Fri, 28 Sep 2007 05:43:00 GMT

This is my first post in a what will become a series of posts on LINQ to SQL. As the title of my blog suggests, I'm going to be "thinking out loud" as I write these and I'm hoping for feedback from others on the pro's and con's of the approaches I'm suggesting. That said, the point of this series of posts will be to define a loose set of best practices around developing with LINQ to SQL in an ASP.Net web environment. The first couple posts will be pretty simplistic but I wanted to get these in place first so I can refer to the concepts later without having to go into detail.

On with the topic at hand...

When starting a new project with LINQ to SQL, it's a good idea to wrap the DataContext with your own class that inherits from the DataContext generated by the designer (or SQLMetal).

Before we do that though, let's take a small step back and talk about namespaces. In the designer, if you click on design surface background, you'll select the DataContext. In the properties window there will be two entries related to namespaces. One is the Context Namespace and the other is the Entity Namespace. Typically you'd put both the generated DataContext and the entity classes within the same namespace, but since we're going to be creating our own DataContext that inherits from the one that is generated for us, I'd suggest that we set these up with different namespaces.

I like to have my data layer to have its own namespace and as such I tend to go with a DB.such_and_such namespace where such_and_such is the name of our database or other logical context name. I'll be using the northwind database for this series of posts, so the logical namespace name is DB.NW. This is what we should put in the Entity Namespace property, but for the Context Namespace, we should set the value to DB.NW.Base to leave room for our custom version in the DB.NW namespace. With our DataContext's name set as simply DataContext as well, we'll get the following generated for us by the designer...

namespace DB.NW.Base
{
    ...
    public partial class DataContext : System.Data.Linq.DataContext
    {
    ...
    }

}
namespace DB.NW
{
    ...
    [Table...]
    public partial class table_name... etc.
}

Next we need to create a new class file in app_code. In this case I'll keep the name the same as the namespace it contains and go with DB.NW.cs.

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Runtime.Serialization;
using System.Reflection;

namespace DB.NW
{
    public class DataContext : DB.NW.Base.DataContext
    {
    }
}

Now, it may seem silly at this point to go through all this but doing this now, even with no custom or overridden methods, will save us from having to change our code later that when we need to override methods such as SubmitChanges()..

Write Less, Do More, But Keep It Simple : LINQ to SQL

Converting web site projects from Visual Studio 2008 Beta 2 to Visual Studio 2008 RTM

LINQ : Paging and sorting LINQ Queries with a custom QueryDataSource

MSDN's 101 LINQ Samples

101 LINQ Samples - C# Version

101 LINQ Samples - VB Version (has lots more content and sections on LINQ to SQL, etc)

LINQ to SQL : Handling disconnected CRUD operations in ASP.Net

LINQ to SQL : Start with a good foundation by defining a custom DataContext

Write Less, Do More,
But Keep It Simple : LINQ to SQL