NHibernate - QueryOver Child rows as comma separated values

Question

I have to write a query where for each vendor I have to read vendor's contact numbers from VendorContact but instead in rows, one row for each unique vendor and one columns for each phone number as comma separated string in main result. This can be done easily in plain SQL but I have to do it in QueryOver. I cannot declare variables in QueryOver SQL.

var vendorvar = Session.QueryOver<Vendor>(() => V)
    .Left.JoinQueryOver(() => v.ContactNumbers, () => VendorContact)
    .SelectList(list => lst
       .Select(() => v.Name)
       .Select(() => VendorContact.PhoneNumber))
    .TransformUsing(Transformers.AliasToBean<VendorModel>())
    .List<VendorModel>();

There could be more phone numbers for one vendor, so I need like

Vendor         Contacts
------         -----------------------------------------
V0001          905-2343444,416-4545454,647-8484834
V0002          905-2356219,416-4111111,647-8000004

Thanks

Answer 1

In general, if that is really so easy that way:

...This can be done easily in plain sql but...

just use that SQL statement in the raw query. There is some DTO for results

public class MyDto
{
    public virtual string FirstAlias { get; set; } // set name and type you need
    public virtual string SecondAlias { get; set; }
}

And here we do query, transform and list:

// raw SQL query
var query = session
    .CreateSQLQuery("Select " +
                    " SomColumn AS FirstAlias, " +
                    " SomComputedColumn AS SecondAlias " +
                    " FROM mySchema.MyTable" +
                    " join, where, order by..... "
                   );

// here we set transformer (check the aliases)
query.SetResultTransformer(Transformers.AliasToBean<MyDto>());

// and there is nice C# result
var list = query.List<MyDto>();