Book review: NHibernate in Action now in print

imageIt took the authors of NHibernate in Action a very long time to complete this book. It was already in the summer of 2006 that I got involved in the NHibernate in Action book. First steps were basically reviewing the first chapters manuscript. Later on I also reviewed other chapters. About half a year ago, or maybe it was even three quarter of a year ago (time goes fast these days), Manning asked me to do the Technical Proofreading of NHïbernate in Action. In het end the book finally got to print. And yes I really like it. Although I would have been very happy to have this book in the year 2006, I’m glad to have it now. Besides the fact that this is a NHibernate book, it also covers ORM in general. And a lot patterns and concepts discussed in this book apply to other ORMs as well, like Linq2SQL and the Entity Framework.

Linq to SQL doing it manually: Part 3 - Relationships

Beside column mapping we did in Part 1 of the "Linq to SQL doing it manually" series. We also want to have relationships, this part will be all about relationships. The series contains the following parts:

When we take a look at the generated code for a one-to-many relation it has the following looks.

For the one-side:

1 private EntityRef<Post> _Post; 2 [Association(Name="Post_PostTagRelation", Storage="_Post", ThisKey="PostId", OtherKey="Id", IsForeignKey=true)] 3 public Post Post 4 { 5 get 6 { 7 return this._Post.Entity; 8 } 9 set 10 { 11 Post previousValue = this._Post.Entity; 12 if (((previousValue != value) 13 || (this._Post.HasLoadedOrAssignedValue == false))) 14 { 15 this.SendPropertyChanging(); 16 if ((previousValue != null)) 17 { 18 this._Post.Entity = null; 19 previousValue.PostTagRelations.Remove(this); 20 } 21 this._Post.Entity = value; 22 if ((value != null)) 23 { 24 value.PostTagRelations.Add(this); 25 this._PostId = value.Id; 26 } 27 else 28 { 29 this._PostId = default(System.Guid); 30 } 31 this.SendPropertyChanged("Post"); 32 } 33 } 34 } 35 36 private System.Guid _PostId; 37 [Column(Storage="_PostId", DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true)] 38 public System.Guid PostId 39 { 40 get 41 { 42 return this._PostId; 43 } 44 set 45 { 46 if ((this._PostId != value)) 47 { 48 if (this._Post.HasLoadedOrAssignedValue) 49 { 50 throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException(); 51 } 52 this.OnPostIdChanging(value); 53 this.SendPropertyChanging(); 54 this._PostId = value; 55 this.SendPropertyChanged("PostId"); 56 this.OnPostIdChanged(); 57 } 58 } 59 }

For the many-side:

1 private EntitySet<PostTagRelation> _PostTagRelations; 2 [Association(Name="Post_PostTagRelation", Storage="_PostTagRelations", ThisKey="Id", OtherKey="PostId")] 3 public EntitySet<PostTagRelation> PostTagRelations 4 { 5 get 6 { 7 return this._PostTagRelations; 8 } 9 set 10 { 11 this._PostTagRelations.Assign(value); 12 } 13 }

I think this is a lot of code. So let's try to minimize this.

For the one-side:

1 [Column(IsPrimaryKey = true)] 2 private Guid PostId { get; set; } 3 4 [Association(IsForeignKey = true, ThisKey = "PostId")] 5 public Post Post { get; set; }

For the many-side:

1 private IList<PostTagRelation> postTagRelations = new List<PostTagRelation>(); 2 [Association(Storage = "postTagRelations", OtherKey = "PostId")] 3 public IList<PostTagRelation> PostTagRelations 4 { 5 get { return postTagRelations; } 6 set { postTagRelations = value; } 7 }

A lot less code I would guess. From 72 lines to just 12 lines. But we are not there yet. After some testing I found out the generated relation is bi-directional, and the relation I created is just uni-directional. What does this mean? This means that if you manipulated either post.PostTagRelations or postTagRelation.Post the other will be changed as well in a bi-directional relation. I remember in the past you had to do something special to make this work with NHibernate. I have no solution for this right now, but you can take a look at how other people did something similar on CodePlex. One of the troubles I find in the solution they propose is the usage of EntityRef and EntitySet. Those are both parts of Linq to SQL and would kind of interfere with my other code-parts, if used.

Another feature that is lost is lazy loading. I must admit I don't hate it being lost. I think people must think about persistence before doing everything automatically. So if we want to load only the tags we don't have to anything special. But if we also want to load the PostTagRelations and the Posts with the tags we will have to add some DataLoadOptions. The following code example show how it works.

1 using (var context = new Context("")) 2 { 3 var dataLoadOptions = new DataLoadOptions(); 4 dataLoadOptions.LoadWith<Tag>(t => t.PostTagRelations); 5 dataLoadOptions.LoadWith<PostTagRelation>(ptr => ptr.Post); 6 //dataLoadOptions.LoadWith<PostTagRelations>(ptr => ptr.Tag); 7 context.LoadOptions = dataLoadOptions; 8 9 var dotNetTag = (from tag in context.Tags 10 where tag.Name.Equals(".NET") 11 select tag).Single(); 12 13 Assert.Equal(3, dotNetTag.PostTagRelations.Count()); 14 15 var posts = from postTagRelation in dotNetTag.PostTagRelations 16 where postTagRelation.Post != null 17 select postTagRelation.Post; 18 Assert.Equal(3, posts.Count()); 19 20 var tags = from postTagRelation in dotNetTag.PostTagRelations 21 where postTagRelation.Tag != null 22 select postTagRelation.Tag; 23 Assert.Equal(3, tags.Count()); 24 }

The only thing I must admit, line 23 fails. The tags aren't loaded in the PostTagRelations. I tried adding line 6 but this did throw an exception: "System.InvalidOperationException : Cycles not allowed in LoadOptions LoadWith type graph." I have no solution for this yet, but it has something to do with the bi-directional issue I also found.

There are some areas of improvement on the relationships with Linq to SQL.

Linq to SQL doing it manually: Part 2 - Database creation

This week I started a series on Linq to SQL doing it manually. The serie contains of the following parts:

This part I'll introduce the actual creation of the database, tables and columns. The people that already have made use of Linq to SQL through the dbml-designer, know the generation of a class that inherits from DataContext. The DataContext class contains an method called CreateDatabase.

But the documentation mentions the algorithm that's used:

DataContext.CreateDatabase Method

  • If a database is identified in the connection string, its name is used.
  • If a DatabaseAttribute attribute is present, its Name property is used as the name of the database.
  • If there is no database tag in the connection string and a strongly typed DataContext is used, a database that has the same name as the DataContext inheriting class is checked.
  • If a weakly typed DataContext is used, an exception is thrown.
  • If the DataContext has been created by using a file name, the database corresponding to that file name is created.

I just tried something out to get to know more about it. What I first did, was create a mapping for a few classes so that tables for those classes could be created. I also created a database where I wanted to get generated tables.

So my first approach was the following:

  1. Instantiate a DataContext class with a connection-string.
  2. Call CreateDatabase method.

1 var context = new DataContext(@"..."); 2 context.CreateDatabase();

What happened was an exception. Just according to the algorithm in the documentation. Although the exception message was somewhat strange: "Unable to create database because data context 'DataContext' has no tables." I think this is kind of a strange message, but the documentation already mentions this wasn't going to work.

My second approach.

  1. Create a class derived from DataContext.
  2. Instantiate the derived class with a connection-string.
  3. Call CreateDatabase method on the derived class.
1 public class EmptyContext : DataContext 2 { 3 public EmptyContext(string fileOrServerOrConnection) 4 : base(fileOrServerOrConnection) 5 { 6 } 7 }

var context = new EmptyContext(@"..."); context.CreateDatabase();

Still no luck, I got an exception again. The message was the same though: "Unable to create database because data context 'DataContext' has no tables." This got me thinking. The Context needs to know about the mapped classes in some way. So I took a look at the context that's generated by the designer.

I found get properties for each table/class that was mapped.

My third approach, let's add some properties.

  1. Create a class derived from DataContext.
  2. Add properties of Table<mappedClassType> to the DataContext.
  3. Call CreateDatabase method on the derived class.

1 public class Context : DataContext 2 { 3 public Context(string fileOrServerOrConnection) : base(fileOrServerOrConnection) 4 { 5 } 6 7 8 public Table<Tag> Tags 9 { 10 get { return GetTable<Tag>(); } 11 } 12 13 public Table<Post> Posts 14 { 15 get { return GetTable<Post>(); } 16 } 17 18 public Table<PostTagRelation> PostTagRelations 19 { 20 get { return GetTable<PostTagRelation>(); } 21 } 22 }

1 var context = new Context(@"..."); 2 context.CreateDatabase();

Very nice, this solution almost created the database. I got an error that the database already existed, so I deleted it, and tried again. Succes!

Linq to SQL doing it manually: Part 1 - Table and column creation from C#

I was very interesting in doing Linq to SQL manually. Yes manually, not making use of the dbml-designer. And why should I want to do this? I thought it would be interesting to do Linq to SQL without a database being available yet.

I've also heard the rumor that Linq to SQL is dead, or almost dead. I'm not sure about this, but I still think Linq to SQL can be appropriate for simple situations, where Linq to Entities is for the more advanced scenario's it's also more complex to apply.

Attribute-Based mapping

What I need is Attribute-Based mapping I guess. The documentation talks about 9 different attributes:

  • DatabaseAttribute
  • TableAttribute
  • ColumnAttribute
  • AssociationAttribute
  • InheritanceMappingAttribute
  • FunctionAttribute
  • ParameterAttribute
  • ResultTypeAttribute
  • DataAttribute

In part 1, I will concentrate on the TableAttribute and the ColumnAttribute.

The TableAttribute

The TableAttribute makes sure a class gets mapped to a database table. As we have no table yet, we want to have as less code as possible. The following code makes sure a class gets mapped to a table. The class Tag gets mapped to the table named Tag.

[Table] public class Tag {}

Sometimes you want to have some control on the naming of the table. This is possible by just applying the Name property. This is just as simple as the following code. The class Tag gets mapped to the table named Tags.

[Table(Name="Tags")] public class Tag {}

This is all I have to say about the TableAttribute.

The ColumnAttribute

The ColumnAttribute is a little more advanced than the TableAttribute. But it can also be as simple as the TableAttribute. This is because all the primitive types are automatically mapped to a database type. But where do we start? Just very simple just add the ColumnAttribute to a property. The property Name gets mapped to the database column named Name.

[Column] public string Name { get; set; }

If you want the Name property to be mapped to the database column CustomerName we can do the following.

[Column(Name="CustomerName")] public string Name { get; set; }

It's possible to have some influence on the type that's used in the database for a column. I think this can be handy sometimes. For example if you want the Name field to be maximized to 25 characters and also not nullable, you can use this.

[Column(DbType="NVarChar(25) NOT NULL")] public string Name { get; set; }

But I like the feature that Linq to SQL does the mapping himself. The following diagram is derived from MSDN and shows what mappings are possible.

Mapping for SQL and CLR Types

The default mapping for the CLR types are the following:

CLR Type Default SQL Type
System.Enum INT
System.Boolean BIT
System.Byte TINYINT
System.Int16 SMALLINT
System.Int32 INT
System.Int64 BIGINT
System.UInt16 INT
System.UInt32 BIGINT
System.UInt64 DECIMAL (20)
System.Decimal DECIMAL (29,4)
System.Single REAL
System.Double FLOAT
System.Char NCHAR(1)
System.String NVARCHAR(4000)
System.Char[] NVARCHAR(4000)
Custom type implementing Parse() and ToString() NVARCHAR(MAX)
System.DateTime DATETIME
System.DateTimeOffset DATETIMEOFFSET
System.TimeSpan TIME
System.Date.Linq.Binary VARBINARY(MAX)
System.Runtime.Serialization.ISerializable VARBINARY(MAX)
System.Object SQL_VARIANT

We also want some columns to be identifying. This can be done by setting the IsPrimaryKey property that's part of the ColumnAttribute. The following example makes the property Id the primary key. It's possible to have multiple properties to define a composed primary key.

[Table] public class Tag { [Column(IsPrimaryKey = true)] public Guid Id { get; set; } [Column] public string Name { get; set; } }

Next article will be about the actual generation of the tables and columns from the class definition.

LINQ to SQL: Aggregations on multiple columns without any grouping

I think the title speaks for itself. I want to create a LINQ query with aggregations on multiple columns where no grouping is applied. This is just something that can be done very simple in plain SQL.

1 SELECT 2 MAX(HorsePower), 3 AVG(Weight) 4 FROM 5 Car

But when you want to create a construction that's similar in LINQ you will get an compilation error, because of a wrong syntax.

1 var result = from car in sut.Cars 2 select new 3 { 4 MaxHorsePower = car.Max(p => p.HorsePower), 5 AverageWeight = car.Average(p => p.Weight) 6 };

The trouble is that this doesn't work. You can do an aggregation on an complete set, like the following. This will result in just two queries.

1 var result2 = (from car in sut.Cars select car.HorsePower).Max(); 2 var result3 = (from car in sut.Cars select car.Weight).Average();

You can actually do multiple aggregations at once when using groups. The sad thing is, we don't always have something to group on. But that can be solved.

1 var result = (from car in 2 (from car in sut.Cars 3 select new 4 { 5 car.HorsePower, 6 car.Weight, 7 Dummy = string.Empty 8 }) 9 group car by new {car.Dummy} 10 into dummyGroup 11 select new 12 { 13 MaxHorsePower = dummyGroup.Max(p => p.HorsePower), 14 AverageWeight = dummyGroup.Average(p => p.Weight) 15 }).Single();

This solution adds a dummy column called 'Dummy' with the same value for every record. Because this value is the same for every record we can safely group on it and expect to have only one group. This solution makes sure we have one query for the database, but with the use of a workaround.

I would say yes this works, but for readability I would not suggest the use of this workaround for the trivial problem above. By using two queries we have a very manageable solution that's readable from code. The workaround needs at least some comments before we can totally understand it as someone new to the workaround.

But it in the end we can also say, we now know how we can fake the grouping. Use it with care, as I mentioned because of the manageability of your code.