Not sure how to join tables using Fluent NHibernate
I'm using NHibernate on legacy tables and after looking through our codebase, I seem to be the only person with this need. I need to join two tables so I can run a query, but I haven't made any progress today. I'll try to abbreviate where it makes sense in my code snippets. Care to help?
Tables--
Order
OrderID (primary key)
OrderName
OrderType
OrderLocation
OrderAppendix
ID (composite key)
Key (composite key)
Value (composite key)
The ID portion of the OrderAppendix composite key is associated with OrderID in the Order table; therefore, and Order can have several entries in the OrderAppendix table.
Domain Objects--
[Serializable]
public class Order
{
public virtual string OrderID { get; set; }
...
public virtual string OrderLocation { get; set; }
}
[Serializable]
public class OrderAppendix
{
public virtual string ID { get; set; }
public virtual string Key { get; set; }
public virtual string Value { get; set; }
public override bool Equals(object obj)
{
...
}
public override int GetHashCode()
{
...
}
}
Mapping
internal sealed class OrderMap : ClassMap<Order>
{
Table("Order");
Id(x => x.OrderID).Column("OrderID").Length(20).GeneratedBy.Assigned();
Map( x => x.OrderName).Column("OrderName")
....
}
internal sealed class OrderAppendixMap : ClassMap<OrderAppendix>
{
Table("OrderAppendix");
CompositeId()
.KeyProperty(x => x.ID, "ID")
....
Map( x => x.ID).Column("ID);
...
}
I won't muddy this up with my futile attempts at joining these tables, but what I would like to do is query by things like OrderType or OrderLocation given that the results all have the same Value from the OrderAppendix table.
Example desired SQL
SELECT * FROM ORDER
INNER JOIN
ON Order.OrderID = OrderAppendix.ID
WHERE OrderAppendix.Key = "Stuff"
Edit
Here's where I've gotten by reading the documentation on "QueryOver"
http://nhibernate.info/doc/nh/en/index.html#queryqueryover
Order Order = null;
OrderAppendix OrderAppendix = null;
resultList = session.QueryOver<Order>(() => Order)
.JoinAlias(() => Order.OrderAppendix, () => OrderAppendix)
.Where(() => OrderAppendix.Key == "MatchThis")
.List();
I think I'm on the right track using aliases to join the table, but obviously I haven't found a way to inform NHibernate of the many-to-one mapping that's needed. Also, you can see that I've added a property of type OrderAppendix to Order in order to the use the alias functionality.
Well, I didn't get why you did not used standard parent-child approach using HasMany on Order side and Reference of Appendix. This is because of composite key on appendix side?
If you would do that, you will be able to provide following HQL:
SELECT o FROM Order o LEFT JOIN FETCH o.apxs a WHERE a.Key="Stuff"
上一篇: LINQ to SQL