NHibernate: Return a referenced property without lazy loading using SetProjection

I have the following two classes mapped:

public class Foo
{
    publi开发者_JAVA技巧c 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?

---

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>();

---

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.

---

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.