How to join two tables using auxiliary table and LINQ

Question

How to join two tables using auxiliary table and LINQ

Navigation

#1 by (1 votes)

2

I have the People and Representatives tables, 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, which brings People's Representatives

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

Based on this scheme, I generate a contract where I tell the Person that I will pick up with the PersonID and the Representatives from the RepID field to place in the contract if the client has one or more representatives.

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

Here's 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 ID 8 person and their representatives and put them on the contract

loop c# asp.net string

asked by anonymous 05.10.2017 / 20:16

1 answer

How to change column type in pandas? Error attempting asynchronous connection

score 1 · Accepted Answer

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

LINQ Query

// 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 above query, I'm getting the table representantePessoa and joining ( join ) the representante and pessoa tables, as if it were a common SQL query, and doing the select names and documents of the persons 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 just like an SQL query, people's names will be repeated for each delegate.

Here are the classes I've 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; }
}