CODEWITHSUNDEEP
nhibernate
cbp
cbp

Reputation: 25628

NHibernate: Return a referenced property without lazy loading using SetProjection

I have the following two classes mapped:

public class Foo
{
    public virtual Guid Id { get; set; }
    public virtual Bar Bar { get; set; }
}
public class Bar
{
    public virtual Guid Id { get; set; }
    public virtual string Name { get; set; }
}

I have the following Criteria:

return Session.CreateCriteria<Foo>("f")
    .CreateAlias("f.Bar", "b")
    .SetProjection(Projections.Property("f.Bar"))
    .List<Bar>();

This generates the following SQL:

select b.Id from Foo f
inner join Bar on f.BarId = b.Id

Notice how only the Id of Bar is returned, rather than the entire class. How do I get all the columns of Bar instead?

Upvotes: 2

Views: 694

Answers (3)

Baz1nga
Baz1nga

Reputation: 15579

If the above detached criteria query doesnt work out here is a simple HQL query that should do the trick.

var hqlQuery="select b from Foo as f inner join f.Bar as b";

Now run this query as follows:

Session.CreateQuery(hqlQuery).List<Boo>();

you can now add where condition to your query too if you want.

Hope this helps.. I can tell you how to do it with Criteria but I think this is a little easier for you to ustand as u seem to be comfortable with SQL.

Upvotes: 0

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

If it doesn't fit into a single criteria, use DetachedCriteria:

var subquery = DetachedCriteria.For<Foo>("f")
    .SetProjection(Projections.Property("f.Bar"))
    // more filter criteria ...

return session.CreateCriteria<Bar>
    .SetProjection(Subqueries.PropertyIn("id", subquery));

which creates sql like this:

select Bar.*
from Bar
where Bar.id in (Select b.id from Foo f inner join Bar b on ...)

Is obviously only make sense if you have some filter criteria base on Foo in the subquery.

Upvotes: 0

Filip Zawada
Filip Zawada

Reputation: 824

The solution depends on your needs.

First of all if you need to return entity Bar, then your initial Criteria must be of type Bar so you just:

session.CreateCriteria<Bar>().
       .List<Bar();

If you need to add some restrictions based on Foo then there are two ways.

  1. Use HQL

    session.CreateQuery( 

        "select b " +
    "from Foo f " +
    "inner join f.Bar b " +
    "where f.Id = 9 ")
.List();

  2. Use query only property. Do this by adding access="noop" in your Bar mapping file.

    <many-to-one name="foo" access="noop" class="Foo" column="FooFk"/>

Note that your domain model doesn't have to change! You don't need to add that "foo" property/field in the Bar class. Now you can use that property in your queries like:

session.CreateCriteria<Bar>()
    .CreateAlias("foo", "f")
    .Add(Restrictions.Eq("f.Id", 9))
    .List<Bar>();

Upvotes: 2

Related Questions