Get columns from Tables (Using Inner Join) with DetachedCriteria (prefereable) or QueryOver

Question

I'm having a problem with my query in NHibernate With DetachedCriteria (or using Session QueryOver). I need to get only a few columns from each table, like the classes as follow:

public class PanelaCorrida : BaseEntity
{
    public virtual Int64? CodCorrida { get; set; }

    public virtual Int64 NumLocal { get; set; }

    public virtual Boolean IdcInspecionada { get; set; }

    public virtual String Sigla { get; set; }

    public virtual String Rota { get; set; }

    public virtual Local Local { get; set; }

    public virtual Panela Panela { get; set; }
}


public class Panela : BaseEntity
{
    public Panela()
    {
        this.Local = new Local();
        this.PanelaCorridas = new List<PanelaCorrida>();
    }

    public virtual Int32 Numero { get; set; }

    public virtual Boolean IdcAtiva { get; set; }

    public virtual Int16 Status { get; set; }

    public virtual ICollection<PanelaCorrida> PanelaCorridas { get; set; }

    public virtual Local Local { get; set; }

    #endregion
}


public class Local : BaseEntity
{
    public Local()
    {
        this.PanelaCorridas = new List<PanelaCorrida>();
        this.Panelas = new List<Panela>();
    }

    #region Property

    public virtual Int32 IdLocal { get; set; }

    public virtual Int32 AreaLocal { get; set; }

    public virtual String Descricao { get; set; }

    public virtual String Codigo { get; set; }

    #endregion
}

Basically, the Entity 'Panela' is the Mother of 'PanelaCorrida' (Each Panela can have multiple PanelaCorrida's) but a 'PanelaCorrida' can be in one Local. But one local can have multiple PanelaCorrida's as well. It's basically, this relationship:

Panela 1 - N PanelaCorrida

Local 1 - N Panela

Local 1 - N PanelaCorrida

For this query, i need to get the Last PanelaCorrida of the db, but i need the info of Panela and Local as well.

So far, i can get all data using this NHibernate query:

To get all id's of 'panela' which is active:

var panelaIdList = Session.QueryOver<Panela>()
                    .Select(c => c.Id)
                    .Where(c => c.IdcAtiva == true)
                    .List<Int64>();

To get all last id's of 'PanelaCorrida' which is active (and the last PanelaCorrida generated):

var corridaPanelaIdList = Session.QueryOver<PanelaCorrida>()
    .Select(
    Projections.Max<PanelaCorrida>(x => x.Id),
    Projections.Group<PanelaCorrida>(x => x.Panela)
    )
    .Where(p => p.Panela.IsIn(panelaIdList.ToArray()))
    .List<Object[]>();

Now, to get the result with all info of all those tables:

With DetachedCriteria:

criteria = DetachedCriteria.For<PanelaCorrida>()
    .CreateAlias("Local", "L")
    .CreateAlias("Panela", "P")
    .Add(Restrictions.In("Id", corridaPanelaIdList.Select(x => x[0]).ToArray()));

With Session QueryOver:

var corridas = Session.QueryOver<PanelaCorrida>()
    .Where(p => p.Id.IsIn(corridaPanelaIdList.Select(x => x[0]).ToArray()))
    .List<PanelaCorrida>();

But the problem is, i need only a few columns of each Table. With NHibernate i've tried with Projections, and with QueryOver, i've tried with SelectList, but each time they generate a error (could not found the property of ...) or they do not populate the entities in result.

How i could achieve this?

Note: This is my query in first place (in SQL):

select cd.num_panela_corrida, cd.num_panela, p.numero, l.num_local from scp_panela_corrida cd  
inner join scp_panela p on p.num_panela = cd.num_panela 
inner join scp_local l on l.num_local = cd.num_local and cd.num_panela_corrida
in (
    select 
        max( c.num_panela_corrida) as num_panela_corrida 
    from 
        scp_panela_corrida c 
    inner join
        scp_panela p on p.num_panela = c.num_panela
        and p.num_panela in (
            select 
                num_panela 
            from 
                scp_panela 
            where 
                idc_ativa = 1
        ) group by c.num_panela ) order by cd.num_panela_corrida desc

But the client don't want to use a Stored Procedure or HQL.

Any help is welcome.

Check this http://stackoverflow.com/q/26893475/1679310 and you really should get your answer... — Radim Köhler, Aug 08 '16 at 05:32
Radim, this link helped me. If you want, please answer this question with the provided link and i'll accept it as the answer. Thanks for your help. — Gustavo Gonçalves, Aug 08 '16 at 21:14

score 1 · Accepted Answer · answered Aug 08 '16 at 21:14

Resolved with the following code (provided with the link of Radim Kohler).

Radim, if you want, please answer this question with the provided link and i'll accept it as the answer. Thanks for your help.

Panela panela = null;
Local local = null;

var query = session.QueryOver<PanelaCorrida>()
    .JoinAlias(c => c.Panela, () => panela)
    .Where (c => c.Id.IsIn(corridaPanelaIdList.ToArray()))
    .SelectList(list => list
    .Select(c => c.Id))
    .Select(c => c.CodCorrida)
    .Select(Projections.Property(() => panela.Id).As("Panela.Id"))
    .Select(Projections.Property(() => panela.IdcAtiva).As("Panela.IdcAtiva"))
    .TransformUsing(Transformers.AliasToBean(typeof(PanelaCorrida)));

Don't know if is the best approach, but it worked. We will analyze best approaches using QueryOver/DetachedCriteria, but for now, this works great.

Note: I've removed some columns, just to explain how it worked.

Thanks again.

Great job :) Enjoy NHibernate – Radim Köhler Aug 09 '16 at 04:10 — Radim Köhler, Aug 09 '16 at 04:10

Get columns from Tables (Using Inner Join) with DetachedCriteria (prefereable) or QueryOver

1 Answers1