Posts tagged with

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)


LINQ to SQL template for Visual Studio 2008

A newer version of this LINQ to SQL template is available.

If you want to customize the LINQ to SQL code generation phase in your project without additional tool dependencies this could be what you’re looking for.

11 Dec 2008 Fixes to association code with one-to-one’s and with no serialization required. 7 Dec 2008 Added IsDelayLoaded, IsUnique. Fixed association code, stored proc update overrides. 17 Nov 2008 Added IsInheritanceDefault read/emit IsDefault on type 26 Oct 2008 Emit column Name attribute if it doesn’t match the member (thanks Steele) 20 Oct 2008 Handle class naming better, fix associations for renamed or out-of-sequence keys and their access modifiers 25 Sep 2008 Figure out missing association keys by using either sides primary key 25 Sep 2008 Empty DBML name-space is now no namespace instead of “MyApplication” 23 Sep 2008 fixed stored procedures with 0 parameters 22 Sep 2008 fixed VB.NET IsForeignKey attribute for associations 18 Sep 2008 now generates stored procedures including insert/update/delete with concurrency checking.

New since ‘reloaded’ version

  • Inheritance – generates sub-classes with all properties and code mappings.
  • is joined by a VB.NET emitting
  • DataContract SP1 – additional mode to emit SP1-compatible DataContract serialization via Roger Jennings.
  • Composite keys – both as the primary key and as a foreign key in an association.
  • Type attributes – the data context and entity types can now be sealed or abstract as well as public, private, protected, internal or protected internal.
  • Associations – prevents foreign key values changing once the object association is made and updates parent side of one-to-many associations.
  • Stored procedures – generates method wrappers and associated methods to facilitate insert/update/delete with concurrency support.

Functionality compared to designer

A primary goal in developing the template was to allow for easy switching between the template and the LINQ to SQL designer so things are very similar.


  • Comprehensive sanity checking on the DBML.
  • The Custom Tool Namespace and project namespaces are not pulled in when the DBML namespaces not specified.


The designer has a few bugs which helpfully this template doesn’t suffer from.

  • Modifying a table via a stored procedure using original values for concurrency will throw ChangeConflictException and not silently fail.
  • Protected internal virtual property doesn’t forget to be virtual.
  • Checks all associations based on a foreign key are not loaded before allowing change and not just the first one.


  • Fully customizable with full source.
  • Serialization mode to support DataContract improvements in .NET 3.5 SP1 To use uncomment the line // data.Serialization = SerializationMode.DataContractSP1; in
  • CanBeNull attribute generated for value types (useful when working with metadata).

Source compared to designer

The designer generated code can be difficult to read and isn’t well suited to template generation so the output from this template is different in a number of ways:


Everything related to a column mapping – the storage variable, event signatures, attribute and the property itself – is batched together so it can be hit with a single loop making the template shorter and easier to work with.


Opinion may be divided on the usefulness of #regions in your own code but for code generation of large files I found it invaluable. There are regions for the logical parts of the data context and within each entity such as construction, column mapping, associations and serialization.


The code generated should be a little easier to follow – if/else ordering, no redundant casts or extra brackets etc.


I don’t believe adding “this” everywhere or fully qualifying attributes and exceptions makes things easy to read. I realize this might cause some name conflicts for some people but it is easy to change yourself and means the code is shorter and easier to work with for the majority.

Getting started

Although I work on the LINQ to SQL team this template should be treated as a third-party sample and is not supported by Microsoft.

Download via CodePlex

  1. Add the L2ST4.ttinclude and either or to your project depending on your language type
  2. Rename the to match your DBML file but with .tt extension instead of .dbml
  3. Set the existing DBML file’s .designer.cs/vb Build Action property to None to ignore the LINQ to SQL built-in code generation

Note that the template will only regenerate when it has been changed so use Run Custom Tool from the template’s right-mouse button menu in Solution Explorer when you’ve changed the DBML.

Should you wish to switch back to using the designer code then set the DBML file’s .designer.cs/vb Build Action to Compile and either remove the .tt and .ttinclude file for permanent removal or just set the .generated.cs/vb Build Action to None to keep it around. VB.NET users will need to use Show All Files to see the .designer.vb file.


The template run-time built into Visual Studio 2008 is called T4 and requires no additional tools however if you do a lot of editing you might want to install the Clarius T4 Editor for syntax highlighting and also check out the treasure trove of T4 material that is Oleg Sych’s blog.

The template is simple to follow, it loads the DBML file as an XML document then uses LINQ to XML to instantiate wrapper objects over the elements. This gives you a simple way to change default naming and behavior while making the template simpler to work with.

Let me know how you get on by leaving a comment here.


Object Initializers in .NET 3.5

One compiler improvement in .NET 3.5 is the object initializers feature that lets you concisely set properties of an object as you create it.

If you’ve ever used VB.NET you may well have found and enjoyed the with keyword to write code such as:

Dim myObj As MyClass
myObj = New MyClass()
With myObj
  .ProductCode = "ABC123"
  .Quantity = 5
  .Cost = 567.89
End With

This is more concise than writing myObj several times over, especially if setting a large number of properties, but as C# has no such keyword many people resorted to providing helpful constructors to facilitate code like:

MyClass myObj = new MyClass("ABC123", 5, 567.89);

If all three of these properties are essential then this makes for a sensible constructor however many classes have a number of properties that are optional and class designers struggle to determine whether to make constructors that merely cut-down on typing and which of the various combinations of optional properties might make sense in having their own constructor.

Invariably the combination you might want doesn’t exist and if it does the chances of being able to understand which properties are being set from one of a number of constructors that take parameters of similar types is quite low unless you go and take a peek with the IntelliSense.

Using object initializers you can stick to creating constructors that reflect parameters necessary to ensure your object is in a valid state and forget about providing helpful ones for those optional parameters. In our example if we assume the ProductCode is essential and the others are optional we can write code like:

MyClass myObj = new MyClass("ABC123") { Quantity = 5, Cost = 567.89 };

Which is both concise and easy to understand. It also requires no work on the part of the class designer and therefore works with all your existing classes. You can also nest them to set properties that require more complex types such as:

MyClass myObj = new MyClass("ABC123") {
   Quantity = 5,
   Cost = 567.89,
   Category = new Category("A") { Description = "New machine" }

This feature is no use if your objects are immutable in which case constructors are your only friend.


Visual Studio 2003 – System.ArgumentException in debugger

I recently ran into a problem while debugging inside Visual Studio 2003.Net. Google couldn’t find me an answer, only a few other people with the same problem. Here’s my solution in the hope it might save somebody else some time.


Whenever debugging a specific VB.NET application that used a C# class library I would receive the following error certain objects in the C# class library:

<error: an exception of type: {System.ArgumentException} occurred>

Strangely only the ASP.NET application was affected, the WinForms application that also used this class library was unaffected. It also seemed to be localized to the debugger only, run-time behavior appeared to be just fine.

A reboot, rebuild project or a clear down of the ASP temp directory had no effect and another developer on the project had exactly the same problem despite the fact we do not share any binaries…


Close VS and delete all bin and obj directories from all projects in your solution if you want to be totally sure.

If you really don’t want to do this, deleting the .pdb’s from the project where the messed up object lives may be enough.

You may think forcing a rebuild with “rebuild solution” would achieve this, but it does not.

Cause (optional)

While debugging I noticed that I couldn’t create the messed up object interactively either. It was complaining that no constructor took 4 arguments and that it took 3. Well, it used to take 3 – over a week ago.

This leads me to believe that some combination of changing your C# source can make either VS or the C# compiler believe the debugging symbols are still up to date when they are not. This may possibly only affect VB.NET applications compiled against it.

It must be a reproducible bug because it happened on two different machines.