I had a question about how NHibernate interacts with MS Access. I recently downloaded the most recent version of NHibernate, and I am using the new NHiibernate.JetDriver.Dialect.

Before I upgraded, I was using the SQL Server dialect, and OLEDB. I was quite pleased to find out that when I queried for an object with a Many-to-one relationship, NHibernate used an outer join to populate the the entire object, only using one query.

After I upgraded to use the NHibernate.JetDriver.Dialect, I noticed that NHibernate was now using 2 queries to populate the same object. One to the populate the object, the other to populate the many-to-one relationship.

I was wondering, if this the normal for NHibernate.JetDriver.Dialect?

I have verified that the use outer join property is set to true. Also, I would go back to using the SQL Server dialect, but there was an issue populating an object with more than 1 many-to-one relationaship. The upgrade fixed it.

I may have found an answer to my question. I found this in the Nhibernate.JetDriver.Dialect code:

//although theoretically Access should support outer joins, it has some severe
//limitations on complexity of the SQL statements, so we better switch it off.
DefaultProperties[ Cfg.Environment.UseOuterJoin ] = "false";

I was wondering if there is any plan to support outer joins for the Access dialect?

I think that you should ask this question to Microsoft :wink:

Are you sure you really have the hibernate.use_outer_join property set to true in your configuration? It should force the driver to use outer joins.

If you are sure that your queries will never be more complex than the limits below, you can turn the use on, but in default settings I decided to switch it off. Not only that many of the NHibernate tests fail because of this setting, but more importantly older versions of Access have stricter limits. I may be biased, but I tend to work with complex dynamicly built queries that break these quite often.

These are the query limits for Access 2003 (taken from http://office.microsoft.com/en-us/assistance/HP051868081033.aspx and http://www.micronetservices.com/Access2kCapacities.htm):

Number of enforced relationships: 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships

Number of tables in a query: 32

Number of fields in a recordset: 255

Recordset size: 1 gigabyte

Sort limit: 255 characters in one or more fields

Number of levels of nested queries: 50

Number of characters for a parameter in a parameter query: 255

Number of ANDs in a WHERE or HAVING clause: 99 (40 for Access 2000)

Number of characters in an SQL statement: approximately 64,000