Posts tagged with c - page 5

Client-side properties and any remote LINQ provider

David Fowler on the ASP.NET team and I have been bouncing ideas about how to solve an annoyance using LINQ:

If you write properties on the client, you can’t use them in remote LINQ operations.

The problem occurs because these properties can’t be translated and sent to the server. They have been compiled into Intermediate Language (IL) and not expression trees. LINQ requires expression trees to translate them using IQueryable providers. There is nothing available in .NET to let us reverse-engineer the IL back into the methods and syntax that would allow us to translate the intended operation into a remote query.

This means you end up having to write your query in two parts. Firstly, the part the server can perform. Then a ToList or AsEnumerable call to force that to happen and bring the intermediate results down to the client. Then the second part with operations that must be evaluated locally. This process can hurt performance if you want to reduce or transform the result set significantly.

What David, Colin Meek, and I came up with is a provider-independent way of declaring properties just once so they are usable in both scenarios. Computed properties for LINQ to SQL, LINQ to Entities and anything else LINQ enabled with little effort, and it works on .NET 3.5 SP1 :)

Before example

Here we have extended the Employee class to add Age and FullName. We only wanted people with “da” in their name but are forced to pull everything to the client to perform the selection.

partial class Employee {
  public string FullName {
    get { return Forename + " " + Surname; }
  }

  public int Age {
    get { return DateTime.Now.Year - BirthDate.Year - (((DateTime.Now.Month < BirthDate.Month)
			|| DateTime.Now.Month == BirthDate.Month && DateTime.Now.Day < BirthDate.Day) ? 1 : 0));
    }
  }
}

var employees = db.Employees.ToList().Where(e => e.FullName.Contains("da")).GroupBy(e => e.Age);

After example

Here, using our approach, it all happens server-side and works on both LINQ to Entities and LINQ to SQL.

partial class Employee {
    private static readonly CompiledExpression<Employee,string> fullNameExpression
     = DefaultTranslationOf<Employee>.Property(e => e.FullName).Is(e => e.Forename + " " + e.Surname);
    private static readonly CompiledExpression<Employee,int> ageExpression
     = DefaultTranslationOf<Employee>.Property(e => e.Age).Is(e => DateTime.Now.Year - e.BirthDate.Value.Year - (((DateTime.Now.Month < e.BirthDate.Value.Month) || (DateTime.Now.Month == e.BirthDate.Value.Month && DateTime.Now.Day < e.BirthDate.Value.Day)) ? 1 : 0)));

  public string FullName {
    get { return fullNameExpression.Evaluate(this); }
  }

  public int Age {
    get { return ageExpression.Evaluate(this); }
  }
}

var employees = db.Employees.Where(e => e.FullName.Contains("da")).GroupBy(e => e.Age).WithTranslations();

Getting started

Usage considerations

The caveats to the technique shown above are:

  • You need to ensure your class is initialized before you write queries with it (see alternatives below).
  • The expression you register for a property must be translatable to the remote store. You need to constrain yourself to the methods and operators your IQueryable provider supports.

There are a few alternative ways to use this rather than the specific examples above.

Registering the expressions

You can register the properties in the class itself, as shown in the examples. This means the properties themselves can evaluate the expressions without any reflection calls. Alternatively, if performance is less critical, you can register them elsewhere and have the methods look up their values dynamically via reflection. e.g.

DefaultTranslationOf<Employee>.Property(e => e.FullName).Is(e => e.Forename + " " + e.Surname);
var employees = db.Employees.Where(e => e.FullName.Contains("da")).GroupBy(e => e.Age).WithTranslations();

partial class Employee {
    public string FullName { get { return DefaultTranslationOf<Employees>.Evaluate<string>(this, MethodInfo.GetCurrentMethod());} }
}

When the performance of these client-side properties is critical, you can have them as regular get properties with the full code in there at the expense of having the calculation duplicated, once in IL in the property and once as an expression for the translation.

Different maps for different scenarios

Sometimes certain parts of your application may want to run with different translations for different scenarios, performance etc. No problem!

The WithTranslations method normally operates against the default translation map (accessed with DefaultTranslationOf), but there is also another overload that takes a TranslationMap you can build for specific scenarios, e.g.

var myTranslationMap = new TranslationMap();
myTranslationMap.Add<Employees, string>(e => e.Name, e => e.FirstName + " " + e.LastName);
var results = (from e in db.Employees where e.Name.Contains("martin") select e).WithTranslations(myTranslationMap).ToList();

How it works

CompiledExpression<T, TResult>

The first thing we needed to do was get the user-written client-side “computed” properties out of IL and back into expression trees so we could translate them. Given we also want to evaluate them on the client, we need to compile them at run time, so CompiledExpression exists. It takes an expression of Func<T, TResult>, compiles it, and allows evaluation of objects against the compiled version.

ExpressiveExtensions

This little class provides both the WithTranslations extensions methods and the internal TranslatingVisitor that unravels the property accesses into their actual, registered Func<T, TResult> expressions via the TranslationMap so that the underlying LINQ provider can deal with that instead.

TranslationMap

We need to have a map of properties to compiled expressions, and for that purpose, TranslationMap exists. You can create a TranslationMap by hand and pass it to WithTranslations if you want to programmatically create them at run-time or have different ones for different scenarios, but generally, you will want to use DefaultTranslationOf.

DefaultTranslationOf

This helper class lets you register properties against the default TranslationMap we use when nothing is passed to WithTranslations. It also allows you to look-up what is already registered so you can evaluate that although there is a small reflection performance penalty:

public int Age { get { return DefaultTranslationOf<Employees>.Evaluate<int>(this, MethodInfo.GetCurrentMethod()); } }

Have fun!

[)amien

LINQ to SQL tips and tricks #2

A few more useful and lesser-known tips 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 of what makes an optimal query.

In either case, the Translate method allows you to deliver your own TSQL to LINQ to SQL, to execute, with 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 to figure out what the return type is. To do this without actually running the stored procedure, they use the SET FMTONLY command so that SQL Server parses the stored procedure but does not execute it.

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 of the subsequent warning.

SET FMTONLY OFF

If your stored procedure can not safely handle calls 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 subclassing, you 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:

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 this is a problem for you, why not grab the 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 not 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 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 support it. It is necessary to hand-edit the DBML and then use SQL Metal (or my T4 template) to produce the required method signature.

It is 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 when it is useful to listen to events and perform your own logic, perhaps auditing or logging for some scenarios. The easiest way is to implement some specially-named methods on your data context, perform your action, then dispatch the call back to LINQ to SQL.

The format of these specially-named methods is [Action][Entity], and 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