News
Photos
Articles
Components
Applications
Kleinkunst

.NET - LINQ to SQL - part 2

In part 1 of this series of articles about LINQ to SQL I covered the following topics :

In this article I will show how to use the LINQPad tool and the inheritance features of LINQ to SQL :

In the next articles I will cover the following topics :

 

LINQPad

Joseph Albahari has released a fantastic free tool called LINQPad (http://www.linqpad.net). This standalone application, which is still in beta, is an advanced LINQ expressions testing and learning tool.

Just add a connection to your (SQL Server) database and LINQPad will create a LINQ to SQL object model of your tables. Then you can start executing LINQ to SQL queries. The results will be displayed as HTML tables.

You can use multiple statements and LINQPad contains a Dump extension method which can almost dump anything to the HTML result view. You can even dump every step in your LINQ to SQL query.

It is also possible to refer to external assemblies so you can call your own functions in the LINQ to SQL queries. Referring to an external assembly can also be used to utilize your own DataContext object instead of a standard database connection. This is great when you've created your own DataContext in Visual Studio 2008 (beta) with renamed properties, hided properties, non-mapped partial class properties, new derived classes, ...

 

Inheritance

Using inheritance and derived classes is quite easy in LINQ to SQL. In this chapter I will show you step by step how to use this technology.

 

Adding inheritance

1) Add new classes

Add 2 new classes UKEmployee and USAEmployee in the LINQ to SQL designer.

 

2) Add inheritance

Select the base class Employee, right click and choose Add inheritance. Select Employee as base class and UKEmployee as derived class. A line between the two entities will be drawn. Do the same for USAEmployee.

 

3) Setting up the properties

Select the inheritance line of the UKEmployee class. First we have to fill in the Discriminator Property property. This is the column/property of the base entity which will hold the discriminator value for the inheritance hierarchy. In this example this will be Country.

Secondly, you have to set the Derived Class Code property which holds the discriminator value. For the UKEmployee this should be UK and for the USAEmployee USA.

Finally we have to specify the Inheritance Default property. On of the inheritance mappings should be the default one. This is a kind of fallback when the discriminator value does not match any Code values

The base class is also listed in this combobox but do not select it ! This will result in 2 default inheritance entities or just none. Everything will compile, but when executing a query a "The inheritance hierarchy rooted at 'Northwind.Employee' does not define a default" or "The inheritance type 'Northwind.Employee' has multiple defaults" exception will be thrown.

 

4) Auto-generated code

Let's take a look at the auto-generated source code in the designer.cs file.

Some [InheritanceMapping] attributes have been added to the base class Employee. This attribute describes the Code property which is the value in the discriminator column and the Type property which refers to the derived class. The IsDefault property of the [InheritanceMapping] serves as a fallback mapping when the discriminator value does not match any Code values.

The discriminator column, in this case Country, has an IsDiscriminator property for the [Column] attribute. 

[Table(Name="dbo.Employees")]
[InheritanceMapping(Code="UK", Type=typeof(UKEmployee), IsDefault=true)]
[InheritanceMapping(Code="USA", Type=typeof(USAEmployee))]
public partial class Employee : INotifyPropertyChanging, INotifyPropertyChanged
  {
  [Column(Storage="_Country", DbType="NVarChar(15)", IsDiscriminator=true)]
  public string Country
  {
    get { }
    set { }
  }
}

 

5) Bug in designer

There is a bug in the Visual Studio 2008 beta designer when using the Inheritance Default property. Once the Inheritance Default has been set to an entity, it can not be changed anymore. So be sure to select the correct entity because otherwise you have to delete the inheritance and add it again. Young Joo of Microsoft did assure me that this bug is already fixed and will be shipped in the final version of Visual Studio 2008.

 

Querying with derived classes

Derived classes are not mapped directly to a table in the database. So it seems that the derived classes do not have their own collection of entities. That is why you have to query on the base class collection, in this case Employees. If you only want to retrieve the USA employees, you have to add a Where-clause. 

NorthwindDataContext dc = new NorthwindDataContext();
 
var employees = from emp in dc.Employees
                where emp is USAEmployee
                select emp;
 
foreach (var emp in employees)
  Console.WriteLine("- {0} {1} {2} {3}", emp.EmployeeID, emp.FirstName, emp.LastName, emp.Country);

 

Adding objects of derived classes

When adding an entity of a derived class, you do not have to fill in the value of the discriminator column. After executing this example, the Country property of the new employee entity (record/row) will automatically be set to USA. 

USAEmployee employee = new USAEmployee() { FirstName = "John", LastName = "Doo" };
dc.Employees.Add(employee);
dc.SubmitChanges();

 

Extending inherited classes

Querying and adding derived classes works fine, but our two empty derived classes aren't really useful. So let's extend the USAEmployee class. The Region property is only filled in when the employees lives in the USA. So remove the Region property from the base class Employee and add it to our USAEmployee class (NVarChar(15)). There is no USAEmployee table in the database, so the Employee table is still the owner of the Region field/column.

If you want to retrieve the USA employees and access the properties of the derived class, then you have to typecast the result. When you do the same for the UKEmployee you will notice that you can not access the Region property. 

NorthwindDataContext dc = new NorthwindDataContext();
 
var employees = from emp in dc.Employees
                where emp is USAEmployee 
                select (USAEmployee)emp;

 
foreach (var emp in employees)  
  Console.WriteLine("- {0} {1} {2} {3} {4}", emp.EmployeeID, emp.FirstName, emp.LastName, emp.Country, emp.Region);

LINQ to SQL uses the single table inheritance pattern. This means one table in the database and several derived classes in the object model. As you can see this kind of inheritance with LINQ to SQL is quite easy to use and it can be very powerful.