On the second day at TechEd, the first session I joined had the title “Cool Looking 3D visualizations with Windows Presentation Foundation (WPF)” (Speaker: Dennis Vroegop).

The WPF has a “built-in” 3D-API, and this session showed what you can do with 3D in your business applications. The 3D-API of WPF isn’t adequate for great 3D Games. For Games you should use Managed DirectX that has a much better performance than WPF’s 3D-API (which is build on a “Media Integration Layer” (Milcore) that is built on top of DirectX). WPF’s 3D-API is an easy way to bring some great 3D effects to your business applications. This session showed beside the basics of 3D an example how 3D can be used for a better data representation. The used example showed a 3D map and represented product-data for different locations as 3D-objects.

LINQ to SQL: Accessing Relational Data with Language was the session I joined next, speaker was Luca Bolognese. LINQ to SQL is the easiest way to access a relational database with .NET 3.5. In LINQ to SQL entity classes are mapped directly to tables in the database. For each table a class. To query a database a developer hasn’t to know anything about SQL. The query is defined in C# with the LINQ-Syntax introduced in C# 3.0. The SQL sent to the database is generated automatically. Let’s look at a very simple example to load some data.

To load data with LINQ to SQL, the first thing you have to do is to add a reference to the assembly System.Data.Linq.dll to your project and then create your entity classes. The entity classes are mapped to a table in the database via the attributes Table and Column contained in the namespace System.Data.Linq.Mapping. Of course Visual Studio 2008 contains a designer, that allows you to drag’n’drop tables from the server explorer to the designer surface, and it’ll create the entity classes for you. In this example I’ll just use a manually created class:

[Table(Name="Friends")]
class Friend
{
  [Column(IsPrimaryKey=true)]
  public Guid FriendID;
  [Column]
  public string Name;
  [Column]
  public int Age;
}

As shown above, the Class Friend maps to the table Friends as specified with the TableAttribute. You could also specify the TableAttribute without setting the Name-Property, then your classname must match exactly the table name in the database. The table Friends in the database contains the columns FriendID, Name and Age. You see exactly the same columns in the Friend class. The ColumnAttribute also has a Name-Property that you can use if your .NET field or property doesn’t match the columnname from the table in the database. The column FriendID is additionally marked as primary key. Only those fields/properties you’ve marked with an ColumnAttribute are persisted in the database and could be loaded from database via LINQ to SQL. Ok, so far we’ve created a Friend class mapping to a Friends table in the database.

Now we can already connect to the Database and query the Friends table by using the DataContext class. The DataContext is used similar to the ADO.NET Connection. In fact the DataContext constructor takes a connection string and internally uses an IDbConnection. The DataContext is responsible to translate the queries you write in LINQ against your objects to SQL. To view the SQL the DataContext has generated for you, assign a TextWriter to its Log-Property. In the following snippet I’ve just assigned the standard output stream of the console to the Log-Property of the DataContext. I’ve passed in a connection string for the database “hubethomsDB” in my local SQLEXPRESS instance to the constructor of the DataContext:

DataContext ctx = new DataContext(
  @"Server=.\SQLEXPRESS;Database=hubethomsDB");

ctx.Log = Console.Out;

In LINQ to SQL every table is represented by a Table-Collection of type System.Data.Linq.Table<TEntity>. As the name implies, the Table itself is identified by the entity class. Back to our sample, you get a Table-Collection for the table Friends by calling the generic Method GetTable<TEntity> on the DataContext and give the Method the Friend entity as the generic argument:

Table friends = ctx.GetTable();

On the received Table-Collection, you can create a query in LINQ. And now there’s a big difference to SQL. The query isn’t executed by the statement you see below, it’s only created.

var query = from f in friends
            where f.Age < 30
            select f;

The query gets executed, when you iterate over it, not before!!!

foreach (var friend in query)
{
  Console.WriteLine(friend.ToString());
}

The SQL generated by the DataContext is shown in the console window below. As you can see in the generated SQL, the data is filtered on the database and not in .NET.

Luca Bolognese said in his session, that Microsoft has tried every possible query and the DataContext was able to create a good SQL statement out of it. The tests have been done by people working for long times in the sqlserver team. Time to try some real complex queries on it and check it out.

What is the benefit of LINQ to SQL, when classes are mapped directly to tables?

The biggest benefit out of LINQ to SQL is that a developer hasn’t to know anything about SQL, he can specify and execute his query directly in C#.

Conclusion: I haven’t tested real complex SQL statements with LINQ to SQL up to now. Interesting are also things like indexes used by the generated query etc.