code-zoop code-zoop - 2 months ago 11
C# Question

NHibernate query looking for the related object's related object

I have an nHibernate query issue that looks quite straight forward, but I can't seem to get my head around it!

I am creating some simple example classes to illustrate my problem:

public class Car {
public int Id { get; set; }
public IList<Interior> InteriorParts { get; set; }
}

public class Interior {
public int Id { get; set; }
public InteriorProducer Producer { get; set; }
}

public class InteriorProducer {
public int Id { get; set; }
}


Now to the query: I have the id of the InteriorProducer, but need to get a list of Cars where the interior have been produced by the interior producer.

So in a simple, pseudo SQL, it looks something like this:

select cars
where car.InteriorParts.Producer.Id = Id


I have a really hard time getting my head around this to create an nHibernate query.

Any Ideas?

Thanks

Answer
var cars = session
    .CreateCriteria<Car>()
    .CreateAlias("InteriorParts", "parts")
    .CreateAlias("parts.Producer", "producer")
    .Add(Expression.Eq("producer.Id", id))
    .List();

Depending on your case you might also want to filter duplicate cars that will be fetched due to the SQL joins:

.SetResultTransformer(Transformers.DistinctRootEntity)
Comments