Posts in category .net - page 6

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)

[)amien

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;
}

[)amien

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 resources

A quick round-up of some useful LINQ to SQL related resources that are available for developers. I’ve not used everything on this list myself so don’t take this as personal endorsement.

Templates

  • Entity Developer Add-in for Visual Studio that provides a replacement designer with code templates. Commercial ($99.95)
  • LLBLGen Pro This ORM templating and design tool has a set of LINQ to SQL templates available for generating C# code including file per entity that can be used in conjunction with their designer. Templates are free but require LLBLGen Pro license (€179-249).
  • L2ST4 My templates use Visual Studio’s built in T4 engine and provide a great starting point to customizing the code generation process. Everything that SQL Metal/LINQ to SQL designer can generate is handled including C# and VB.NET generation and are freely licensed under the MS-PL.
  • PLINQO These templates for Eric Smith’s ever-popular CodeSmith templating environment include a whole host of extra functionality beyond the standard generation including entity clone & detach, enum’s from tables, data annotations, batching, auditing and more. Templates are free but require CodeSmith license ($79-$299).
  • T4 Toolbox Oleg Sych has put together a suite of useful T4 templates including ones for producing LINQ to SQL entities, data contexts as well as SQL scripts for altering the schema to reflect changes. C# only, MS-RL.

Note: While the T4 templating language is built-in to Visual Studio 2008/2010 it does not come with syntax highlighting or IntelliSense. Check out either:

  • Clarius Visual T4 Basic ‘community’ version available for free, commercial ‘pro’ version with IntelliSense, sub-templates, preview, user preferences etc. is $99.
  • Tangible T4 Free version available with limited IntelliSense, commercial ‘pro’ version with UML modeling etc. available for $99.

Blogs

  • David DeWinter David is a dev in test who recently joined our team and hit the ground running with testing, blogging and helping out on the forums.
  • Roger Jennings Roger over at OakLeaf Systems publishes regular articles and roundups of some of the best .NET data access content from around the web including LINQ to SQL.
  • Scott Guthrie Our Corporate Vice President for the .NET Developer Platform takes a very active role in getting his hands dirty and publishes a series of useful LINQ to SQL articles.
  • Sidar Ok Sidar is a regular forum helper and has written a number of great posts on LINQ to SQL including some good POCO coverage.

Tools

  • Devart dotConnect Devart’s dotConnect family are database providers for Oracle, MySQL, Postgres and SQLite that also include LINQ support to enable LINQ to SQL like functionality on other platforms. Some database basic versions are free, professional versions are commercial and vary in price (~$99-$209).
  • Hugati DBML/EDMX Tools Add-in for Visual Studio that provides comparison/update facilities between the database and the DBML as well as standardizing names and generating interfaces. Commercial ($49-$119, free 30 day trial).
  • Hugati LINQ to SQL Profiler Profiling tool to help optimize your LINQ to SQL based applications. Commercial ($49-$119, free 45 day trial).
  • LINQpad This invaluable tool helps you write and visualize your LINQ queries in a test-bench without compilation and includes a version for the .NET 4.0 beta. Free to use, auto-completion add-on available ($19).
  • LINQ to SQL Cheat Sheet PDF download of the most popular query and update syntax for C# and VB.NET.

Official guides

  • Samples The official samples includes a whopping 101 snippets showing how to use many of the features and syntax.
  • Programming Guide Includes steps on how to get started, querying, making changes, debugging and background information.
  • Whitepaper Single document describing the architecture, query capabilities, change tracking and life-cycle, multi-tier entities, external mapping etc.
  • Changes in .NET 4.0 List of the changes made to LINQ to SQL to .NET 4.0 including some possible breaking changes

Books

Support

  • Official MSDN forums Great way to get access to the product team directly as well as knowledgeable and experienced users if you have a question or a problem.
  • Official LINQ to SQL FAQ Coverage is a little thin on the ground but it has some useful tips.
  • StackOverflow’s LINQ to SQL tag StackOverflow has rapidly become a leader in questions and answers for a wide variety of developer topics and covers LINQ to SQL (which the site uses for it’s data access too!)

[)amien