I am using R# 4.5, and I have to say, for  a release that is supposedly all about perf, they managed to squeeze in some really nice new smarts:

Figuring out all the implementations that use or do not use parameters.

Killing off BDD :-)

The presentations from Oredev are now available, and among them is my talk about Active Record.

You can watch it here, the blurb is:

What would you say if I told you that you can stop writing data access code in .Net? Aren't you tired of writing the same thing over and over again, opening connection, querying the database, figuring out what to return, getting back untype data that you need to start putting on the form? Do you really see some value in writing yet another UPDATE statement?The Active Record framework allows you to fully utilize the power of the database, but without the back breaking work that it used to take. Active Record uses .Net objects to relieve you from the repeating task of persistence. Those objects are schema aware and can persist and load themselves without you needing to write a single line of SQL. Building business application using Active Record is a pleasure, the database stuff just happens, and you are free to implement the business functionality.

To be frank, I consider this to be one of the best presentations that I gave. Everything just ticked.

<many-to-any/> is the logical extension of the <any/> feature that NHibernate has. At the time of this writing, if you do a Google search on <many-to-any/>, the first result is this post. It was written by me, in 2005, and contains absolutely zero useful information. Time to fix that.

Following up on the <any/> post, let us say that we need to map not a single heterogeneous association, but a multiple heterogeneous one, such as this:

In the database, it would appear as:

How can we map such a thing?

Well, that turn out to be pretty easy to do:

<set name="Payments" table="OrderPayments" cascade="all">
	<key column="OrderId"/>
	<many-to-any id-type="System.Int64"
			meta-type="System.String">
		<meta-value value="CreditCard"
			class="CreditCardPayment"/>
		<meta-value value="Wire"
			class="WirePayment"/>
		<column name="PaymentType" 
			not-null="true"/>
		<column name="PaymentId"
			not-null="true"/>
	</many-to-any>
</set>

Now, let us look at how we use this when we insert values:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var order = new Order
	{
		Payments = new HashSet<IPayment>
        {
        	new CreditCardPayment
        	{
        		Amount = 6,
                CardNumber = "35434",
                IsSuccessful = true
        	},
            new WirePayment
            {
            	Amount = 3,
                BankAccountNumber = "25325",
                IsSuccessful = false
            }
        }
	};
	session.Save(order);
	tx.Commit();
}

This will produce some very interesting SQL:

I think that the SQL make it pretty clear what is going on here, so let us move to a more fascinating topic, what does NHibernate do when we read them?

Here is the code:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var order = session.Get<Order>(1L);
	foreach (var payment in order.Payments)
	{
		Console.WriteLine(payment.Amount);
	}
	tx.Commit();
}

And the SQL:

As you can see, this is about as efficient as you can get. We load the order, we check what tables we need to check, and the we select from each of the tables that we found to get the actual values in the association.

True heterogeneous association, not used very often, but when you need it, you really love it when you do.

Sometimes, well known associations just don’t cut it. We sometimes need to be able to go not to a single table, but to a collection of table. For example, let us say that an order can be paid using a credit card or a wire transfer. The data about those are stored in different tables, and even in the object model, there is no inheritance association them.

From the database perspective, it looks like this:

As you can see, based on the payment type, we need to get the data from a different table. That is somewhat of a problem for the standard NHibernate mapping, which is why we have <any/> around.

Just to close the circle before we get down into the mapping, from the object model perspective, it looks like this:

In other words, this is a non polymorphic association, because there is no mapped base class for the association. In fact, we could have used System.Object instead, but even for a sample, I don’t like it.

The mapping that we use are:

<class name="Order"
			 table="Orders">

	<id name="Id">
		<generator class="native"/>
	</id>

	<any name="Payment" id-type="System.Int64" meta-type="System.String" cascade="all">
		<meta-value value="CreditCard" class="CreditCardPayment"/>
		<meta-value value="Wire" class="WirePayment"/>
		<column name="PaymentType"/>
		<column name="PaymentId"/>
	</any>

</class>

<class name="CreditCardPayment"
			 table="CreditCardPayments">
	<id name="Id">
		<generator class="native"/>
	</id>
	<property name="IsSuccessful"/>
	<property name="Amount"/>
	<property name="CardNumber"/>
</class>

<class name="WirePayment"
			 table="WirePayments">
	<id name="Id">
		<generator class="native"/>
	</id>
	<property name="IsSuccessful"/>
	<property name="Amount"/>
	<property name="BankAccountNumber"/>
</class>

Pay special attention to the <any/> element. Any <meta-value/> declaration is setting up the association between the type as specified in the PaymentType column and the actual class name that it maps to. The only limitation is that all the mapped class must have the same data type for the primary key column.

Let us look at what this will give us:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var order = new Order
	{
		Payment = new CreditCardPayment
		{
			Amount = 5,
			CardNumber = "1234",
			IsSuccessful = true
		}
	};
	session.Save(order);
	tx.Commit();
}

Which produces:

And for selecting, it works just the way we would expect it to:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var person = session.Get<Order>(1L).Payment;
	Console.WriteLine(person.Amount);
	tx.Commit();
}

The generated SQL is:

An interesting limitation is that you cannot do an eager load on <any/>, considering the flexibility of the feature, I am most certainly willing to accept that limitation.

I just posted about a horribly complicated test, I thought I might as well share the results of its refactoring:

[TestFixture]
public class IndexedEmbeddedAndCollections : SearchTestCase
{
	private Author a;
	private Author a2;
	private Author a3;
	private Author a4;
	private Order o;
	private Order o2;
	private Product p1;
	private Product p2;
	private ISession s;
	private ITransaction tx;

	protected override IList Mappings
	{
		get
		{
			return new string[]
			{
				"Embedded.Tower.hbm.xml",
				"Embedded.Address.hbm.xml",
				"Embedded.Product.hbm.xml",
				"Embedded.Order.hbm.xml",
				"Embedded.Author.hbm.xml",
				"Embedded.Country.hbm.xml"
			};
		}
	}

	protected override void OnSetUp()
	{
		base.OnSetUp();

		a = new Author();
		a.Name = "Voltaire";
		a2 = new Author();
		a2.Name = "Victor Hugo";
		a3 = new Author();
		a3.Name = "Moliere";
		a4 = new Author();
		a4.Name = "Proust";

		o = new Order();
		o.OrderNumber = "ACVBNM";

		o2 = new Order();
		o2.OrderNumber = "ZERTYD";

		p1 = new Product();
		p1.Name = "Candide";
		p1.Authors.Add(a);
		p1.Authors.Add(a2); //be creative

		p2 = new Product();
		p2.Name = "Le malade imaginaire";
		p2.Authors.Add(a3);
		p2.Orders.Add("Emmanuel", o);
		p2.Orders.Add("Gavin", o2);


		s = OpenSession();
		tx = s.BeginTransaction();
		s.Persist(a);
		s.Persist(a2);
		s.Persist(a3);
		s.Persist(a4);
		s.Persist(o);
		s.Persist(o2);
		s.Persist(p1);
		s.Persist(p2);
		tx.Commit();

		tx = s.BeginTransaction();

		s.Clear();
	}

	protected override void OnTearDown()
	{
		// Tidy up
		s.Delete("from System.Object");

		tx.Commit();

		s.Close();

		base.OnTearDown();
	}

	[Test]
	public void CanLookupEntityByValueOfEmbeddedSetValues()
	{
		IFullTextSession session = Search.CreateFullTextSession(s);

		QueryParser parser = new MultiFieldQueryParser(new string[] { "name", "authors.name" }, new StandardAnalyzer());

		Lucene.Net.Search.Query query = parser.Parse("Hugo");
		IList result = session.CreateFullTextQuery(query).List();
		Assert.AreEqual(1, result.Count, "collection of embedded (set) ignored");
	}

	[Test]
	public void CanLookupEntityByValueOfEmbeddedDictionaryValue()
	{
		IFullTextSession session = Search.CreateFullTextSession(s);
		
		//PhraseQuery
		TermQuery  query = new TermQuery(new Term("orders.orderNumber", "ZERTYD"));
		IList result = session.CreateFullTextQuery(query).List();
		Assert.AreEqual(1, result.Count, "collection of untokenized ignored");
		query = new TermQuery(new Term("orders.orderNumber", "ACVBNM"));
		result = session.CreateFullTextQuery(query).List();
		Assert.AreEqual(1, result.Count, "collection of untokenized ignored");
	}

	[Test]
	[Ignore]
	public void CanLookupEntityByUpdatedValueInSet()
	{
		Product p = s.Get<Product>(p1.Id);
		p.Authors.Add(s.Get<Author>(a4.Id));
		tx.Commit();

		QueryParser parser = new MultiFieldQueryParser(new string[] { "name", "authors.name" }, new StandardAnalyzer());
		IFullTextSession session = Search.CreateFullTextSession(s);
		Query query = parser.Parse("Proust");
		IList result = session.CreateFullTextQuery(query).List();
		//HSEARCH-56
		Assert.AreEqual(1, result.Count, "update of collection of embedded ignored");

	}
}

It is almost the same as before, the changed are mainly structural, but it is so much easier to read, understand and debug.

I think, without question, that this is one of the most horrible nasty tests that I have seen. It has been ported as is from the java code, and you can literally feel the nastiness in reading it:

[Test]
public void IndexedEmbeddedAndCollections()
{
	Author a = new Author();
	a.Name = "Voltaire";
	Author a2 = new Author();
	a2.Name = "Victor Hugo";
	Author a3 = new Author();
	a3.Name = "Moliere";
	Author a4 = new Author();
	a4.Name = "Proust";

	Order o = new Order();
	o.OrderNumber = "ACVBNM";

	Order o2 = new Order();
	o2.OrderNumber = "ZERTYD";

	Product p1 = new Product();
	p1.Name = "Candide";
	p1.Authors.Add(a);
	p1.Authors.Add(a2); //be creative

	Product p2 = new Product();
	p2.Name = "Le malade imaginaire";
	p2.Authors.Add(a3);
	p2.Orders.Add("Emmanuel", o);
	p2.Orders.Add("Gavin", o2);


	ISession s = OpenSession();
	ITransaction tx = s.BeginTransaction();
	s.Persist(a);
	s.Persist(a2);
	s.Persist(a3);
	s.Persist(a4);
	s.Persist(o);
	s.Persist(o2);
	s.Persist(p1);
	s.Persist(p2);
	tx.Commit();

	s.Clear();

	IFullTextSession session = Search.CreateFullTextSession( s );
	tx = session.BeginTransaction();

	QueryParser parser = new MultiFieldQueryParser( new string[] { "name", "authors.name" }, new StandardAnalyzer() );

	Lucene.Net.Search.Query query = parser.Parse( "Hugo" );
	IList result = session.CreateFullTextQuery( query ).List();
	Assert.AreEqual( 1, result.Count, "collection of embedded ignored" );

	//update the collection
	Product p = (Product) result[0];
	p.Authors.Add( a4 );

	//PhraseQuery
	query = new TermQuery( new Term( "orders.orderNumber", "ZERTYD" ) );
	result = session.CreateFullTextQuery( query).List();
	Assert.AreEqual( 1, result.Count, "collection of untokenized ignored" );
	query = new TermQuery( new Term( "orders.orderNumber", "ACVBNM" ) );
	result = session.CreateFullTextQuery( query).List();
	Assert.AreEqual( 1, result.Count, "collection of untokenized ignored" );

	tx.Commit();

	s.Clear();

	tx = s.BeginTransaction();
	session = Search.CreateFullTextSession( s );
	query = parser.Parse( "Proust" );
	result = session.CreateFullTextQuery( query ).List();
	//HSEARCH-56
	Assert.AreEqual( 1, result.Count, "update of collection of embedded ignored" );

	// Tidy up
	s.Delete(a);
	s.Delete(a2);
	s.Delete(a3);
	s.Delete(a4);
	s.Delete(o);
	s.Delete(o2);
	s.Delete(p1);
	s.Delete(p2);
	tx.Commit();

	s.Close();
}

Point to anyone who want to start a list of how many good test metrics this test violates.

Oh, and right now, this test fails.

The news are just out, Oracle is buying Sun.

This is especially interesting since Oracle has previously bought InnoDB (a key component for MySQL in the enterprise) and Sun has bought MySQL. This means that, off the top of my head, Oracle is now the owner of the following database products:

• Oracle DB
• BerkleyDB
• MySQL & InnoDB

I am pretty sure that they have others, but even so, that is quite a respectable list, I should think.

Of course, with Sun, Oracle is also getting Java…

Justin has a post called: Keep your IQueryable in check, in which he basically says that while he finds value in the Query Object pattern that I recently talked about, he is concerned by the fact that I am returning an IQueryable (or ICriteria or IQuery) from the query object. The problem is that the UI layer can then modify the query in potentially undesirable ways. The example that he gave is:

latePayingCustomQuery  
  .Where(c => c.LastName.StartsWith("E"))  
  .Skip((page - 1) * itemCount)  
  .Take(10);  

Which perform a query on an unindex string column, which is slow.

He suggests limiting what the user can do by providing a IPagable && ISortable that would limit the options of the UI layer to abuse it.

My response to that is: And…?

I am not in favor of doing things to protect developers, but there is also another side to that.

What is the worst thing that can possibly happen? That part of the application would be slow. We will find it during testing, or (heaven forbid), on production.

And…?

How is this in any way different than anything else that we are doing? Presumably the developer who wrote the code didn’t wrote it just because he felt like it, there was a business reason behind it. At that point, we treat this issue as a standard perf issue, and fix it.

The cost of doing that in the rare cases where it will happen vs. the cost of creating the limited abstraction, forcing developers to deal with that, etc. More than that, I can assure you that you will need to break out of the straightjacket at some point, why put it on in the first place?

This is a question that I routinely ask my clients. Why do you want that? “To stop developers from…”

To which I reply: “What is the cost of doing it this way? What is the cost of a mistake? What is the cost of fixing that mistake? What is the difference between that and just another bug?”

There is a reason that I choose the ALT.Net logo to be running with scissors. Power tools are here to be used. Yes, there are things that you want to hide, but those are by far the exception, not the norm.

We have previously explored the one-to-one mapping, which let you create 1:1 association in the database, but there is actually another way to map several tables to an object model. We aren’t constrained by the database model, and we can merge several tables into a single entity.

We do that using the <join/> element:

<join
        table="tablename"                        (1)
        schema="owner"                           (2)
        catalog="catalog"                        (3)
        fetch="join|select"                      (4)
        inverse="true|false"                     (5)
        optional="true|false">                   (6)

        <key ... />

        <property ... />
        ...
</join>

Let us explore this a bit, assuming the we have the following database model:"

And what we want is to map this to the following object model:

We can do this will the following mapping:

<class name="Person"
	 table="People">

	<id name="Id">
		<generator class="identity"/>
	</id>
	<property name="Name" />

	<join table="Addresses">
		<key column="PersonId"/>
		<property name="Line1"/>
		<property name="Line2"/>
		<property name="City"/>
		<property name="Country"/>
		<property name="ZipCode"/>
	</join>
</class>

And getting a Person will now result in the following SQL:

NHibernate will now take care of mapping everything to its place, and making sure that everything works just right.

By now you should be able to figure out what fetch means, and inverse should also be familiar. Optional is interesting, what is basically says is that we should always use an outer join to get the values from the Addresses table and if all the mapped properties are null, it wouldn’t create a new row in the Addresses table.

On the face of it, it looks like a nice way to merge tables together, but that isn’t actually the primary reason for this feature. You can use it for that, for sure, but that is mostly useful in legacy databases. In most cases, your object model should be more granular than the database model, not the other way around.

The really interesting part about this feature is that it allows us to mix & match inheritance strategies. It let us create a table per hierarchy that store all the extra fields on another table, for example. That significantly reduce the disadvantage of using a table per hierarchy or table per subclass, since we can tell very easily what is the type of the class that we are using, and act appropriately.

In the database world, we have three kind of associations: 1:m, m:1, m:n.

However, occasionally we want to have a one to one relationship. We could simulate it easily enough on the database side using two many to one relations, but that would require us to add the association column to both tables, and things gets… tricky when it comes the time to insert or update to the database, because of the cycle that this creates.

NHibernate solves the problem by introducing a one-to-one mapping association, which allow you to define the two relationships based on a single column in the database, which controls the two way association.

<one-to-one
        name="PropertyName"                                (1)
        class="ClassName"                                  (2)
        cascade="all|none|save-update|delete"              (3)
        constrained="true|false"                           (4)
        fetch="join|select"                                (5)
        property-ref="PropertyNameFromAssociatedClass"     (6)
        access="field|property|nosetter|ClassName"         (7)
/>

1, 2, 3, 6, 7 were all discussed elsewhere, so I’ll skip them and move directly to showing how this can be used.

We have the follow object model:

And the database model:

Note that while in the object model we have a bidirectional mapping, in the database we have only a single reference on the employees table. In the relational model, all associations are naturally bidirectional, but that is not true on the object model. In order to bridge this inconsistency, we map them as:

<class name="Employee"
		table="Employees">

	<id name="Id">
		<generator class="native"/>
	</id>
	
	<property name="Role"/>

	<many-to-one name="Person"
		unique="true"
		column="Person"/>
</class>

<class name="Person"
		table="People">

	<id name="Id">
		<generator class="native"/>
	</id>
	<property name="Name" />
	<one-to-one name="Employee"
			class="Employee"/>
</class>

We have a unique many-to-one association from Employee to Person, but a one to one from Person to Employee. This will reuse the many-to-one association defined in the Employee mapping.

Let see how this works for saving and loading the data:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var person = new Person
	{
		Name = "test",
	};
	var employee = new Employee
	{
		Person = person,
		Role = "Manager"
	};
	person.Employee = employee;
        session.Save(person);
        session.Save(employee);
	tx.Commit();
}

// person to employee
using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var employee = session.Get<Person>(1).Employee;
	Console.WriteLine(employee.Role);
	tx.Commit();
}

// employee to person
using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var person = session.Get<Employee>(1).Person;
	Console.WriteLine(person.Name);
	tx.Commit();
}

And the SQL that would be generated would be:

This is quite interesting. We can see that we insert the entities as we expect, but when we pull a person out, we do a join to the employee, to get the one-to-one association. For that matter, even in the second scenario, we do a join to get the associated employee.

The reason that we have to do it is quite interesting as well. NHibernate makes some guarantees about the way the object model and the database model map to one another. And one of those guarantees is that if there is no association in the database, we will get back a null in the object model.

Generally, this works very well, since we can tell whatever an association exists or not using the value in the table (for many-to-one associations). But for one-to-one association, if we want to keep this guarantee, we have to check the associated table to verify if we need to have a null or a proxy there. That is somewhat annoying, but we can get around that by specifying constrained=”true”. This tell NHibernate that in this case, whenever there is a Person, there must also be a matching Employee value. We can specify it like this:

<one-to-one name="Employee" 
	constrained="true"
	foreign-key="none"
	class="Employee"/>

Something else to note is that we must specify this with foreign-key=”none”, because otherwise NHibernate’s Schema Export feature would create two foreign keys for us, which would create a circular reference that wouldn’t allow us to insert anything into the database.

When setting this, we can see that there is a dramatic change in NHibernate’s behavior:

Instead of generating joins, NHibernate now uses standard selects to get the data. And we don’t have to pre-populate the information on loading the entity, we can delay that as we usually do with NHibernate.

And the last thing that we will explore for <one-to-one/> is the fetch attribute. It defaults to select, so we have already seen how that works, but when we set fetch=”join”, we get an interesting flashback. Well, almost:

Again, we use a join to get the value upfront, but since we are now using constrained=”true”, we can use an inner join instead of a left outer join, which is more efficient in most cases.