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.
