Posts tagged with linq - page 4

Changing type, the state pattern and LINQ to SQL

A question I see from time-to-time on LINQ to SQL relates to changing an entity’s class.

C# and VB.NET don’t allow a class to change its type at run-time and LINQ to SQL specifically doesn’t provide a mechanism for changing the underlying discriminator for this reason.

Discarding the current object and creating a new one is fraught with issues. What do we do about existing references, unsaved data, established associations and caches?

Start with an example

Consider an abstract Account class with SavingsAccount and CurrentAccount sub-classes. Bank accounts don’t change type once created (in my experience) so that’s good so far.

When we get into processing and validation logic its tempting to create ClosedAccount and OpenAccount classes but what happens during execution when closing an account?

A further consideration is how exactly ClosedAccount and OpenAccount fit into the hierarchy given the single-inheritance limitation of C# and VB.NET.

Enter the State Pattern

The ever-resourceful Gang of Four describe the State Pattern as:

Allow an object to alter its behavior when its internal state changes. The object will appear to change its class.

Taking the bank accounts example and applying the state pattern gives:

State Pattern class diagram

Account no longer needs to change type at run-time yet is still able to have clients call Validate and process Methods that end up in discrete methods as if inheritance had been used.

To achieve this we have:

  1. Created a State hierarchy that contains the logic we need to change at run-time
  2. Introduced a private member in Account that points to the current state instance
  3. Ensured the Account methods call the current state class via the private member

Because the state member is private and only accessed by Account itself we can happily create and dispose it as the conditions that affect the state change as much as we like without worrying about references to it.

This is best illustrated with code. Here rather than just calling the state’s validation logic there is a combination of core Account validation (balance), state validation (closed) and CheckingAccount validation (transaction limits):

public abstract class Account {
  private AccountState state;

  public virtual Status Validate(ITransaction tx) {
    Status result = state.Validate(tx);
    if (tx.Amount > Balance)
    return result;

public class SavingsAccount : Account {
  public override Status Validate(ITransaction tx) {
    Status result = base.Validate(tx);
    if (Transactions.Count > TransactionLimit)
    return result;

public class ClosedAccountState : AccountState {
  public override Status Validate(ITransaction tx) {
    return new Status(TransactionFailures.InvalidSourceAccount);

This is less complex than selectively replacing objects within our application at run-time and can bring additional benefits:

Like all guidance, patterns and principles do not blindly follow these guidelines or patterns but consider how it affects and fits with your application. For this particular example it not only solves the problem but helps maintainability – at least at this simple stage. Once Validation becomes sufficiently complex it would likely move out entirely into a new set of orchestrated classes just for that.

With LINQ to SQL (and other mappers)

Moving this example into an object-relational mapper requires two – not unexpected – database-mapped properties.

  1. The inheritance discriminator (Type)
  2. A state indicator (Active)

Sample class diagrams for accounts using state pattern

The only thing we need to ensure is the Account’s state member always refers to either a ClosedAccountState or OpenedAccountState depending upon the Active flag.

Given that LINQ to SQL code-generates the property for Active we could:

  1. Make Active private, wrap it in another property and set the state member when it changes and at initialization
  2. Make the state member a read-only property instead of an instance variable

The second works well here and given that AccountState is itself stateless (perhaps not the best choice of name) we can use a singleton to avoid multiple instances. The state instance variable in the Account class is replaced with:

private AccountState State {
  get {
    if (Active)
      return OpenAccountState.Instance;
      return ClosedAccountState.Instance;

The code continues to work and now changing the Active flag results in different behavior.

Best of all we still have the code in separate classes, no switch/case/if statements relating to validation or account types, a clean inheritance hierarchy and no running around trying to invalidate existing references.

Hitting the discriminator directly

There may be times when claims are made that a type has to change – perhaps data was entered incorrectly.

Before delving into the database or providing a tool to flip the underlying discriminator value consider:

  1. Does the new class interpret the same data in a different manner? Has a $1,000 credit limit just become a 1,000 transactions per-month limit?
  2. Would the object be valid in the new class? Did a ProposedCustomer just become ApprovedCustomer without a policy-enforced credit check?
  3. Are associations still honored? Are 300 unshipped orders for a GameProduct still honored for a BookProduct?

If in doubt don’t do it.

An inconsistent database bleeding through your application isn’t good for anyone and will take a lot longer to sort out than setting up a new entity.


LINQ to SQL next steps

There has been a flurry of posts and comments in the last 24 hours over the future of LINQ to SQL so I thought it would be interesting to provide some information on what the LINQ to SQL team have been up to and what we’re working on for .NET Framework 4.0.

A little background

LINQ was a new feature in .NET 3.5 that provides a store-agnostic query language syntax using a provider model.

As part of that initiative the C# team delivered LINQ to SQL – a LINQ provider to SQL Server with additional update and access management via DataContext and mapping tools such as the designer and SQL Metal. The result is a great lightweight solution that is easy to get started with and a good data access solution where your database and objects are closely aligned.

Meanwhile Data Programmability worked on an initiative to provide abstraction between conceptual and physical models of databases to allow applications to operate independently of the database vendor and underlying physical schema. LINQ support was also added in the form of LINQ to Entities and it shipped with it’s management tools in .NET 3.5 SP1.

Which now means we have two object-relational mapping options that overlap in some areas but with very different backgrounds.

The story so far

Some time before I started in May LINQ to SQL was handed over to Data Programmability.

One of the first things you notice is that the overlap between the two object-relational mapping solutions Microsoft causes confusion and hesitation.

Do I want something lightweight and easy or do I need the extra abstraction with a richer feature set?
If I want to start with LINQ to SQL today but know that the database won’t stay under my control how would I move to Entity Framework?

These are the questions that many developers face and our team spent a lot of time looking at the differences between the two stacks, how they behave and which features were missing if you wanted to migrate from LINQ to SQL to Entity Framework v1. We put together code samples, some helpers and documentation which are now being serialized on the ADO.NET blog and left us with a better understanding of the disparity – some of which the EF team have already addressed in v2.

Where next

The decision has been made that Entity Framework is the recommended solution for LINQ to relational scenarios but we are committed to looking after our users and are approaching this in two ways.

Firstly we are going to make sure LINQ to SQL continues to operate as it should. This doesn’t just mean making sure what we had works in .NET 4.0 but also fixing a number of issues that have arisen as people pick it up for more advanced projects and put it into production environments.

Secondly we will evolve LINQ to Entities to encompass the features and ease of use that people have come to expect from LINQ to SQL. In .NET 4.0 this already includes additional LINQ operators and better persistence-ignorance.

This isn’t to say LINQ to SQL won’t ever get new features. The communities around LINQ to SQL are a continuous source of ideas and we need to consider how they fit the minimalistic lightweight approach LINQ to SQL is already valued for. Where these suggestions fit with this strategy we will be working hard to get them into the product. Some enhancements like the T4 templates can be released independently but run-time elements need to stick to the .NET Framework schedule.

In conclusion


(in large, friendly letters)

LINQ to SQL will continue to work and EF will better address the needs of LINQ to SQL users with each new release.


LINQ to SQL template for Visual Studio 2008

A newer version of this LINQ to SQL template is available.

If you want to customize the LINQ to SQL code generation phase in your project without additional tool dependencies this could be what you’re looking for.

11 Dec 2008 Fixes to association code with one-to-one’s and with no serialization required. 7 Dec 2008 Added IsDelayLoaded, IsUnique. Fixed association code, stored proc update overrides. 17 Nov 2008 Added IsInheritanceDefault read/emit IsDefault on type 26 Oct 2008 Emit column Name attribute if it doesn’t match the member (thanks Steele) 20 Oct 2008 Handle class naming better, fix associations for renamed or out-of-sequence keys and their access modifiers 25 Sep 2008 Figure out missing association keys by using either sides primary key 25 Sep 2008 Empty DBML name-space is now no namespace instead of “MyApplication” 23 Sep 2008 fixed stored procedures with 0 parameters 22 Sep 2008 fixed VB.NET IsForeignKey attribute for associations 18 Sep 2008 now generates stored procedures including insert/update/delete with concurrency checking.

New since ‘reloaded’ version

  • Inheritance – generates sub-classes with all properties and code mappings.
  • is joined by a VB.NET emitting
  • DataContract SP1 – additional mode to emit SP1-compatible DataContract serialization via Roger Jennings.
  • Composite keys – both as the primary key and as a foreign key in an association.
  • Type attributes – the data context and entity types can now be sealed or abstract as well as public, private, protected, internal or protected internal.
  • Associations – prevents foreign key values changing once the object association is made and updates parent side of one-to-many associations.
  • Stored procedures – generates method wrappers and associated methods to facilitate insert/update/delete with concurrency support.

Functionality compared to designer

A primary goal in developing the template was to allow for easy switching between the template and the LINQ to SQL designer so things are very similar.


  • Comprehensive sanity checking on the DBML.
  • The Custom Tool Namespace and project namespaces are not pulled in when the DBML namespaces not specified.


The designer has a few bugs which helpfully this template doesn’t suffer from.

  • Modifying a table via a stored procedure using original values for concurrency will throw ChangeConflictException and not silently fail.
  • Protected internal virtual property doesn’t forget to be virtual.
  • Checks all associations based on a foreign key are not loaded before allowing change and not just the first one.


  • Fully customizable with full source.
  • Serialization mode to support DataContract improvements in .NET 3.5 SP1 To use uncomment the line // data.Serialization = SerializationMode.DataContractSP1; in
  • CanBeNull attribute generated for value types (useful when working with metadata).

Source compared to designer

The designer generated code can be difficult to read and isn’t well suited to template generation so the output from this template is different in a number of ways:


Everything related to a column mapping – the storage variable, event signatures, attribute and the property itself – is batched together so it can be hit with a single loop making the template shorter and easier to work with.


Opinion may be divided on the usefulness of #regions in your own code but for code generation of large files I found it invaluable. There are regions for the logical parts of the data context and within each entity such as construction, column mapping, associations and serialization.


The code generated should be a little easier to follow – if/else ordering, no redundant casts or extra brackets etc.


I don’t believe adding “this” everywhere or fully qualifying attributes and exceptions makes things easy to read. I realize this might cause some name conflicts for some people but it is easy to change yourself and means the code is shorter and easier to work with for the majority.

Getting started

Although I work on the LINQ to SQL team this template should be treated as a third-party sample and is not supported by Microsoft.

Download via CodePlex

  1. Add the L2ST4.ttinclude and either or to your project depending on your language type
  2. Rename the to match your DBML file but with .tt extension instead of .dbml
  3. Set the existing DBML file’s .designer.cs/vb Build Action property to None to ignore the LINQ to SQL built-in code generation

Note that the template will only regenerate when it has been changed so use Run Custom Tool from the template’s right-mouse button menu in Solution Explorer when you’ve changed the DBML.

Should you wish to switch back to using the designer code then set the DBML file’s .designer.cs/vb Build Action to Compile and either remove the .tt and .ttinclude file for permanent removal or just set the .generated.cs/vb Build Action to None to keep it around. VB.NET users will need to use Show All Files to see the .designer.vb file.


The template run-time built into Visual Studio 2008 is called T4 and requires no additional tools however if you do a lot of editing you might want to install the Clarius T4 Editor for syntax highlighting and also check out the treasure trove of T4 material that is Oleg Sych’s blog.

The template is simple to follow, it loads the DBML file as an XML document then uses LINQ to XML to instantiate wrapper objects over the elements. This gives you a simple way to change default naming and behavior while making the template simpler to work with.

Let me know how you get on by leaving a comment here.


LINQ to SQL log to debug window, file, memory or multiple writers

The Log property on a LINQ to SQL data context takes a TextWriter and streams out details of the SQL statements and parameters that are being generated and sent to the server.

Normally in examples you will see Console.Out being assigned to it which is fine for small demos and sandboxes but sooner or later you’ll want access to it in Windows or web applications. Here are some examples of how to redirect TextWriter output such as the DataContext log to other destinations.

Github has the latest version of ActionTextWriter etc.

To the output/debug window

The output/debug window mechanism actually uses a listener mechanism and so doesn’t actually directly expose a TextWriter interface however we can simply wrap up Debug.Write in something that does and use that instead:

class DebugTextWriter : System.IO.TextWriter {
   public override void Write(char[] buffer, int index, int count) {
       System.Diagnostics.Debug.Write(new String(buffer, index, count));

   public override void Write(string value) {

   public override Encoding Encoding {
       get { return System.Text.Encoding.Default; }

To use it then simply:

myDataContext.Log = new DebugTextWriter();

To a file

 db.Log = new System.IO.StreamWriter("linq-to-sql.log") { AutoFlush = true };

If you wish to not overwrite the existing log file then change the constructor to include the parameter true after the filename. Bear in mind this log file can get very large and slow down your application with all that extra writing to disk and could well reveal information you’d rather wasn’t persisted there so the DEBUG conditional is recommended.

To memory

 var sw = new System.IO.StringWriter();
 db.Log = sw;

You will be able to examine sw or call ToString() on it to see the contents. Again this is not recommended for production as it will cause a lot of memory consumption as the StringWriter gets larger and larger.

To multiple writers

Here is a small useful class that lets you send the results intended for a TextWriter off into multiple writers.

class MulticastTextWriter : TextWriter {
  private readonly IList<TextWriter> textWriters;

  public MulticastTextWriter() : this(new List<TextWriter>()) {

  public MulticastTextWriter(IList<TextWriter> textWriters) {
    this.textWriters = textWriters;

  public void Add(TextWriter textWriter) {
    lock (textWriters)

  public bool Remove(TextWriter textWriter) {
    lock (textWriters)
      return textWriters.Remove(textWriter);

  public override void Write(char[] buffer, int index, int count) {
    lock (textWriters)
      foreach (TextWriter textWriter in textWriters)
        textWriter.Write(buffer, index, count);

  public override Encoding Encoding {
    get { return System.Text.Encoding.Default; }

So if you wanted to output to a log and also to the debug window, you would use it like this (again recommended only for debugging):

var mw = new MulticastTextWriter();
mw.Add(new DebugTextWriter());
mw.Add(new System.IO.StreamWriter("linq-to-sql.log") { AutoFlush = true });
db.Log = mw;

Anything you want

To wrap things up here is a small TextWriter that lets you go off and do whatever you like with the string via the Action delegate.

class ActionTextWriter : TextWriter {
  private readonly Action<string> action;

  public ActionTextWriter(Action<string> action) {
    this.action = action;

  public override void Write(char[] buffer, int index, int count) {
    Write(new string(buffer, index, count));

  public override void Write(string value) {

  public override Encoding Encoding {
    get { return System.Text.Encoding.Default; }

So if you wanted to output all log information to say a WinForms message box a tiny lambda expression gets you there:

db.Log = new ActionTextWriter(s => MessageBox.Show(s));

Have fun!