Query many-to-many without selecting all objects using Criteria API

Question

I have a many-to-many relationship between Project and Site. I am trying to retrieve a list of Sites for a project using the Criteria API. I've got this working but the query also selects all of the columns for the associated Projects, which I don't want. I wrote what I thought was an equivalent query using HQL and it only selects the Site columns.

var target1 = session.CreateQuery("select s from Site s join s.Projects pr where pr.ProjectId = ?")
    .SetInt32(0, projectId)
    .List<Site>();

var target2 = session.CreateCriteria<Site>()
    .CreateAlias("Projects", "pr")
    .Add(Restrictions.Eq("pr.ProjectId", projectId))
    .List<Site>();

How can I limit the Criteria API version (target2) to select only the Site columns? I tried using Projections but there's no method to project a type. I have to use Criteria API in this case.

Answer 1

I'm not sure if this is the best way, but I got it to work using SqlProjection:

Session.CreateCriteria<T>("foo")
            .CreateAlias("foo.Bar", "bar")
            .SetProjection(Projections.SqlProjection("{alias}.*", new string[] {}, new IType[] {}))
            .SetResultTransformer(new AliasToBeanResultTransformer(typeof(Foo)))
            .List<Foo>();

This produces the following SQL:

SELECT this_.* FROM Foo this_ inner join Bar b1_ on this_.BarId=b1_.Id