Published: 23 Jun 2008
By: Dino Esposito
Download Sample Code

Dino Esposito explains how to customize the code generated by Linq-to-SQL.

Contents [hide]


LINQ-to-SQL is a table-based object model that requires a 1:1 match between entities in its object model and tables or views in the related database. The database is necessarily a SQL Server database and you can’t have multiple databases involved with the data model. If you have a view to a linked server, you can probably experience the thrill of multiple databases in LINQ-to-SQL, but this sounds like a really extreme solution. In a similar scenario, you are far better off looking around for Entity Framework or other O/RM tools.

What about the match between properties on entity classes and table columns? Even though the Visual Studio 2008 designer delivers classes that have exactly one property for each table column, this is not a strict rule or requirement. A class in the LINQ-to-SQL object model can have any number of properties, including properties that are not strictly related to the underlying table.

When comparing LINQ-to-SQL to full-blown O/RM tools, two main differences emerge. One is the database support that, as mentioned, in LINQ-to-SQL is limited to just one SQL Server database. Another is the inability to abstract properties from the collection of columns on a table. In other words, if you opt for a domain-driven approach in the design of the system, you may find it easier and more natural to group, say, the address in its own data type. This is not a feature that LINQ-to-SQL supports. Well, apparently.

In this article, I’ll go through some of the nitty-gritty details of the LINQ-to-SQL model in order to delve deeper than usual into it, make sense of some of the designer’s tricks, and add some new capabilities to the entity classes.

Life Without the LINQ-to-SQL Designer

There’s just one way of dealing with LINQ-to-SQL that many developers know. Precisely, it is adding a new project item of type “LINQ to SQL Classes”, opening the Server Explorer view, and picking up all the tables and views you want in the resulting object model. At the end of the wizard, a brand new auto-generated C# or Visual Basic .NET file makes your Visual Studio 2008 project a bit richer. What about this code?

The LINQ-to-SQL designer class contains two types of classes—a particular flavor of the system’s System.Data.Linq.DataContext type that is specific of the object model and one entity class for each table or view you added to the model through the designer.

Entity classes are plain old .NET classes and have no need to derive from a particular parent class or to implement any special interface. This is an important attribute for any class being part of any object model aimed at representing entities in a problem space. This attribute is referred to as POCO, where POCO stays for Plain Old CLR Object.

Is the Visual Studio 2008 LINQ-to-SQL class designer absolutely necessary? The designer is certainly useful and helpful; and it definitely saves you from spending a lot of time in code-monkey mode. The designer, though, adds a lot of details to the generated code that can be stripped off the source. To represent with a class a record in the Products table, you essentially need the following code:

The Table and Column attributes come from the System.Data.Linq.Mapping namespace in the assembly. Let’s compare the class Product as described above with an analogous class that the designer creates.

As you can see, it’s a much larger chunk of code. But what’s in it more than in the manually written version? The designer-generated code includes definitions for extensibility methods, notification code for data binding, more mapping attributes, and a richer constructor. All useful features; but all features you can do without.

The really key features of a LINQ-to-SQL data model class are the Table and Column attributes. The Table attribute indicates the table the class relates to; the Column attribute applied to a property maps that property to a particular column on the specified table.

Another fundamental action that the designer accomplishes is the generation of an ad hoc data context class, such as the one below:

What has this class more than the plain base DataContext class? It has a set of made-to-measure constructors and a typed property for each table or view added to the drawing surface of the designer. Here’s an example of such a property:

The Database attribute on a specialized DataContext class just indicates the database name to provide access to. The following code runs a LINQ-to-SQL query on a perfectly valid object model without going through the Visual Studio 2008 designer.

What is the whole point of the article up to here? The comment on top of any auto-generated file warns from manually editing the content of the file, but it is a kind of routine warning. If you know what you’re doing, editing the LINQ-to-SQL generated code is not dangerous at all.

The real point is all another. Why should you be modifying the LINQ-to-SQL generated code? The best answer I can find to this question is the following: to manually implement features that are nice to have and that the designer doesn’t support. Want an example? For example, consider the possibility of grouping multiple table columns into a single property of a custom type.

Personalizing a LINQ-to-SQL Class

My goal is adding to the Product class a custom property that groups together a bunch of table columns. For example, let’s say that I want to expose a custom property out of the Product class of type UnitInfo to incorporate the QuantityPerUnit and UnitPrice columns in the Products table. The type is defined as below:

You start by defining this class into a separate C# class file and add it to the project. Next, you define a new property of type UnitInfo into the Product class. Should you really edit the auto-generated source code? All in all, there’s a better way: using partial classes. All designer-generated classes are marked as partial classes, meaning that the whole source code for the class is not in one file, but it is possibly scattered in multiple files or assemblies across the project. So you start with a new file and add the following code to it:

How would you fill the properties of the UnitInfo class with values coming from table columns? The binding between database values and LINQ-to-SQL classes happens only if the Column attribute is found. So you still need QuantityPerUnit and UnitPrice mapped properties, but you don’t want to make them public. Also, because you actually manage these properties through a wrapper class—the UnitInfo class—you might want to remove them from the designer-generated class. In this way, any further edits you make through the designer don’t affect your previous changes. In the end, the partial class Product you get from the designer is like in Figure 1.

Figure 1: The class Product as you get it from the Visual Studio 2008 LINQ-to-SQL designer

The class Product as you get it from the Visual Studio 2008 LINQ-to-SQL designer

The additional partial class you write now looks as below.

Properties for QuantityPerUnit and UnitPrice are now private to the entity class and their getters and setters are basically wrappers for corresponding properties on the Unit property of type UnitInfo. One-way data binding is ensured by the Column attribute. (By the way, you don’t need the Storage attribute anymore because you are not using a private field to store the property value.)

Based on this code, you can load data into the Product class as usual with LINQ-to-SQL and query unit information through an intermediate class, as below:

What about updates? As things are now, you can effectively edit the content of members of the UnitInfo class, but these changes won’t be detected as changes and won’t be tracked by the data context. As a result, no such changes will ever apply to the database when you call SubmitChanges. You need to bring in the setters of UnitInfo properties some of the notification logic you find in the designer-generated code. Here’s what you need:

Methods SendPropertyChanging and SendPropertyChanged are defined for all properties in the designer-generated code and you can safely call them from a partial class extension. What about methods like OnQuantityPerUnitChanging and OnQuantityPerUnitChanged and similar methods for the UnitPrice property?

The designer creates such methods, for each property, as partial methods. However, the method definition is erased from the designer’s code when you delete the corresponding property from the designer. In other words, based on Figure 1 you only get partial changing/changed methods for ProductID and ProductName. Any ideas to work around it? Quite simply, just add partial methods to the class extension.

Partial methods do not necessarily require a body. A partial method is merely a declaration of intent. It is basically telling the compiler that if any such methods exist throughout the project it should be called; otherwise any invocation of a partial method results in a no-op.

At this point, you can freely enter changes to the product object with the certainty that they will be detected and saved back to the database.


LINQ-to-SQL is rigidly pushing a 1:1 correspondence between tables and classes and between columns and properties. However, the mechanism of partial classes makes it possible for you to do some extra things like adding non-bound properties to an entity class, calculated properties, and also wrapper properties. To get this, you have to modify the source code generated by the designer and, more importantly, understand the role of each method and attribute in designer’s code.

<<  Previous Article Continue reading and see our next or previous articles Next Article >>

About Dino Esposito

Dino Esposito is one of the world's authorities on Web technology and software architecture. Dino published an array of books, most of which are considered state-of-the-art in their respective areas. His most recent books are “Microsoft ® .NET: Architecting Applications for the Enterprise” and “...

This author has published 54 articles on DotNetSlackers. View other articles or the complete profile here.

Other articles in this category

C# 4.0 Reflection Programming - Part 2
As introduced in the first article, the most typically-used tools associated with .NET reflection ar...
C# 4.0 Reflection Programming - Part 3
In the previous article, we used the reflection to obtain the information of an assembly, module, ty...
C# 4.0 Reflection Programming - Part 4
In this last article of this series, we will learn what to do with reflection. But before making the...
Understanding and Using Extension Methods
Extension methods were new to C# 3.0. They allow you to add a method to an existing type without hav...
Introduction to C# 3.0 features
C# 3.0 introduced some of very useful features built on top of 2.0. This article explains the usage,...

You might also be interested in the following related blog posts

Migrating to Postgresql with my friend NHibernate read more
Linq For Silverlight Developers read more
Important Entity Framework Query Improvements for .NET 4.0 read more
OleDb Parameters to access FoxPro Data from .NET read more
Create custom LINQ providers fluently read more
LINQ to Entities, Entity SQL, Parameterized Store Queries and SQL Injection read more
Creating LINQToTwitter library using LinqExtender read more
date validations read more
Bundle SQL script with build process using MSBuild read more
Athena - A LINQ to flickr API (Release 1.4) read more


Subject Author Date
placeholder Error: The member 'Product.Unit' has no supported translation to SQL. Aishel Mortillaro 7/15/2009 8:26 AM

Please login to rate or to leave a comment.