Populate DTO from several queries

Question

I have a DTO with 40+ properties. But in order to populate all properties I need to execute 4 separate queries. My first query is in charged of getting basic information. For each row returned I run 3 more queries based on the id's given from the main query (N+1 problem). I can set use eager loading but then I'm loading thousands of objects that I don't need.

Should I split my DTO and create a separate DTO for each query I run then link then tie them all together into a central DTO by id?

I was envisioning a final DTO like this.

public class FooDto
{
    public string Foo { get; set; }
    public string Bar { get; set; }
    public FirstDto FirstQueryResults { get; set; }
    public SecondDto SecondQueryResults { get; set; }
    public ThirdDto ThirdQueryResults { get; set; }
}

Is there a better way of solving this? I'm using Oracle and NHibernate doesn't support multi criterias. Note that I am joining most of my data. The problem comes when I need to query data with a complete new set of criteria.

Answer 1

How about creating a VIEW that joins the data to give the 40 properties all in one go, and basing your DTO on that - whatever a DTO may be ;-)