NHibernate Mapping and Querying Where Tables are Related But No Foreign Key Constraint
- by IanT8
I'm fairly new to NHibernate, and I need to ask a couple of questions relating to a very frequent scenario. The following simplified example illustrates the problem.
I have two tables named Equipment and Users. Users is a set of system administrators. Equipment is a set of machinery.
Tables:
Users table has UserId int and LoginName nvarchar(64).
Equipment table has EquipId int, EquipType nvarchar(64), UpdatedBy int.
Behavior:
System administrators can make changes to Equipment, and when they do, the UpdatedBy field of Equipment is "normally" set to their User Id.
Users can be deleted at any time.
New Equipment items have an UpdatedBy value of null.
There's no foreign key constraint on Equipment.UpdatedBy which means:
Equipment.UpdatedBy can be null.
Equipment.UpdatedBy value can be = existing User.UserId value
Equipment.UpdatedBy value can be = non-existent User.UserId value
To find Equipment and who last updated the Equipment, I might query like this:
select E.EquipId, E.EquipName, U.UserId, U.LoginName
from Equipment E
left outer join Users U on. E.UpdatedBy = U.UserId
Simple enough.
So how to do that in NHibernate?
My mappings might be as follows:
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Data"
assembly="Data">
<class name="User" table="Users">
<id name="Id" column="UserId" unsaved-value="0">
<generator class="native" />
</id>
<property name="LoginName" unique="true" not-null="true" />
</class>
<class name="Equipment" table="Equipment">
<id name="Id" column="EquipId" type="int" unsaved-value="0">
<generator class="native" />
</id>
<property name="EquipType" />
<many-to-one name="UpdatedBy" class="User" column="UpdatedBy" />
</class>
</hibernate-mapping>
So how do I get all items of equipment and who updated them?
using (ISession session = sessionManager.OpenSession())
{
List<Data.Equipment> equipList =
session
.CreateCriteria<Data.Equipment>()
// Do I need to SetFetchmode or specify that I
// want to join onto User here? If so how?
.List<Data.Equipment>();
foreach (Data.Equipment item in equipList)
{
Debug.WriteLine("\nEquip Id: " + item.Id);
Debug.WriteLine("Equip Type: " + item.EquipType);
if (item.UpdatedBy.Country != null)
Debug.WriteLine("Updated By: " + item.UpdatedBy.LoginName);
else
Debug.WriteLine("Updated by: Nobody");
}
}
When Equipment.UpdatedBy = 3 and there is no Users.UserId = 3, the above fail
I also have a feeling that the generated SQL is a select all from Equipment followed by many select columns from Users where UserId = n whereas I'd expected NHibernate to left join as per my plain ordinary SQL and do one hit. If I can tell NHibernate to do the query in one hit, how do I do that?
Time is of the essence on my project, so any help you could provide is gratefully received. If you're speculating about how NHibernate might work in this scenario, please say you're not absolutely sure. Many thanks.
