How to join two tables using auxiliary table and LINQ

Question

I have the tables People and Representatives, where in my application the person may or may not have representative(s).

public  class Conta
{
    int PessoaID;
    string Nome;
    String Documento;
}
public  class Representante
{
    int RepresentanteID;
    int PessoaID.    
    string Nome;
    String Documento;
}

Based on this I have a third table, where brings representatives of People

ID     PessoaID    RepID
1        8                   2
2        8                   3
3        10                 5
4        11                 6

Based on this scheme I will be signing a contract where I speak of Pessoa, which I will pick up with Pessoaid and Reps through the Repid field to put in the contract if the customer has any or more than 1 reps.

I think the best way to represent this would be with String.Format(). Now assuming I have one person and she has three reporters, how would I do that in C#? I know I need to run a loop but I don’t know how to build it based on this model I need, and even more: I think I’m using too much code.

Here’s this part of what I did:

var pessoa = (from p in db.Pessoas
where pPessoaID == pessoaid
select p).SingleOrDefault();

var representante = (from p in db.Representantes
where p.RepID == repid
select p).SingleOrDefault();

var contrato = from p in db.Contrato
where p.PessoaID == pessoa.PessoaID
select p;

The question is: How do I get the person from ID 8 and their representatives and put them in the contract

Answer 1

I did the query in LINQ who gets people and representatives through the table that binds representatives to people, I made some style changes in her class so that it stays in a more concise pattern.

Query LINQ

// representantePessoa, pessoa e representante são 
// os objetos que contém as listas das pessoas representantes, etc..

var contratos = (from rp in representantePessoa
                join p in pessoa on rp.PessoaId equals p.PessoaId
                join r in representante on rp.RepresentanteId equals r.RepresentanteId
                // where rp.PessoaId == algumaVariavel
                select new
                {
                    NomePessoa = p.Nome,
                    DocumentoPessoa = p.Documento,
                    NomeRepresentante = r.Nome,
                    DocumentoRepresentante = r.Documento
                }).ToList();

// Para exibir basta acessar os objetos dentro de um `foreach` 
// ou LINQ como se fosse um objeto comum.

foreach (var contrato in contratos)
{
   Console.WriteLine(string.Format("Nome: {0}, Documento {1}, Representante: {2}, Documento Representante: {3}", 
   contrato.NomePessoa, contrato.DocumentoPessoa, contrato.NomeRepresentante, contrato.DocRepresentante));
}

In the query above, I’m getting the table representantePessoa and uniting (join) the tables representante and pessoa, as if it were a common SQL query, and selecting the names and documents of people and their respective representatives. No need to make queries in other tables or create other objects, this is enough, has all the information in one. Remember that the result will be exactly like an SQL query, the names of people will be repeated for each representative.

Here are the classes I used:

Classes

public class Pessoa
{
    public int PessoaId { get; set; }
    public string Nome { get; set; }
    public string Documento { get; set; }
}

public class Representante
{
    public int RepresentanteId { get; set; }
    public int PessoaId { get; set; }
    public string Nome { get; set; }
    public string Documento { get; set; }
}

public class RepresentantePessoa
{
    public int Id { get; set; }
    public int PessoaId { get; set; }
    public int RepresentanteId { get; set; }
}