Posts in category .net - page 7

LINQ to SQL tips and tricks #1

Being on the inside of a product team often leads to uncovering or stumbling upon lesser known techniques and here are a few little nuggets I found interesting – I have more if there is interest.

Loading a delay-loaded property

LINQ to SQL lets you specify that a property is delay-loaded meaning that it is not normally retrieved as part of normal query operations against that entity. This is particularly useful for binary and large text fields such as a photo property on an employee object that is rarely used and would cause a large amount of memory to be consumed on the client not to mention traffic between the SQL and application.

There are times however when you want all these binaries returned in a single query, say for example returning all the photos for the company photo intranet page:

var db = new NorthwindContext();
var loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Employee>(e => e.Photo);
db.LoadOptions = loadOptions;

Multiple entity types from a single stored procedure

It is actually possible to return multiple entity types from a single stored procedure in LINQ to SQL although this is not well known as the LINQ to SQL designer doesn’t actually support it. Indeed to generate the code it is necessary to hand-edit the DBML and then use SQL Metal (or my T4 template) to generate the required method signature.

In fact it is just much easier to write the code yourself and add it to the non-generated portion of your data context. If you imagine a stored procedure GetStaticData that looks like this:

CREATE PROCEDURE GetStaticData AS
  SELECT * FROM Region
  SELECT * FROM Categories
  SELECT * FROM Territories

Then all you need to do is write a method signature that looks like this (the sequence of result type attributes must match the order in the stored procedure):

[Function(Name=@"dbo.DynamicContractsActiveBetween")]
[ResultType(typeof(Region))]
[ResultType(typeof(Category))]
[ResultType(typeof(Territory))]
public IMultipleResults GetStaticData() {
   return (IMultipleResults) ExecuteMethodCall(this, (MethodInfo) MethodInfo.GetCurrentMethod()).ReturnValue;
}

Intercepting create, update and delete operations

There are times it is useful to be able to listen in to when these events happen and perform your own logic, perhaps auditing or logging for some scenarios. The easiest way to do this is to implement some specially-named methods on your data context, perform your action and then to dispatch the call back to LINQ to SQL.

The format of these specially-named methods is [Action][Entity] and then you should pass back control to LINQ to SQL using ExecuteDynamic[Action] where [Action] is either Insert, Update or Delete. One example of such usage might be:

partial class NorthwindContext {
   partial void InsertEmployee(Employee instance) {
      instance.CreatedBy = CurrentUser;
      instance.CreatedAt = DateTime.Now;
      ExecuteDynamicInsert(instance);
   }

   partial void UpdateEmployee(Employee instance) {
      AuditEmployeeOwnerChange(instance);
      instance.LastModifiedAt = DateTime.Now;
      ExecuteDynamicUpdate(instance);
   }

   partial void DeleteEmployee(Employee instance) {
      AuditDelete(instance, CurrentUser);
      ExecuteDynamicDelete(instance);
   }
}

Check out part 2 of LINQ to SQL tips

[)amien

Multiple outputs from T4 made easy

An improved version is now available.

One of the things I wanted my LINQ to SQL T4 templates to do was be able to split the output into a file-per-entity. Existing solutions used either a separate set of templates with duplicate code or intrusive handling code throughout the template. Here’s my helper class to abstract the problem away from what is already complicated enough template code.

Using the Manager class

Setup

You’ll need to get the code into your template – either copy the code in or reference it with an include directive. Then declare an instance of the Manager class passing in some environmental options such as the desired default output path.

<#@ template language="C#v3.5" hostspecific="True"
#><#@ include file="Manager.ttinclude"
#><# var manager = new Manager(Host, GenerationEnvironment, true) { OutputPath = Path.GetDirectoryName(Host.TemplateFile) }; #>

Define a block

Then add one line before and one line after each block which could be split out into it’s own file passing in what the filename would be if split.

<# manager.StartBlock("Employee.generated.cs"); #>
public class Employee {  }
<# manager.EndBlock(); #>

Headers and footers

Many templates need to share a common header/footer for such things as comments or using/import statements or turning on/off warnings. Simply use StartHeader/EndHeader and StartFooter/EndFooter. The resulting blocks will be emitted into all split files and left in the original output too.

<# manager.StartHeader(); #>
// Code generated template
using System;

<# manager.EndHeader(); #>

Process

At the end of the template call Process to handle splitting the files (true) or not (false). Anything not included in a specific start/end block will remain in the original output file.

<# manager.Process(true); #>

When processing each block name in the Output path will either be overwritten or deleted to enable proper clean-up. It will also add and remove the files from Visual Studio so make sure your generated names aren’t going to collide with hand-written ones!

Manager classes

Here is the Manger class itself as well as the small ManagementStrategy classes that determines what to do with the files within Visual Studio (add/remove project items) and outside of Visual Studio (create/delete files).

Download Manager.ttinclude (4KB)

<#@ assembly name="System.Core"
#><#@ assembly name="EnvDTE"
#><#@ import namespace="System.Collections.Generic"
#><#@ import namespace="System.IO"
#><#@ import namespace="System.Text"
#><#@ import namespace="Microsoft.VisualStudio.TextTemplating"
#><#+

// T4 Template Block manager for handling multiple file outputs more easily.
// Copyright (c) Microsoft Corporation.  All rights reserved.
// This source code is made available under the terms of the Microsoft Public License (MS-PL)

// Manager class records the various blocks so it can split them up
class Manager
{
	private struct Block {
		public String Name;
		public int Start, Length;
	}

	private List<Block> blocks = new List<Block>();
	private Block currentBlock;
	private Block footerBlock = new Block();
	private Block headerBlock = new Block();
	private ITextTemplatingEngineHost host;
	private ManagementStrategy strategy;
	private StringBuilder template;
	public String OutputPath { get; set; }

	public Manager(ITextTemplatingEngineHost host, StringBuilder template, bool commonHeader) {
		this.host = host;
		this.template = template;
		OutputPath = String.Empty;
		strategy = ManagementStrategy.Create(host);
	}

	public void StartBlock(String name) {
		currentBlock = new Block { Name = name, Start = template.Length };
	}

	public void StartFooter() {
		footerBlock.Start = template.Length;
	}

	public void EndFooter() {
		footerBlock.Length = template.Length - footerBlock.Start;
	}

	public void StartHeader() {
		headerBlock.Start = template.Length;
	}

	public void EndHeader() {
		headerBlock.Length = template.Length - headerBlock.Start;
	}

	public void EndBlock() {
		currentBlock.Length = template.Length - currentBlock.Start;
		blocks.Add(currentBlock);
	}

	public void Process(bool split) {
		String header = template.ToString(headerBlock.Start, headerBlock.Length);
		String footer = template.ToString(footerBlock.Start, footerBlock.Length);
		blocks.Reverse();
		foreach(Block block in blocks) {
			String fileName = Path.Combine(OutputPath, block.Name);
			if (split) {
				String content = header + template.ToString(block.Start, block.Length) + footer;
				strategy.CreateFile(fileName, content);
				template.Remove(block.Start, block.Length);
			} else {
				strategy.DeleteFile(fileName);
			}
		}
	}
}

class ManagementStrategy
{
	internal static ManagementStrategy Create(ITextTemplatingEngineHost host) {
		return (host is IServiceProvider) ? new VSManagementStrategy(host) : new ManagementStrategy(host);
	}

	internal ManagementStrategy(ITextTemplatingEngineHost host) { }

	internal virtual void CreateFile(String fileName, String content) {
		File.WriteAllText(fileName, content);
	}

	internal virtual void DeleteFile(String fileName) {
		if (File.Exists(fileName))
			File.Delete(fileName);
	}
}

class VSManagementStrategy : ManagementStrategy
{
	private EnvDTE.ProjectItem templateProjectItem;

	internal VSManagementStrategy(ITextTemplatingEngineHost host) : base(host) {
		IServiceProvider hostServiceProvider = (IServiceProvider)host;
		if (hostServiceProvider == null)
			throw new ArgumentNullException("Could not obtain hostServiceProvider");

		EnvDTE.DTE dte = (EnvDTE.DTE)hostServiceProvider.GetService(typeof(EnvDTE.DTE));
		if (dte == null)
			throw new ArgumentNullException("Could not obtain DTE from host");

		templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile);
	}

	internal override void CreateFile(String fileName, String content) {
		base.CreateFile(fileName, content);
		((EventHandler)delegate { templateProjectItem.ProjectItems.AddFromFile(fileName); }).BeginInvoke(null, null, null, null);
	}

	internal override void DeleteFile(String fileName) {
		((EventHandler)delegate { FindAndDeleteFile(fileName); }).BeginInvoke(null, null, null, null);
	}

	private void FindAndDeleteFile(String fileName) {
		foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems) {
			if (projectItem.get_FileNames(0) == fileName) {
				projectItem.Delete();
				return;
			}
		}
	}
}#>

[)amien

LINQ to SQL templates updated, now on CodePlex

My templates that allow you to customize the LINQ to SQL code-generation process (normally performed by SQLMetal/LINQ to SQL classes designer) have been updated once again.

Updates

  • Now licensed under the Microsoft Public License and hosted at CodePlex
  • User options specified with a var options block at the start of the template
  • Option for each class to be a separate file that is reflected in the VS project EntityPerFile=true
  • Detection and support of IsComposable functions
  • General code clean-up and better error handling such as missing DBML file

CodePlex

CodePlex makes it easier for people to be able to see and merge updates in with their own modified versions as well as report issues via the issue tracker etc. There is also an RSS feed that lets you keep track of releases, source updates or whatever else you are interested in.

For now it is a grab-the-source style release but I hope to publish downloadable tested releases wrapped up in a Visual Studio Installer (VSI) package to make getting started easier soon.  Feel free to grab the sources directly via TFS/Subversion to be able to diff them with your own modified versions.

Enjoy!

[)amien

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)
      result.Add(TransactionFailures.InsufficientFunds);
    return result;
  }
}

public class SavingsAccount : Account {
  public override Status Validate(ITransaction tx) {
    Status result = base.Validate(tx);
    if (Transactions.Count > TransactionLimit)
      result.Add(TransactionFailures.TransactionLimitReached);
    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;
    else
      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.

[)amien