Query with 3 tables

Question

Query with 3 tables

Navigation

#1 by (4 votes)

0

I have 3 tables that are: Control, Buyers and Titles. In my Control table I have Code, Date, State, Buyers and Titles Id. In my Buyers table I have Id and Name, As in my table of Titles that I have Id and Name. My General query is the following:

 public List<Control> Listar()
    {
        using (DBEntities DbContext = new DBEntities())
        {
            var Ver1 = (from ctrl in DbContext.Control
                            select ctrl).ToList();
            return Ver1;

        }
    }

when I send my inquiry. It shows me the Buyers ID in the same way as Titles shows me the ID, to which I only want to show me the names of the two Buyers and Titles tables. How could you make such a query?

c# entity-framework

asked by SoyKrut 28.11.2017 в 19:47

source

1 answer

Error when storing a file in SQL MYSQL - problem with ORDER BY of a column of equal values

score 4 · Accepted Answer

Everything is in designing the domain well, before writing a line of code you have to understand how the business operates and thinks, for that we have the UML that helps us to abstract the real world, to recognize the business entities and their Attributes, based on the information you have provided, I have taken the liberty of modeling what your domain may be.

Depending on the domain, it must be a true copy in the implementation of your code and a faithful copy in your database.

Your class would be as follows according to your solution design.

Title

public class Titulo
{
    public int TituloId { get; set; }
    public string Nombre { get; set; }

    //Siempre y cuando deseas tener una colección de controles
    public virtual ICollection<Control> Controles { get; set; }
}

Buyer

public class Comprador
{
    public int CompradorId { get; set; }
    public string Nombre { get; set; }

    //Siempre y cuando deseas tener una colección de controles
    public virtual ICollection<Control> Controles { get; set; }
}

Control

namespace Dominio
{
    public enum EnumEstado : int { Activo = 1, Inactivo = 2}

    public class Control
    {
        public int ControlId { get; set; }
        public int CompradorId { get; set; }
        public int TituloId { get; set; }
        public string Codigo { get; set; }
        public DateTime Fecha { get; set; }
        public EnumEstado Estado { get; set; }

        //Propiedades de navegación
        public virtual Comprador Comprador { get; set; }
        public virtual Titulo Titulo { get; set; }
    }
}

With these navigation properties you can access the other entities from Control and elaborate your queries using linq to entities.

I have created an extended Control Control class to be able to create the query since there can not be properties of the same name.

ControlExtend

public class ControlExtend
{
    public string NombreComprador { get; set; }
    public string NombreTitulo { get; set; }
}

Your query would be as follows, in which I use the navigation properties to access the other entities.

public List<ControlExtend> ListarControles()
    {
        using (MyContext context = new MyContext())
        {
            var result = (from item in context.Controles
                          select new ControlExtend()
                          {
                              NombreComprador = item.Comprador.Nombre,
                              NombreTitulo = item.Titulo.Nombre
                          }).ToList();
            return result;
        }
    }

Greetings!