Multiple INNER JOINS with DAPPER

8

I'm trying to make multiple INNER JOIN in Dapper with these classes:

public class EventoConfiguracao
{
    public EventoConfiguracao()
    {
        //CdEventoConfiguracao = Guid.NewGuid();
     Menus = new List<Menu>();
        MenuLaterais  =new List<MenuLateral>();
        Categorias =new List<Categoria>();
        Produtos=new List<Produto>();
       CupomDescontos =new List<CupomDesconto>();
    }

    [Key]
    public int Id { get; set; }
    public string Nome { get; set; }
    public int CdEvento { get; set; }
    public virtual Evento Evento { get; set; }
    public virtual ICollection<Menu> Menus { get; set; }
    public virtual ICollection<Categoria> Categorias { get; set; }
    public virtual ICollection<Produto> Produtos { get; set; }
    public virtual ICollection<CupomDesconto> CupomDescontos { get; set; }
    public virtual ICollection<MenuLateral> MenuLaterais { get; set; }

    public bool IsValid()
    {
        return true;
    }
}
public class Menu
{

    public Menu()
    {
        SubMenus = new List<Menu>();
    }
    [Key]
    public int Id { get; set; }
    public string Nome { get; set; }
    public string RotaUrl { get; set; }
    public int? MenuTipoId { get; set; }
    public virtual MenuTipo MenuTipo { get; set; }
    public int? MenuParentId { get; set; }
    public virtual Menu MenuParente { get; set; }
    public virtual ICollection<Menu> SubMenus { get; set; }
    public int EventoConfiguracaoId { get; set; }
    public virtual EventoConfiguracao EventoConfiguracao { get; set; }
    public bool IsCarrousel { get; set; }
    public bool IsPainel { get; set; }

}
public class Produto()
{
    public Produto()
    {
        PedidoDetalhes = new List<PedidoDetalhe>();
    }
    public int Id { get; set; }
    public string NrProduto { get; set; }
    public string Nome { get; set; }
    public double Qtd { get; set; }
    public int? EventoConfiguracaoId { get; set; }
    public virtual EventoConfiguracao EventoConfiguracao { get; set; }
}
public class Categoria
{
    public Categoria()
    {
        Produtos = new List<Produto>();
    }
    public int Id { get; set; }
    public string Descricao { get; set; }
    public virtual ICollection<Produto> Produtos { get; set; }
    public int? EventoConfiguracaoId { get; set; }
    public virtual EventoConfiguracao EventoConfiguracao { get; set; }
}

When performing INNER JOIN with 2 or more tables in Dapper , this error message is appearing:

  

Additional information: When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id

while running the code below:

public IEnumerable<EventoConfiguracao> ObterProdutoPorCategoria(string categoria, int clienteId, int cdEvento)
{
        var cn = Db.Database.Connection;
        var sql = @"SELECT e.[Id] as 'EventoID', e.[Nome], p.Id as 'ProdId', p.Nome FROM EventoConfiguracao e " +
                  "INNER JOIN Categoria c " +
                  "ON  e.Id = c.[EventoConfiguracaoId] " +
                  "INNER JOIN Produto p " +
                  "ON p.CategoriaId = c.Id " +
                  "LEFT JOIN Menu m  " +
                  "ON e.Id  = m.EventoConfiguracaoId " +
                  "where c.Descricao = @scategoria AND e.CdEvento = @scdEvento " +
                "GROUP BY e.[Id], e.[Nome], p.Id, p.Nome ";

        Debug.WriteLine(sql);
        var eventoConfiguracao = new List<EventoConfiguracao>();
        cn.Query<EventoConfiguracao, Produto, Categoria,Menu, EventoConfiguracao>(sql,
            (e, p,c,m) =>
            {
                eventoConfiguracao.Add(e);
                if (e != null)
                {
                    eventoConfiguracao[0].Categorias.Add(c);
                    eventoConfiguracao[0].Produtos.Add(p);
                    eventoConfiguracao[0].Menus.Add(m);
                }

                return eventoConfiguracao.FirstOrDefault();
            }, new { scategoria =categoria,  scdEvento = cdEvento }, ***splitOn: "EventoID,ProdId,Id,m.Id");***

        return eventoConfiguracao;
}

My questions are:

  • 1st In this line of code "cn.Query" I informed 3 classes, it is also necessary to include the fields of the others (Menu and Category) together in the select?
  • 2nd SplitOn always need to provide primary key?
asked by anonymous 21.01.2017 / 03:12

1 answer

10
  

In this line of code "cn.Query" I have informed 3 classes, it is also necessary to include the fields of the others (Menu and Category) together in the select?

Needed no, but the more you complete a query , the better the information quality.

  

2nd SplitOn always need to provide primary key?

No. splitOn is a parameter that only serves for Dapper to know where the fields of a class ends and where the fields of another class begin. It does not necessarily have to be a primary key.

  

3º The order informed in the parameters of DAPPER    cn.Query<EventoConfiguracao, Produto, Categoria,Menu, EventoConfiguracao> sql, (e, p,c,m) => does SplitON have to follow the same sequence of Keys as the tables?

The order of the table fields in SQL must follow the order of the fields in the query declaration. That is, if the definition is as:

cn.Query<EventoConfiguracao, Produto, Categoria, Menu, EventoConfiguracao>(...

The order of the fields must be:

  • EventoConfiguracao ;
  • Produto ;
  • Categoria ;
  • Menu .
  • In addition, yes, it can be said that splitOn needs to follow this same order. Remembering that splitOn does not necessarily work with keys, but with any field.

    Possibly this will not work:

    splitOn: "EventoID,ProdId,Id,m.Id"
    

    There is no Id or m.Id specified between the columns. I would do something like this:

    var sql = @"SELECT e.[Id] as 'EventoID', e.[Nome], p.Id as 'ProdId', p.Nome, c.Id as CatId, c.Descricao, m.Id as MenuId, m.Nome " +
                  "FROM EventoConfiguracao e " +
                  "INNER JOIN Categoria c " +
                  "ON  e.Id = c.[EventoConfiguracaoId] " +
                  "INNER JOIN Produto p " +
                  "ON p.CategoriaId = c.Id " +
                  "LEFT JOIN Menu m  " +
                  "ON e.Id  = m.EventoConfiguracaoId " +
                  "where c.Descricao = @scategoria AND e.CdEvento = @scdEvento ";
    

    And splitOn :

    splitOn: "EventoID,ProdId,CatId,MenuId"
    

    Note that it is important that ID columns have unique names, so that Dapper does not get in the way of doing the split correctly.

        
    21.01.2017 / 06:48