子查询上具有复合id的子-父查询正在执行不必要的多个查询

Question

我对这个上下文有一个非常奇怪的问题。

这是父对象（订单标题，IEntity是一个空接口）：

public class OrderEntity : IEntity
{
    public virtual int prg_ordine { get; set; }
    public virtual int num_anno { get; set; }
    public virtual int num_doc { get; set; }
    public virtual String num_doc_esteso { get; set; }
    public virtual DateTime? dat_doc { get; set; }
    public virtual String cod_clifor_c { get; set; }
    public virtual String rag_soc { get; set; }
    public virtual DateTime? dat_evasione { get; set; }
    public virtual decimal qta_peso_lordo_kg { get; set; }
    public virtual decimal qta_peso_netto_kg { get; set; }
    public virtual decimal qta_totale { get; set; }
    public virtual String ind_sped { get; set; }
    public virtual String ind_sped_div { get; set; }
    public virtual String rag_soc_div { get; set; }
    public virtual String cod_agente { get; set; }
    public virtual String des_agente { get; set; }
    public virtual String cod_pag { get; set; }
    public virtual String des_pag { get; set; }
    public virtual String ind_stato_evas { get; set; }
    public virtual int cod_commessa_num { get; set; }
    public virtual String des_num_esterno { get; set; }
    public virtual ClientEntity client { get; set; }
    public virtual ICollection<OrderItemEntity> orderItems { get; set; }
}

这是子对象（订单行）：

public class OrderItemEntity  : IEntity
{
    public virtual int prg_ordine  { get; set; }
    public virtual int prg_ordine_riga { get; set; }
    public virtual String cod_art_completo { get; set; }
    public virtual String cod_clifor_c { get; set; }
    public virtual String rag_soc { get; set; }
    public virtual int num_doc { get; set; }
    public virtual String num_doc_esteso { get; set; }
    public virtual DateTime? dat_doc { get; set; }
    public virtual String cod_art { get; set; }
    public virtual String tipo_art { get; set; }
    public virtual int prg_art { get; set; }
    public virtual String ind_tiporiga { get; set; }
    public virtual String cod_um_doc { get; set; }
    public virtual String des_articolo_riga { get; set; }
    public virtual decimal qta_daevadere { get; set; }
    public virtual decimal qta_evasa { get; set; }
    public virtual decimal qta_ordine { get; set; }
    public virtual DateTime? dat_evasione { get; set; }
    public virtual DateTime? dat_evas_riga { get; set; }
    public virtual String note { get; set; }
    public virtual bool gestisci_pezzatura { get; set; }
    public virtual int numero_pezzi { get; set; }
    public virtual decimal quantita_pezzo { get; set; }
    public virtual string commessa { get; set; }
    public virtual int num_riga { get; set; }
    public virtual ArticoloDisponibilitaEntity articoloDisponibilita { get; set; }
    public virtual OrderEntity order { get; set; }
    public override bool Equals(object obj)
    {
        return base.Equals(obj);
    }
    public override int GetHashCode()
    {
        return base.GetHashCode();
    }
}

这是另外两个参考对象：

public class ClientEntity : IEntity
{
    public virtual String cod_clifor { get; set; }
    public virtual String des_ragsoc { get; set; }
    public virtual String indirizzo { get; set; }
    public virtual String codice_e_descrizione
    {
        get
        {
            return cod_clifor + " - " + des_ragsoc;
        }
    }
    public virtual ICollection<OrderEntity> orders { get; set; }
}
public class ArticoloDisponibilitaEntity :IEntity
{
    public virtual String cod_art_completo { get; set; }
    public virtual decimal qta_esistente { get; set; }
    public virtual decimal qta_in_distinte_lavorazione { get; set; }
    public virtual decimal qta_in_distinte_aperte { get; set; }
    public virtual decimal qta_libera_alla_vendita()
    {
        var ret = qta_esistente - qta_in_distinte_aperte - qta_in_distinte_lavorazione;
        if(ret < 0)
            ret = 0;
        return ret;
    }
}

以下是Fluent nHibernate映射类：

public class OrderEntityMap : ClassMap<OrderEntity>
{
    public OrderEntityMap()
    {
        this.Id(x => x.prg_ordine);
        this.Map(x => x.num_anno);
        this.Map(x => x.num_doc);
        this.Map(x => x.num_doc_esteso);
        this.Map(x => x.dat_doc);
        this.Map(x => x.cod_clifor_c);
        this.Map(x => x.rag_soc);
        this.Map(x => x.dat_evasione);
        this.Map(x => x.qta_peso_lordo_kg);
        this.Map(x => x.qta_peso_netto_kg);
        this.Map(x => x.qta_totale);
        this.Map(x => x.ind_sped);
        this.Map(x => x.ind_sped_div);
        this.Map(x => x.rag_soc_div);
        this.Map(x => x.cod_agente);
        this.Map(x => x.des_agente);
        this.Map(x => x.cod_pag);
        this.Map(x => x.des_pag);
        this.Map(x => x.ind_stato_evas);
        this.Map(x => x.cod_commessa_num);
        this.Map(x => x.des_num_esterno);
        References<ClientEntity>(x => x.client, "cod_clifor_c").Not.Nullable();
        this.HasMany<OrderItemEntity>(x => x.orderItems).KeyColumn("prg_ordine").Not.LazyLoad().Cascade.All();
        this.Table("VW_E_OrdiniTestate");
        this.ReadOnly();
    }
}
public class OrderItemEntityMap : ClassMap<OrderItemEntity>
{
    public OrderItemEntityMap()
    {
        this.CompositeId()
            .KeyProperty(x => x.prg_ordine, "prg_ordine_riga")
            .KeyReference(x => x.order, "prg_ordine");
        this.Map(x => x.prg_ordine  );
        this.Map(x => x.prg_ordine_riga );
        this.Map(x => x.cod_art_completo );
        this.Map(x => x.cod_clifor_c );
        this.Map(x => x.rag_soc );
        this.Map(x => x.num_doc );
        this.Map(x => x.num_doc_esteso );
        this.Map(x => x.dat_doc );
        this.Map(x => x.cod_art );
        this.Map(x => x.tipo_art );
        this.Map(x => x.prg_art );
        this.Map(x => x.ind_tiporiga );
        this.Map(x => x.cod_um_doc );
        this.Map(x => x.des_articolo_riga );
        this.Map(x => x.qta_daevadere );
        this.Map(x => x.qta_evasa );
        this.Map(x => x.qta_ordine );
        this.Map(x => x.dat_evasione );
        this.Map(x => x.dat_evas_riga );
        this.Map(x => x.note );
        this.Map(x => x.gestisci_pezzatura );
        this.Map(x => x.numero_pezzi );
        this.Map(x => x.quantita_pezzo);
        this.Map(x => x.commessa);
        this.Map(x => x.num_riga);
        References<ArticoloDisponibilitaEntity>(x => x.articoloDisponibilita, "cod_art_completo").Not.Nullable();
        References<OrderEntity>(x => x.order, "prg_ordine").Not.Nullable();
        this.Table("VW_E_OrdiniRighe");
        this.ReadOnly();
    }
}
public class ClientEntityMap : ClassMap<ClientEntity>
{
    public ClientEntityMap()
    {
        this.Table("VW_E_Clienti");
        this.Id(x => x.cod_clifor);
        this.Map(x=> x.des_ragsoc);
        this.Map(x => x.indirizzo);
        this.HasMany(x => x.orders).KeyColumn("cod_clifor_c");
    }
}
public class ArticoloDisponibilitaEntityMap : ClassMap<ArticoloDisponibilitaEntity>
{
    public ArticoloDisponibilitaEntityMap()
    {
        this.Table("VW_E_Articoli_Disp");
        this.Id(x => x.cod_art_completo);
        this.Map(x => x.qta_esistente);
        this.Map(x => x.qta_in_distinte_aperte);
        this.Map(x => x.qta_in_distinte_lavorazione);
        this.ReadOnly();
    }
}

此查询：

        return All().FetchMany(x => x.orderItems).Fetch(x => x.client).Where(x => x.cod_clifor_c == filters.clientId).ToList();

导致执行此查询：

select orderentit0_.prg_ordine         as prg1_11_0_,
       orderitems1_.prg_ordine_riga    as prg1_12_1_,
       orderitems1_.prg_ordine         as prg2_12_1_,
       cliententi2_.cod_clifor         as cod1_3_2_,
       orderentit0_.num_anno           as num2_11_0_,
       orderentit0_.num_doc            as num3_11_0_,
       orderentit0_.num_doc_esteso     as num4_11_0_,
       orderentit0_.dat_doc            as dat5_11_0_,
       orderentit0_.cod_clifor_c       as cod6_11_0_,
       orderentit0_.rag_soc            as rag7_11_0_,
       orderentit0_.dat_evasione       as dat8_11_0_,
       orderentit0_.qta_peso_lordo_kg  as qta9_11_0_,
       orderentit0_.qta_peso_netto_kg  as qta10_11_0_,
       orderentit0_.qta_totale         as qta11_11_0_,
       orderentit0_.ind_sped           as ind12_11_0_,
       orderentit0_.ind_sped_div       as ind13_11_0_,
       orderentit0_.rag_soc_div        as rag14_11_0_,
       orderentit0_.cod_agente         as cod15_11_0_,
       orderentit0_.des_agente         as des16_11_0_,
       orderentit0_.cod_pag            as cod17_11_0_,
       orderentit0_.des_pag            as des18_11_0_,
       orderentit0_.ind_stato_evas     as ind19_11_0_,
       orderentit0_.cod_commessa_num   as cod20_11_0_,
       orderentit0_.des_num_esterno    as des21_11_0_,
       orderitems1_.cod_art_completo   as cod3_12_1_,
       orderitems1_.cod_clifor_c       as cod4_12_1_,
       orderitems1_.rag_soc            as rag5_12_1_,
       orderitems1_.num_doc            as num6_12_1_,
       orderitems1_.num_doc_esteso     as num7_12_1_,
       orderitems1_.dat_doc            as dat8_12_1_,
       orderitems1_.cod_art            as cod9_12_1_,
       orderitems1_.tipo_art           as tipo10_12_1_,
       orderitems1_.prg_art            as prg11_12_1_,
       orderitems1_.ind_tiporiga       as ind12_12_1_,
       orderitems1_.cod_um_doc         as cod13_12_1_,
       orderitems1_.des_articolo_riga  as des14_12_1_,
       orderitems1_.qta_daevadere      as qta15_12_1_,
       orderitems1_.qta_evasa          as qta16_12_1_,
       orderitems1_.qta_ordine         as qta17_12_1_,
       orderitems1_.dat_evasione       as dat18_12_1_,
       orderitems1_.dat_evas_riga      as dat19_12_1_,
       orderitems1_.note               as note12_1_,
       orderitems1_.gestisci_pezzatura as gestisci21_12_1_,
       orderitems1_.numero_pezzi       as numero22_12_1_,
       orderitems1_.quantita_pezzo     as quantita23_12_1_,
       orderitems1_.commessa           as commessa12_1_,
       orderitems1_.num_riga           as num25_12_1_,
       orderitems1_.prg_ordine         as prg2_0__,
       orderitems1_.prg_ordine_riga    as prg1_0__,
       cliententi2_.des_ragsoc         as des2_3_2_,
       cliententi2_.indirizzo          as indirizzo3_2_
    from   VW_E_OrdiniTestate orderentit0_
       left outer join VW_E_OrdiniRighe orderitems1_
         on orderentit0_.prg_ordine = orderitems1_.prg_ordine
       left outer join VW_E_Clienti cliententi2_
         on orderentit0_.cod_clifor_c = cliententi2_.cod_clifor
where  orderentit0_.cod_clifor_c = '000030' /* @p0 */

这是非常好的。

问题是，在上述查询之后，将执行一个查询PER ORDER ROW，如下所示：

SELECT orderiteme0_.prg_ordine_riga    as prg1_12_0_,
       orderiteme0_.prg_ordine         as prg2_12_0_,
       orderiteme0_.cod_art_completo   as cod3_12_0_,
       orderiteme0_.cod_clifor_c       as cod4_12_0_,
       orderiteme0_.rag_soc            as rag5_12_0_,
       orderiteme0_.num_doc            as num6_12_0_,
       orderiteme0_.num_doc_esteso     as num7_12_0_,
       orderiteme0_.dat_doc            as dat8_12_0_,
       orderiteme0_.cod_art            as cod9_12_0_,
       orderiteme0_.tipo_art           as tipo10_12_0_,
       orderiteme0_.prg_art            as prg11_12_0_,
       orderiteme0_.ind_tiporiga       as ind12_12_0_,
       orderiteme0_.cod_um_doc         as cod13_12_0_,
       orderiteme0_.des_articolo_riga  as des14_12_0_,
       orderiteme0_.qta_daevadere      as qta15_12_0_,
       orderiteme0_.qta_evasa          as qta16_12_0_,
       orderiteme0_.qta_ordine         as qta17_12_0_,
       orderiteme0_.dat_evasione       as dat18_12_0_,
       orderiteme0_.dat_evas_riga      as dat19_12_0_,
       orderiteme0_.note               as note12_0_,
       orderiteme0_.gestisci_pezzatura as gestisci21_12_0_,
       orderiteme0_.numero_pezzi       as numero22_12_0_,
       orderiteme0_.quantita_pezzo     as quantita23_12_0_,
       orderiteme0_.commessa           as commessa12_0_,
       orderiteme0_.num_riga           as num25_12_0_
FROM   VW_E_OrdiniRighe orderiteme0_
WHERE  orderiteme0_.prg_ordine_riga = 1 /* @p0 */
       and orderiteme0_.prg_ordine = 22 /* @p1 */

这是出乎意料的，也是不必要的；我怀疑映射中有问题，但对我来说似乎一切都很好。关于为什么会发生这种情况以及如何避免这种情况，有什么提示吗？

谢谢，马里奥

Answer 1

我认为你的问题是在子实体中实现GetHashCode方法，使用Nhibernate，当你有一个复合id时，你必须告诉Nh如何重新配置实体。

尝试实现这两种方法：

  public override bool Equals(object obj)
    {
        OrderItemEntity objfrom = (OrderItemEntity)obj;
        return ((this.prg_ordine == objfrom.prg_ordine) && (this.prg_ordine_riga == objfrom.prg_ordine_riga));
    }
    public override int GetHashCode()
    {
        unchecked
        {
            return ((this.prg_ordine * 100000) + this.prg_ordine_riga);
        }

再见Marco