NHibernate QueryOver join with sub query

Question

Anybody know how to generate the following sql query using NHibernate QueryOver?

SELECT this_.Id as Id2_1_, this_.name as name2_1_, this_.rp_id as rp3_2_1_, rtlprd1_.Id as Id3_0_, rtlprd1_.name as name3_0_ FROM receipt this_ left outer join rtl_prd rtlprd1_ on this_.rp_id=rtlprd1_.Id left join ( SELECT ... )

Mapping by code:

class Receipt
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual RtlPrd RtlPrd { get; set; }
    public virtual JoinClass JoinClass { get; set; }

}
class ReceiptMap : ClassMapping<Receipt>
{
    public ReceiptMap()
    {
        Table("receipt");
        Id(x => x.Id);
        Property(x => x.Name, m => m.Column("name"));
        ManyToOne(x => x.RtlPrd, m =>
        {
            m.Column("rp_id");
            m.Cascade(Cascade.None);
        });
        ManyToOne(x => x.JoinClass, m =>
        {
            m.Column("rp_id");
            m.Cascade(Cascade.None);
        });
    }
}
class RtlPrd
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}
class RtlPrdMap : ClassMapping<RtlPrd>
{
    public RtlPrdMap()
    {
        Table("rtl_prd");
        Id(x => x.Id);
        Property(x => x.Name, m => m.Column("name"));
    }
}
class LnkPrdType
{
    public virtual int LptId { get; set; }
    public virtual string Name { get; set; }
}
class LnkPrdTypeMap : ClassMapping<LnkPrdType>
{
    public LnkPrdTypeMap()
    {
        Table("lnk_prd_type");
        Id(x => x.LptId, m => m.Column("lpt_id"));
        Property(x => x.Name, m => m.Column("name"));
    }
}
class LnkPrd
{
    public virtual int LpId { get; set; }
    public virtual string Name { get; set; }
    public virtual LnkPrdType LnkPrdType { get; set; }
    public virtual RtlPrd RtlPrd { get; set; }

}

class LnkPrdMap : ClassMapping<LnkPrd>
{
    public LnkPrdMap()
    {
        Table("lnk_prd");
        Id(x => x.LpId, m => m.Column("lp_id"));
        Property(x => x.Name, m => m.Column("name"));
        ManyToOne(x => x.LnkPrdType, m =>
        {
            m.Column("lpt_id");
            m.Cascade(Cascade.None);
        });
        ManyToOne(x => x.RtlPrd, m =>
        {
            m.Column("rpt_id");
            m.Cascade(Cascade.None);
        });
    }
}

class JoinClass
{
    public virtual int Id { get; set; }
}

class JoinClassMap : ClassMapping<JoinClass>
{
    public JoinClassMap()
    {
        // TODO: need add WHERE condtion in subselect
        Subselect(@"SELECT lp.lp_id
                    FROM lnk_prd lp JOIN lnk_prd_type lpt ON lp.lpt_id = lpt.lpt_id");

        Id(x => x.Id, m => m.Column("lp_id"));
    }
}

Sample join using query

 Receipt receipt = null;
RtlPrd rtlPrd = null;
JoinClass joinClass = null;
var query = session.QueryOver(() => receipt)
    .Left.JoinAlias(() => receipt.RtlPrd, () => rtlPrd)
    .Left.JoinAlias(() => receipt.JoinClass, () => joinClass)
    .SelectList(list =>
        list.Select(() => receipt.Id))
    .List();

Would be nice to see your mapping and what have you tried. Also, you should know, that there is NO Way how to create JOIN on *(SELECT ...)* unless this select is mapped as some Entity. Sometimes you can re-formulate that with a sub-query. These two links should help you to get some idea about this technique: [here](http://stackoverflow.com/questions/20528760/) and [here](http://stackoverflow.com/a/25531842/1679310) — Radim Köhler, Sep 27 '14 at 14:57
I need some data from subset that is created by joining of 2 another entities. It is easier if the subset is a entity. But in my case, it does not permit to do that. Do you know any way to work around for this issue? — Martinez, Sep 27 '14 at 15:09
Look, it is not so difficult. I can understand that you cannot change DB, but you can create as many C# entities as you need. Some of these could be readonly, built on top of Views (non-editable). These could be then easily used in QueryOver, if there are mapped realtions. So, I would suggest: 1) create entities, representing business model 2) mapped them using views/selects 3) map many-to-one relations among them 4) create simple QueryOver... If you will later has some issues during that process, show it here. You will surely get assistance — Radim Köhler, Sep 27 '14 at 15:12
I wonder if there is any way to create a view of 2 entities or something like that (use to join using queryover) in NHibernate without changing or creating view in DB? — Martinez, Sep 27 '14 at 15:18
I drafted some ideas how to go about your issue. Hope this help to get some idea how to use NHibernate even in this scenario.. — Radim Köhler, Sep 27 '14 at 15:33
I updated the code with mapping. Could you please take a look at it? — Martinez, Sep 27 '14 at 16:14
Not sure what to say. Your mapping does not include relations among `Rtl` and `Lnk` worlds. That means no `QueryOver`, as I tried to explain above. The only way is to create specail SQL Query or create inline view and map it to some ad hoc entity as I tried to explain below. Sorry if it did not help — Radim Köhler, Sep 27 '14 at 16:20
Thank you very much for your help. I get some idea from your subselect mapping and I did it (as updated code). — Martinez, Sep 27 '14 at 17:08
Good that my answers helped... Great to see that. Nice ;) NHibernate is powerful. Enjoy it. — Radim Köhler, Sep 27 '14 at 17:09

Radim Köhler · Answer 1 · 2014-09-27T15:45:27.460

NHibernate is ORM tool, supporting CRUD and powerful querying on top of mapped Entity model. In scenarios like this, we have to use some special approaches, which are drafted below...

One way, we always have, is to use the raw SQL Query. See:

17.1. Using an ISQLQuery or even
17.1.2. Entity queries (small cite)

The following shows how to get entity objects from a native SQL query via AddEntity():

session
   .CreateSQLQuery("SELECT * FROM CATS")
   .AddEntity(typeof(Cat));

The other option in this case, is to create new "virtual" / "readonly" entities and map them to tweaked views. NHibernate supports inline view definition, so we can manage that without any change of the underlying DB.

This could be an example:

<class name="Cat"  lazy="true" dynamic-update="true" batch-size="25" >

    <subselect>
      SELECT CatId
      , Name
      , Age
      , ParentId
      FROM [animals].[Cat]
    </subselect>

    <cache usage="read-write" ... />
    <id name="ID" column="CatId" .. />
    <many-to-one name="Parent"...  />
    ...

As we can see instead of <class name="cat" table="..." we can use <subselect> with whatever definition we need. This will act as a standard entity, and we can create any kind of relations on top of this... Exactly as we would do with native DB views.

The Content of <subselect> could be really anything. Above we see simple select from one table. But it could be pretty complex JOIN jam session...

NHibernate QueryOver join with sub query

1 Answers1