NHibernate child collection Projection to DTO

Question

I have had a good look around for answers on this, and several questions suggest this cannot be done.

Nhibernate projection with child collection

NHibernate QueryOver projections - projecting collections to DTO

NHibernate Projections - how to project collections

So I was wondering what would be a good work around to project a child collection to my DTO. Will I need to run two seperate projections and manually add the children to the parent?

I am using NH 3.3.1 I have the following DTO data structure

 public class ProviderDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<EmployerDTO> Employers { get; set; }
}


 public class EmployerDTO
{
    public int Id { get; set; }
    public string Name { get; set; }

}

I would like to map my Provider and employer domain entities to this structure with a projection. (In this simple scenario my domain entities are very similar to my dto's)

Employer employerAlias = null;
Provider providerAlias = null;
ProviderDto providerDto = null;

var dto = session.QueryOver<Provider>(() => providerAlias)
            .JoinAlias(x => x.Employers, () => employerAlias)
            .Where(()=> providerAlias.Id == 1)
            .Select(Projections.ProjectionList()
            .Add(Projections.Property(() => providerAlias.Id).WithAlias(() => providerDto.Id))
            .Add(Projections.Property(() => providerAlias.Name).WithAlias(() => providerDto.Name))

              //This is where I cannot project my child employer collection
            .TransformUsing(Transformers.AliasToBean<ProviderDto>()).List<ProviderDto>();

How could I map across the child collection? Thanks very much.

Paul Connolly · Accepted Answer · 2013-06-25T11:56:06.913

2

You should batch queries using futures and construct the relationship yourself.

Employer employerAlias = null;
Provider providerAlias = null;
ProviderDto providerDto = null;
EmployerDto employerDto = null;

var providers = session.QueryOver<Provider>(() => providerAlias)
        .JoinAlias(x => x.Employers, () => employerAlias)
        .Where(()=> providerAlias.Id == 1)
        .Select(Projections.ProjectionList()
        .Add(Projections.Property(() => providerAlias.Id).WithAlias(() =>    providerDto.Id))
        .Add(Projections.Property(() => providerAlias.Name).WithAlias(() => providerDto.Name))
        .TransformUsing(Transformers.AliasToBean<ProviderDto>()).Future<ProviderDto>();

var employers = session.QueryOver<Employer>(() => employerAlias)
//Etc 
.TransformUsing(Transformers.AliasToBean<EmployerDto>()).Future<EmployerDto>();

Then it's a matter of mapping the correct employers to providers. You might need more information in the DTO for that, but using Future queries, you will only make one database roundtrip to get all the information.

EDIT:

In order to get which employers map to which providers.

session.QueryOver(() => provider)
       .JoinAlias(() => provider.Employers, () => employer)
       .SelectList(list => list.Select(() => provider.Id).WithAlias(() =>     peDTO.ProviderId)
                               .Select(() => employer.Id).WithAlias(() =>     peDTO.EmployerId))
       .TransformUsing(Transformers.AliasToBean<ProviderEmployerMapDTO>()).Future<ProviderEmployerMapDTO>();

edited Jun 25 '13 at 11:56

answered Jun 24 '13 at 19:53

Paul Connolly

359
2
8

great. just going to try this. be back in a minute. – jonho Jun 25 '13 at 08:49
can I just ask. In the second (employer) projection what would be the best way to approach the "where" clause. obviously i need to retrieve all employers associated to the provider I have just batched. but there is only a one way relationship between provider to employer. thanks – jonho Jun 25 '13 at 09:04
That's why I said the DTO might need to be modified. Can you place the ProviderId on the employer? Or is it a many to many? If it's many to many, you can write a projection of a list of ProviderId:EmployerId as a third future query (Add another DTO called ProviderEmployerDTO). Keep in mind that you want to use the same restrictions on all queries. ie: ProviderId == 1. – Paul Connolly Jun 25 '13 at 09:15
It is one to many. Ideally I would prefer not to have to add a provider reference to the employer. Is there any way to do the projection whilst I am still in the context of the first projection? thanks – jonho Jun 25 '13 at 09:55
maybe a sub select after the first projection? or something similar (not sure if this is possible!) – jonho Jun 25 '13 at 09:56
There isn't, because it's a sublist, at best you would cause duplicates. I see no reason why the DTO can't handle this for you, a DTO is really a throw away class used for transferring data, it shouldn't be used as a highlevel domain class. It's ideal that you modify it to handle this case. Or, you can create another DTO which just holds the relationships. Classes are cheap. – Paul Connolly Jun 25 '13 at 10:01
I don't mind the dto having extra references. I agree that is throw away. I just didn't want to add a provider reference to the employer domain model. – jonho Jun 25 '13 at 11:36
The only option then is to create a dto which holds the mapping ids. I've updated the answer – Paul Connolly Jun 25 '13 at 11:54

NHibernate child collection Projection to DTO

1 Answers1

Linked