Consider this contrived domain:
namespace TryHibernate.Example {
public class Computer
{
public int Id { get; set; }
public IList<Device> Devices { get; set; }
}
public class Device
{
public int Id { get; set; }
public Maker Maker { get; set; }
}
public class Maker
{
public int Id { get; set; }
public string Name { get; set; }
}
} // namespace
If I just query all computers, their devices will be randomly ordered. I'd like to have them ordered by the maker's name. I can't really do it with HasMany().OrderBy() because as far as I can tell OrderBy can only use local columns (so I can sort it by Device.Id, for example). Moreover, it would be nice to control ordering on a per-query basis, so I'm looking for a QueryOver solution.
The farthest I could get to was this:
using (ISessionFactory sessionFactory = Fluently.Configure()
.Database(SQLiteConfiguration.Standard.UsingFile("temp.sqlite").ShowSql())
.Mappings(m => m.AutoMappings.Add(
AutoMap.AssemblyOf<Computer>(new ExampleConfig())
.Conventions.Add(DefaultLazy.Never())
.Conventions.Add(DefaultCascade.All())))
.ExposeConfiguration(c => new SchemaExport(c).Create(true, true))
.BuildSessionFactory())
{
using (ISession db = sessionFactory.OpenSession())
{
Computer comp = new Computer();
comp.Devices = new List<Device>();
Device dev1 = new Device();
comp.Devices.Add(dev1);
dev1.Maker = new Maker() { Name = "IBM"};
Device dev2 = new Device();
comp.Devices.Add(dev2);
dev2.Maker = new Maker() { Name = "Acer"};
db.Persist(comp);
db.Flush();
}
using (ISession db = sessionFactory.OpenSession())
{ // This is the part I'm having trouble with:
Device devAlias = null;
Maker makerAlias = null;
IList<Computer> comps = db.QueryOver<Computer>()
.JoinAlias(c => c.Devices, () => devAlias)
.JoinAlias(() => devAlias.Maker, () => makerAlias)
.OrderBy(() => makerAlias.Name).Asc
.List();
Console.WriteLine(comps.Count);
foreach (Device dev in comps[0].Devices)
{
Console.WriteLine(dev.Maker.Name);
}
}
}
But of course it doesn't do what I want. It tries to sort the whole list by the maker's name. It succeeds, too, as I can see from the SQL, and I actually get a useless Cartesian product of computers with devices sorted by the device maker.
But then it issues another SQL query to fetch the devices, this time without sorting. I guess NHibernate has no idea that my joins were meant to fetch the children.
The question is, how can I control that second query? For example, to order devices by the maker's name, or to get each Computer.Devices list to contain only devices made by, say, IBM (if any). I guess I need a subquery for that, but where do I plug it in?
Just for completeness, here is my config:
class ExampleConfig : DefaultAutomappingConfiguration
{
public override bool ShouldMap(Type type)
{
return type.Namespace == "TryHibernate.Example";
}
}
After struggling for a while with Bozhidar's answer, I got it somewhat working. I had to do a manual mapping for the Computer, like this:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="property" auto-import="true" default-cascade="all" default-lazy="false">
<class name="TryHibernate.Example.Computer, TryHibernate" table="Computer">
<id name="Id" type="System.Int32, mscorlib" column="Id" generator="identity" />
<bag name="Devices">
<key column="ComputerId" />
<one-to-many class="TryHibernate.Example.Device, TryHibernate" />
<loader query-ref="DeviceLoader" />
</bag>
</class>
<sql-query name="DeviceLoader">
<load-collection alias="Device" role="TryHibernate.Example.Computer.Devices" />
SELECT Device.Id, Device.Maker_Id, Device.ComputerId, Maker.Name
FROM Device JOIN Maker ON (Maker.Id = Device.Maker_Id)
WHERE Device.ComputerId=?
ORDER BY Maker.Name
</sql-query>
</hibernate-mapping>
This goes into an embedded resource named Computer.hbm.xml. Then the code works just like this:
using (ISessionFactory sessionFactory = Fluently.Configure()
.Database(SQLiteConfiguration.Standard.UsingFile("temp.sqlite").ShowSql())
.Mappings(m => m.AutoMappings.Add(
AutoMap.AssemblyOf<Employee>(new ExampleConfig())
.Conventions.Add(DefaultLazy.Never())
.Conventions.Add(DefaultCascade.All())))
.Mappings(m => m.HbmMappings.AddFromAssembly(Assembly.GetExecutingAssembly()))
.ExposeConfiguration(c => new SchemaExport(c).Create(true, true))
.BuildSessionFactory())
{
using (ISession db = sessionFactory.OpenSession())
{
Computer comp = new Computer();
comp.Devices = new List<Device>();
Device dev1 = new Device();
comp.Devices.Add(dev1);
dev1.Maker = new Maker() { Name = "IBM" };
Device dev2 = new Device();
comp.Devices.Add(dev2);
dev2.Maker = new Maker() { Name = "Acer" };
db.Transaction.Begin();
db.Persist(comp);
db.Transaction.Commit();
}
using (ISession db = sessionFactory.OpenSession())
{
IList<Computer> comps = db.QueryOver<Computer>().List();
Console.WriteLine(comps.Count);
foreach (Device dev in comps[0].Devices)
{
Console.WriteLine(dev.Maker.Name);
}
}
}
However, I can't say I'm happy with this. First, it seems to be too much work to just order something, which SQL supports out-of-the box. And it can't be customized at query level, which kind of defeats the purpose of my question. Then, the bag tag makes me feel rather uneasy. It means an unordered collection. Sure, it seems to preserve the order, but are there any guarantees? And stupid NHibernate just doesn't allow to use list there because, you see, it won't be a “true” mapping. You'll need a silly “index” column to get a true list!
All this just to sort something trivial. Makes me think why things like jOOQ come to life. .Net needs something like that (nOOQ anyone?), desperately. Just use typesafe embedded SQL with a code generator and automatic conversion to/from POCOs.