QueryOver in NHibernate with children nullable

c#, dto, nhibernate, queryover

Solution

Solution could be like this:

// these will server as fully-type representatives, and aliases
Shop shop = null;
City city = null;
MyDTO dto = null;

// shop query
var query = session.QueryOver<Shop>(() => shop);
// if needed a reference to criteria of the city
var cityPart = query.JoinQueryOver(() => shop.City // reference
    , () => city // alias
    , JoinType.LeftOuterJoin); // left join

// SELECT Clause
query.SelectList(list => list
    .Select(() => shop.Id)
        .WithAlias(() => dto.Id)
    .Select(() => shop.Name)
        .WithAlias(() => dto.Name)

    // Conditional here
    .Select(Projections.Conditional(
                Restrictions.Where(() => city.NameES== null),
                Projections.Constant("---", NHibernateUtil.String),
                Projections.Property(() => city.NameES)
        ))
        .WithAlias(() => dto.NameEs)
    );

var result = query
    .TransformUsing(Transformers.AliasToBean<MyDTO>())
    .List<MyDTO>();

Problem

I have two entity (shop and city), and I need to fill a DTO with some values of both entity: The Shop entity: ``` public class Shop { public virtual int Id {get;set;} public virtual string Name {get;set;} public virtual string Address {get;set;} public virtual int CityId {get;set;} public virtual City City {get;set;} } ``` The City entity: ``` public class City { public virtual int Id {get;set;} public virtual string NameES {get;set;} public virtual string NameEN {get;set;} public virtual string NameIT {get;set;} } ``` The DTO class: ``` public class MyDTO { public virtual int Id {get;set;} public virtual string Name {get;set;} public virtual string CityName {get;set;} } ``` I wonder if is any way to do the next SQL query with QueryOver (notice that the City child from shop can be null): ``` Sesion.CreateSQLQuery("SELECT s.id as Id, s.name as Name, IF(ISNULL(c.NameES),'---', c.NameES) as CityName from shop as s left join city c on c.Id = s.cityId").SetResultTransformer(Transformers.AliasToBean(typeof(MyDTO))) .List<MyDTO>()) ```

Original source