by Mikael Henriksson
15. December 2009 16:06
If I had it my way we would probably have the whole darn thing in one and the same table but that would not work out long term. The problem I was facing was to fetch one entity based on values in the parent and 2 other (to the parent) related entities. Then I want to eagerly fetch information from the parent.
// Create criteria and add sorting based on version
var criteria = work.Session.CreateCriteria<ContactVersion>("v")
.AddOrder(Order.Desc("v.Version"));
// Create restriction based on that the contact should:
// 1. Not be deleted and
// 2. Match the accountId
criteria.CreateCriteria("v.Contact", "c")
.Add(Restrictions.IsNull("c.DeletedAt"))
.Add(Restrictions.Eq("c.Id", accountId));
// Also add a restriction based on uid
criteria.CreateCriteria("c.Devices", "dc")
.Add(Restrictions.Eq("dc.Id", uid));
// Lastly add a restriction for the current device id and
// eagerly fetch the corresponding contact.
criteria.CreateCriteria("dc.Device", "d")
.Add(Restrictions.Eq("d.Id", deviceId))
.SetFetchMode("c.Contact", FetchMode.Join)
.SetFetchSize(1)
.SetMaxResults(1);
var c = criteria.UniqueResult<ContactVersion>();
This gives me the desired result and the query looks like follows:
SELECT top 1 * /* removed all the columns */
FROM contact_version this_
inner join contact_def c1_
on this_.contact_id = c1_.contact_id
inner join contact_device_uid dc2_
on c1_.contact_id = dc2_.contact_id
inner join device_def d3_
on dc2_.device_id = d3_.device_id
WHERE this_.contact_version_ordinal = 3 /* @p0 */
and c1_.dte_deleted is null
and c1_.subscription_id = 4 /* @p1 */
and dc2_.contact_uid = 1 /* @p2 */
and d3_.device_id = 1 /* @p3 */
A quick look at the query execution plan shows nothing really strange so I suppose I sort of got it right. At least I got the data that I asked for..
Subscribe