CODEWITHSUNDEEP
c#.netnhibernate
Thiago Padilha
Thiago Padilha

Reputation: 4650

NHibernate - How to use projections with associations paths to limit the result set?

I have created a Fluent NHibernate convention to set 'Join' as the fetch mode for all many to one associations(In my opinion, no point in sending selects to fetch 1 record each). The problem is that some entities have a huge amount of columns(legacy database) and I need to limit the result set for the report I'm creating.

Consider the following example :

class X 
{
  public int Id {get;set;}
  public virtual Y RefToY {get;set;}
  ...
}

class Y
{
  public int Id {get;set;}
  public virtual Z RefToZ {get;set;}
  ...
}

class Z
{
  public int Id {get;set;}
  public virtual String data1 {get;set;}
  public virtual String data2 {get;set;}
  public virtual String data3 {get;set;}
  ...
}

Imagine that besides the properties I showed, each of these entities have 200 more properties, each mapped to a column.

Let's say the report I need to create is based on X(meaning the criteria is rooted on X), and I need to fetch X's 'Id' property and Z's 'data1', 'data2', and 'data3' property to show on a DataGrid. There's no point in fetching a resultset with 600 columns so I need to specify using the Criteria API that I only need those 4 columns. How can I quickly acomplish that? I have tried setting the projections like this :

var crit = Session.CreateCriteria<X>().SetProjection(Projections.ProjectionList()
.Add(Projections.Property("Id"))
.Add(Projections.Property("RefToY.RefToZ.data1"))
.Add(Projections.Property("RefToY.RefToZ.data2"))
.Add(Projections.Property("RefToY.RefToZ.data3")));

But this resulted in errors saying : "RefToY.RefToZ.data1" ... properties didn't exist. This resulted in my question. How can I acomplish this using NHibernate criteria API?

Upvotes: 0

Views: 1505

Answers (1)

Daniel Schilling
Daniel Schilling

Reputation: 4977

You need to add a join to the criteria. Give this a try...

var crit = Session.CreateCriteria<X>()
    .CreateAlias("RefToY", "y")
    .CreateAlias("y.RefToZ", "z")
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Property("Id"))
        .Add(Projections.Property("z.data1"))
        .Add(Projections.Property("z.data2"))
        .Add(Projections.Property("z.data3")));

Upvotes: 4

Related Questions