Posts tagged with c - page 4

LINQ to SQL cheat sheet

Thumbnail of the LINQ to SQL Cheat Sheet PDF

A few short words to say I’ve put together a cheat sheet for LINQ to SQL with one page for C# and another for VB.NET.

It shows the syntax for a number of common query operations, manipulations and attributes and can be a very useful quick reference :)

Download LINQ to SQL cheat sheet (PDF) (76 KB)


Dictionary look-up or create made simpler

The design of a Dictionary lends itself well to a caching or identification mechanism and as a result you often see code that looks like this:

private static Dictionary<string, Employee> employees = new Dictionary<string, Employee>();

public static Employee GetByName(string name) {
  Employee employee;
  if (!employees.TryGetValue(name, out employee)) {
    employee = new Employee(whatever);
    employees.Add(name, employee);
  return employee;

It’s not that it is particularly difficult but it can be a bit error prone and when you’re doing it over and over. What would be nicer is something that let you do:

public static Employee GetByName(string name) {
  return employees.GetOrAdd(name, () => new Employee(whatever));

Here’s an extension method to drop-in to a static class of your choosing that achieves just that.

public static TDictionaryValue GetOrAdd<TKey, TDictionaryValue>(
  this IDictionary<TKey, TDictionaryValue> dictionary,
  TKey key,
  Func<TDictionaryValue> newValue
) {
  TDictionaryValue value;
  if (!dictionary.TryGetValue(key, out value)) {
    value = newValue.Invoke();
    dictionary.Add(key, value);
  return value;


Client-side properties and any remote LINQ provider

David Fowler on the ASP.NET team and I have been bouncing ideas about on 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 as they have been compiled into intermediate language (IL) and not LINQ expression trees that are required for translation by IQueryable implementations. 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 do, a ToList or AsEnumerable call to force that to happen and bring the intermediate results down to the client, and then the operations that can only be evaluated locally. This can hurt performance if you want to reduce or transform the result set significantly.

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

Before example

Here we have extended the Employee class to add Age and FullName. We only wanted to people with “da” in their name but we are forced to pull down everything to the client in order to the do 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 usage technique shown above is you need to ensure your class has been initialized before you write queries to it (check out alternatives below) and obviously the expression you register for a property must be able to be translated to the remote store so you will 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 which 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());} }

If performance of the client-side properties is critical then you can always 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 that we also want to evaluate them on the client we need to compile them at run time so CompiledExpression exists which just takes an expression of Func<T, TResult>, compiles it and allows evaluation of objects against the compiled version.


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.


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 in 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…


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

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

Have fun!


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)
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.


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