CODEWITHSUNDEEP
c#nhibernatenhibernate-criterianested-properties
Rabadash8820
Rabadash8820

Reputation: 2524

How Do I Query Objects With Nested Properties Using NHibernate?

Okay, I've seen some similar questions to this, but the answers either confused me or seemed completely over-engineered, so I'd like to ask my own question.

I have a class called Tree, which has an object property from the class Plot, which has an object property from the class Year, which has an object property from the class Series, which has a string property called Id. This is summarized below.

public class Tree {
    public virtual Plot Plot { get; set; }
    // other properties...
}

public class Plot {
    public virtual Year Year { get; set; }
    // other properties...
}

public class Year {
    public virtual Series Series { get; set; }
    // other properties...
}

public class Series {
    public virtual string Id { get; set; }
    // other properties...
}

Each of these classes corresponds to the table of a database, and properties correspond to foreign key fields (for example, the Trees table has a field called PlotKey, which refers to a record in the Plots table). All I want to do is load all trees from the database whose corresponding Series have the Id "Adrian_2012" or "IPED Sample". I thought this would be a pretty easy taking using the following code:

IList<Tree> trees = session.CreateCriteria<Tree>()
                           .Add(Expression.Or(
                               Expression.Eq("Plot.Year.Series.Id", "Adrian_2012")
                               Expression.Eq("Plot.Year.Series.Id", "IPED Sample")
                           ))
                           .List<Tree>();

But this is throwing: "NHibernate.Exceptions.GenericADOException : could not execute query". I have tried using Expression.Disjunction, I have tried using Aliases, Restrictions, and SimpleExpressions, and I know that nothing stupid like unmapped properties or misspelled criteria is occurring. The only other thing I've seen that might help is the ISession.QueryOver<>() function, but I get very confused by lambda expressions. Does anyone have a solution for me that would use just a simple CreateCriteria<> statement like that above?

Thanks in advance!

Upvotes: 0

Views: 1816

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

One not nice side of the Criteria queries is, that we have to define associations chain explicitly. I.e. we have to introduce JOIN:

You may easily specify constraints upon related entities by navigating associations using CreateCriteria().

So to have a JOIN we need syntax like this

var trees = session
     .CreateCriteria<Tree>()
     .CreateCriteria("Plot", "p")
     .CreateCriteria("Year", "y")
     .CreateCriteria("Series", "s")
     .Add(Expression.Or(
         Expression.Eq("s.Id", "Adrian_2012")
         Expression.Eq("s.Id", "IPED Sample")
     ))
     .List<Tree>();

Also, check this:

Upvotes: 2

Related Questions