QueryOver: select columns from subquery

Question

How can I select / project values from a subquery from a different table into my main query?

I have an NH-model like this:

[Serializable]
public class MyModel
{
    public virtual int Id {get; set;}
    //more mapped values
    ....
    //unmapped values
    public virtual string ValueFromOtherTable {get;set;}
}

And I want to fill ValueFromOtherTable with a left join like this:

Select mt.*, ..., ot.ValueFromOtherTable from MyModelTable mt left 
join OtherTable ot ON (somecondition)

where MyModelTable is the table mapped to MyModel-class. I want to fill ValueFromOtherTable (no NH-mapping) by selecting all values from mt (to fill the NH-mapped columns) and then by using OtherTable I want to fill ValueFromOtherTable.

I can´t join both tables via QueryOver as there exists no direct parent-child correlation in the model, so JoinAlias or JoinQueryOver won´t work. My MainQueryOver queries MyModelTable.

ALTERNATIVE:

The alternative is to first get all values from MyModelTable and then using the properties there to query OtherTable. However this will result in an SELECT N+1 problem (for each model from MyModel select some OtherTable...) and also makes the code very complicated.

Is there a good way to solve this problem or is the only way to fill MyModel by using described alternative ?

Answer 1

One way would be to use Projections, Subquery and DTO. So let's say, that we have DTO (almost the same as MyModel, but with new extern property ... e.g. Count). Then we can do it like this:

MyModel main = null;
MyModelDTO dto = null;

// the main query
var query = session.QueryOver<MyModel>(() => main);

// the subquery used for projection
var subquery = QueryOver.Of<OtherModel>()
    // select something, e.g. count of the ID
    .SelectList(selectGroup => selectGroup.SelectCount(o => o.ID))
    // some condition
    // kind of JOIN inside of the subquery
    .Where(o => o.xxx == main.yyy); // just example

// now select the properties from main MyModel and one from the subquery
query.SelectList(sl => sl
      .SelectSubQuery(subquery)
         .WithAlias(() => dto.Count)
      .Select(() => main.ID)
        .WithAlias(() => dto .ID)
      ....
    );

// we have to use transformer
query.TransformUsing(Transformers.AliasToBean<MyModelDTO >())

// and we can get a list of DTO
var list = query.List<MyModelDTO>();