Query with query using LINQ and NHibernate

Question

Query with query using LINQ and NHibernate

Navigation

#1 by (2 votes)

1

I need help with a query that you should look for:

PK_PARENT, PAPER_NAME, {FILE_NAME, FILE_NAME2, ..., FILE_NAME *}

For the search I've tried to search some ways like:

var listaPai = (from parent in session.Query<Parent>() select parent).ToList();

var dto = listaPai.Select(parent => new DTOListaPai
{
    Codigo = parent.Codigo,
    NomePai = parent.Nome,
    // aqui eu faço uma consulta e já monto uma string com o formato  
    // desejado
    NomesFilho = string.Join(",", session.Query<Child>()
        .Where<Child>(
            child => child.Parent.Codigo == parent.Codigo)
        .ToList())

ERROR: When the query does the validation of Where<Child>(child => child.Parent.Codigo == parent.Codigo) method GetHashCode() Child class is invoked and at the time that it tries to do Convert.ToInt32(this.Parent.Codigo) cast a null object instance exception. I think I'm wrong at this point, or maybe some missing annotation in the mappings.

Details about the implementation in the database are commented on in the code below.

Example classes:

public class Parent {
    //pk_parent == Codigo
    public virtual long Codigo { get; set; }
    public virtual string NomePai { get; set; }
    public virtual List<Child> Childs { get; set; }

    public bool Equals(object obj1)
    {
        var obj2 = obj1 as Parent;

        if (ReferenceEquals(null, obj2)) return false;
        if (ReferenceEquals(this, obj2)) return true;

        return this.Codigo == obj2.Codigo;
    }

    public int GetHashCode()
    {
        return Convert.ToInt32(this.Codigo);
    }        
}

public class Child {
    // pk_child == NomeFilho && Parent.Codigo
    public virtual string NomeFilho { get; set; }
    // fk_parent == Parent.Codigo
    public virtual Parent Parent { get; set; }

    public override bool Equals(object obj1)
    {
        var obj2 = obj1 as Child;

        if (ReferenceEquals(null, obj2)) return false;
        if (ReferenceEquals(this, obj2)) return true;

        return this.NomeFilho == obj2.NomeFilho &&
               this.Parent.Codigo == obj2.Parent.Codigo;
    }

    public override int GetHashCode()
    {
        return Convert.ToInt32(this.Parent.Codigo) + NomeFilho.GetHashCode();
    }
}

pk_child is made up of fk_parent + hostname

Mappings:

public class ParentMap : ClassMap<Parent>
{
    Table("tb_parent");
    Id(parent => parent.Codigo)
            .Not.Nullable()
            .Column("pk_parent")
            .GeneratedBy.Sequence("tb_parent_pk_parent_seq");
    Map(parent => parent.Nome).Column("nome_pai")
            .Not.Nullable();
    HasMany(parent => parent.Childs)
            .KeyColumn("fk_parent")
            .Cascade.All()
            .AsBag();
}

public class ChildMap : ClassMap<Child>
{
    Table("tb_child");
    CompositeId()
            .KeyProperty(child => child.Parent.Codigo, "fk_parent")
            .KeyProperty(child=> child.Nome, "nome_filho");

    References(child => child.Parent, "fk_parent");
}

c# nhibernate fluent-nhibernate

asked by anonymous 24.09.2015 / 21:18

1 answer

Error "source file not compiled" INNER JOIN Group Too slow in the query

score 2 · Answer 1

Friend, I do not use Fluent, pure NHibernate only with Mapping-by-code. What I do in this case is to reference everything correctly, correctly creating the "bags" so I can iterate and return all the child records of this class.

Ex:

public partial class Pessoa
{
    public virtual long IdPessoa { get; set; } // Nossa PK

    // ...Suas propriedades aqui etc, só pra exemplificar

    // Você precisa criar a classe Apelido
    public virtual IEnumerable<Apelido> ApelidoBag { get; set; }
}

My mapping class looks like this (remember that in addition to the mapping below, you also need to create the mapping class for the LastName class):

public partial class PessoaMap : ClassMapping<Pessoa>
{
    Schema("SEU_ESQUEMA");
    Lazy(true);

    // Nossa PK que é alimentada por uma sequence de exemplo (no Oracle)
    Id(x => x.IdPessoa, map =>
    {
        map.Column("ID_PESSOA");
        map.Generator(Generators.Sequence, g => g.Params(new
        {
            sequence = "SQ_EXEMPLO"
        }));
    });

    // ...Todos os itens do mapeamento do BD aqui etc, só pra exemplificar.

    Bag(x => x.ApelidoBag, colmap =>
    {
        colmap.Key(x => x.Column("ID_PESSOA"));
        colmap.Inverse(true);
    },
    map => { map.OneToMany(); });
}

In this way I can do SELECT using LINQ (remember to reference System.Linq and NHibernate.Linq in> just looking for the person (by ID, for example) and when I need to do a foreach in the bag list because automatically it will bring all the records of this "person".

Ex:

return suaSessao
    .Query<Pessoa>
    .Where(x => x.IdPessoa == 12345)
    .SingleOrDefault()
;

Because of Lazy (true); of Nhibernate, you can bring child records at any time by calling the property NicknameBag and doing it iterately.

Ex:

foreach (Apelido item in seuObjPessoa.ApelidosBag)
{
    // Faça o que tiver que fazer aqui
}