Multiple INNER JOINS with DAPPER

Question

I’m trying to accomplish multiple INNER JOIN in the 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 the INNER JOIN with 2 or more tables in the Dapper, is showing this error message:

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

when executing 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 doubts are:

• 1º In this code line "cn. Query" I informed 3 classes, it is necessary to also include the fields of the others (Menu and Category) together in select?
• 2º Spliton is always necessary to inform primary key?
• 3º The order reported in the DAPPER parameters
  **cn.Query<EventoConfiguracao, Produto, Categoria,Menu, EventoConfiguracao> sql, (e, p,c,m) =>** Spliton has to follow the same sequence of Keys from the tables?

Answer 1

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

Not necessary, but the more complete one query, improve the quality of information.

2º Spliton is always necessary to inform primary key?

Not. splitOn is a parameter that only serves for the Dapper to know where the fields of one class end and where the fields of another class begin. It does not necessarily need to be primary key.

3º The order reported in the DAPPER parameters cn.Query<EventoConfiguracao, Produto, Categoria,Menu, EventoConfiguracao> sql, (e, p,c,m) => Spliton has to follow the same sequence of Keys from the tables?

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

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

The order of the fields must be:

1. EventoConfiguracao;
2. Produto;
3. Categoria;
4. Menu.

Additionally, yes, one can say that the splitOn needs to follow this same order. Remembering that splitOn does not necessarily work with keys, but with any field.

Maybe this won’t work:

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

There is no Id or m.Id specified between 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 the splitOn:

splitOn: "EventoID,ProdId,CatId,MenuId"

Note that it is important that ID columns have unique names, so that Dapper does not mess up when doing the split correctly.