NHibernate Using a join inside a subquery: Not generating join sql statement

Question

I have a call using QueryOver that is layed out as follows and is not generating the inner join statement needed inside the subquery.

The structure of the objects that I'm querying over are as follows:

public class Product {
    public virtual long Id { get; set; }
    ... OTHER DATA MEMBERS ...
}

public class ProductListType1 {
    public virtual int Id { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual char Category { get; set; }
    ... OTHER DATA MEMBERS ...
    public virtual IDictionary<short, Detail> Details { get; set; }

    public class Detail {
        public virtual short SequenceNumber { get; set; }
        public virtual ProductListType1 ProductListType1 { get; set; } // PARENT REFERENCE FOR BIDIRECTIONAL RELATIONSHIP
        public virtual Product Product { get; set; }
        ... OTHER DATA ...
    }
}


public class ProductListType2 {
    // SAME STRUCTURE AS PRODUCT LIST TYPE 1
}


ProductListType1.Detail detailAlias = null;
Product productAlias = null;

ProductListType2 productListType2Alias = null;
ProductListType2.Detail productListType2DetailAlias = null;

_session.QueryOver<ProductListType1>()
    .Left.JoinAlias(productListType1 => productListType1.Details, () => detailAlias)
    .Left.JoinAlias(() => detailAlias.Product, () => productAlias)
    .Where(productListType1 => productListType1.Next == "abc" || productListType1.Final == "abc")
    .And(productListType1 => productListType1.Status.IsIn(new string[] { "STATUS1", "STATUS2", "STATUS3", "STATUS4"}))
    .WithSubquery.WhereNotExists(QueryOver.Of<ProductListType2.Detail>(() => productListType2DetailAlias)
        .Select(productListType2Detail => productListType2Detail.Product.id)
        .Inner.JoinAlias(productListType2Detail => productListType2Detail.ProductListType2, () => productListType2Alias)
        .Where(productListType2Detail => productListType2Detail.SequenceNumber < 900)
        .And(productListType2Detail => productListType2Detail.Product.Id == detailAlias.Product.Id)
        .And(() => productListType2Alias.Date == DateTime.Today)
        .And(() => productListType2Alias.Category == "D")
    )
    .TransformUsing(Transformers.DistinctRootEntity)
    .List();

The sql being generated when this is called is as follows.

SELECT (lots of stuff)
FROM PRODUCT_LIST_TYPE1_TABLE this_
LEFT OUTER JOIN PRODUCT_LIST_TYPE1_DETAILS_TABLE detailalia1_
    ON this_.id=detailalia1_.parentId
LEFT OUTER JOIN PRODUCT_TABLE product2_
    ON detailalia1_.productId=product2_.id
WHERE (this_.next = ? or this_.dest = ?)
AND this_.stat in (?, ?, ?, ?)
AND NOT EXISTS (
    SELECT this_0_.productId as y0_
    FROM PRODUCT_LIST_TYPE2_DETAILS_TABLE this_0_
    WHERE this_0_.sequenceNumber < ?
    AND this_0_.productId = detailalia1_.pron
    AND productlisttype2_.date = ?
    AND productlisttype2_.type = ?
); PARAMETERS

The sql generated causes an exception to be thrown:

GenericADOException: {"SQL5001 Column qualifier or table productlisttype2_ undefined."}

Can anyone tell me why the subquery .JoinAlias() call is not generating the inner join statement to the PRODUCT_LIST_TYPE2_TABLE and how I can get it to do so?

To be clear the sql I'm looking for it to generate is:

SELECT (lots of stuff)
FROM PRODUCT_LIST_TYPE1_TABLE this_
LEFT OUTER JOIN PRODUCT_LIST_TYPE1_DETAILS_TABLE detailalia1_
    ON this_.id=detailalia1_.parentId
LEFT OUTER JOIN PRODUCT_TABLE product2_
    ON detailalia1_.productId=product2_.id
WHERE (this_.next = ? or this_.dest = ?)
AND this_.stat in (?, ?, ?, ?)
AND NOT EXISTS (
    SELECT this_0_.productId as y0_
    FROM PRODUCT_LIST_TYPE2_DETAILS_TABLE this_0_
    INNER JOIN PRODUCT_LIST_TYPE2_TABLE productlisttype2_  // THIS PART IS MISSING
        ON this_0_.parentId=productlisttype2_.id
        AND productlisttype2_.date = ?
        AND productlisttype2_.type = ?
    WHERE this_0_.sequenceNumber < ?
    AND this_0_.productId = detailalia1_.pron
); PARAMETERS

or roughly equivalent inside the subquery.

Update: I have also attempted changing the:

 .Inner.JoinAlias(productListType2Detail => productListType2Detail.ProductListType2, () => productListType2Alias)

to

 .Inner.JoinAlias(() => productListType2DetailAlias.ProductListType2, () => productListType2Alias)

and tried using a join query over:

 .Inner.JoinQueryOver(productListType2Detail => productListType2DetailAlias.ProductListType2)

both changes are generating the same sql where the INNER JOIN is not present.

Answer 1

Can you try re parsing the query as follows,

ProductListType1.Detail detailAlias = null;
Product productAlias = null;

ProductListType2 productListType2Alias = null;
ProductListType2.Detail productListType2DetailAlias = null;

_session.QueryOver<ProductListType1>()
    .Left.JoinAlias(productListType1 => productListType1.Details, () => detailAlias)
    .Left.JoinAlias(() => detailAlias.Product, () => productAlias)
    .Where(productListType1 => productListType1.Next == "abc" || productListType1.Final == "abc")
    .And(productListType1 => productListType1.Status.IsIn(new string[] { "STATUS1", "STATUS2", "STATUS3", "STATUS4"}))
    .WithSubquery.WhereNotExists(QueryOver.Of<ProductListType2.Detail>(() => productListType2DetailAlias)
        .Select(productListType2Detail => productListType2Detail.Product.id)
        .Inner.JoinAlias(() => productListType2Detail.ProductListType2, () => productListType2Alias)
        .Where(productListType2Detail => productListType2Detail.SequenceNumber < 900)
        .And(productListType2Detail => productListType2Detail.Product.Id == detailAlias.Product.Id)
        .And(() => productListType2Alias.Date == DateTime.Today)
        .And(() => productListType2.Category == "D")
    )
    .TransformUsing(Transformers.DistinctRootEntity)
    .List();

Change is,

.Inner.JoinAlias(productListType2Detail => productListType2Detail.ProductListType2, () => productListType2Alias)

to

.Inner.JoinAlias(() => productListType2Detail.ProductListType2, () => productListType2Alias)