Posts in category .net - page 7

LINQ to SQL tips and tricks #2

A few more useful and lesser-known techniques for using LINQ to SQL.

Take full control of the TSQL

There are times when LINQ to SQL refuses to cook up the TSQL you wanted either because it doesn’t support the feature or because it has a different idea what makes an optimal query.

In either case the Translate method allows you to deliver your own TSQL to LINQ to SQL to process as if it were its own with execution, materialization and identity mapping still honored. For example:

var db = new PeopleContext();
if (db.Connection.State == System.Data.ConnectionState.Closed)
    db.Connection.Open();
var cmd = db.GetCommand(db.Persons.Where(p => p.CountryID == 1));
cmd.CommandText = cmd.CommandText.Replace("[People] AS [t0]", "[People] AS [t0] WITH (NOLOCK)");
var results = db.Translate<Person>(cmd.ExecuteReader());

Complex stored procedures

When working with stored procedures the LINQ to SQL designer and SQLMetal tools need a way of figuring out what the return type will be. In order to do this without actually running the stored procedure itself they use the SET FMTONLY command set to ON so that SQL Server will just parse the stored procedure instead.

Unfortunately this parsing does not extend to dynamic SQL or temporary tables so you must change the return type from the scalar integer to one of the known entity types by hand. You could use the following command at the start to let it run regardless given the subsequent warning.

SET FMTONLY OFF

If your stored procedure can not safely handle being called at any time with null parameters set the return type by hand instead.

Cloning an entity

There are many reasons you might want to clone an entity – you may want to create many similar ones, you could want to keep it around longer than the DataContext it came from – whatever your reason implementing a Clone method can be a pain but taking advantage of the DataContractSerializer can make light work of this providing your DBML is set to enable serialization.

If you use discriminator sub-classing you will need to either ensure your type is cast to its concrete type or use my L2ST4 templates for now as .NET 3.5 SP1 doesn’t emit the necessary KnownType attributes to make this automatically happen (fixed in .NET 4.0). Add a simple method to serialize in-memory like this:

public static T Clone<T>(T source) {
    var dcs = new System.Runtime.Serialization.DataContractSerializer(typeof(T));
    using (var ms = new System.IO.MemoryStream()) {
        dcs.WriteObject(ms, source);
        ms.Seek(0, System.IO.SeekOrigin.Begin);
        return (T)dcs.ReadObject(ms);
    }
}

And then to clone simply:

var source = myQuery.First();
var cloned = Clone(source);

Be aware that this comes with a little overhead in the serialization and de-serialization process.

If that is a problem for you then why not grab those templates and make your entities implement ICloneable!

Check out part 3 of LINQ to SQL tips

[)amien

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