INNER JOIN listed in the Entity Framework

Question

INNER JOIN listed in the Entity Framework

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have these 2 tables

Table Products

Table Balances

I want to list the table in such a way that it shows me all the data of the first table plus the amount according to the id, I did it with this sentence:

    SELECT Articulos.id, Articulos.nombreProducto, Articulos.Precio, Saldos.Cantidad
      FROM Articulos 
INNER JOIN Saldos ON Articulos.id = Saldos.id

I'm trying to do this myself but using EF in C # but it does not work out, I have this but it's wrong

public List<Articulos> listar(){
    var arti = new List<Articulos>();

    try{
        using (var ctx = new ModeloProductos()){
            arti = ctx.Articulos.Include("Saldos")
                                .Include("Saldos.id")
                                .Where(x => x.id == id)
                                .ToList();
        }
    } catch (Exception E) {
        throw;
    }
    return arti;
}

How can I pass that INNER JOIN to the EF?

EDITED Model of the db Products

namespace PruebaVictorDuarte.Models
{
    using System;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;

    public partial class ModeloProductos : DbContext
    {
        public ModeloProductos()
            : base("name=ModeloProductos")
        {
        }

        public virtual DbSet<Articulos> Articulos { get; set; }
        public virtual DbSet<Bitacora> Bitacora { get; set; }
        public virtual DbSet<Saldos> Saldos { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Articulos>()
                .Property(e => e.nombreProducto)
                .IsUnicode(false);

            modelBuilder.Entity<Articulos>()
                .Property(e => e.Precio)
                .HasPrecision(18, 4);

            modelBuilder.Entity<Articulos>()
                .HasMany(e => e.Bitacora)
                .WithRequired(e => e.Articulos)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Articulos>()
                .HasMany(e => e.Saldos)
                .WithRequired(e => e.Articulos)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<Bitacora>()
                .Property(e => e.Historial)
                .IsUnicode(false);
        }
    }
}

Articles

namespace PruebaVictorDuarte.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Data.Entity.Spatial;
    using System.Linq;

    public partial class Articulos
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Articulos()
        {
            Bitacora = new HashSet<Bitacora>();
            Saldos = new HashSet<Saldos>();
        }

        public int id { get; set; }

        [Required]
        [StringLength(50)]
        public string nombreProducto { get; set; }

        public decimal Precio { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Bitacora> Bitacora { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Saldos> Saldos { get; set; }


        /*AGREGA DATOS DEL PRODUCTO A LA TABLA
         Y RECUPERA EL ID INSERTADO*/
        public int addArticulo(string nombre, decimal pp)
        {
            int idCate=0;

            var datos = new Articulos
            {
                nombreProducto = nombre,
                Precio = pp
            };

            using (var ctx = new ModeloProductos())
            {
                ctx.Entry(datos).State = EntityState.Added;
                ctx.SaveChanges();
                idCate = datos.id;
            }

            return idCate;
        }


    }


}

Balances

namespace PruebaVictorDuarte.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Data.Entity.Spatial;

    public partial class Saldos
    {
        [Key]
        [Column(Order = 0)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int id { get; set; }

        [Key]
        [Column(Order = 1)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Cantidad { get; set; }

        public virtual Articulos Articulos { get; set; }

        /*ESTABLECE EL SALDO PRIMARIO EN CERO ("0")*/
        public void setSaldo(int sal)
        {
            int saldo = 0;

            var datos = new Saldos
            {
                id=sal,
                Cantidad = saldo
            };

            using(var ctx=new ModeloProductos()){

                ctx.Entry(datos).State = EntityState.Added;
                ctx.SaveChanges();
            } 
        }
    }
}

sql c# entity-framework

asked by Baker1562 25.08.2018 в 00:26

source

2 answers

1

Linq to entities automatically makes the inner join, based on your model, for example, if you use code first, if you had the following model

class A
{
    [Key]
    public int Id { get; set; } 
    public string CampoDeEjemplo { get; set; }
    public int B_Id { get; set; }
    [ForeignKey("B_Id")]
    public B Inner { get; set; }

}

class B
{
    [Key]
    public int Id { get; set; }
    public string OtroCampoEjemplo { get; set; }
}

At the moment of making a query with linq to entities, you can choose two load methods, LazyLoading and EagerLoading , in the case of the first, you will perform the INNER JOIN automatically, in the second, you would do it by hand (if you have many relationships, you would generate a much higher performance)

Example LazyLoading

using(var db = new TuDBContext())
{
    //Por lo general está definido por defecto de esta manera
    db.Configuration.LazyLoadingEnabled = true;
    var Ejemplo = db.A.Where(x =>x.Id==3).First();
    Console.WriteLine(A.Inner.OtroCampoEjemplo); //Debería dar como resultado, el contenido de B relacionado con A en la base de datos
}

Example EagerLoading (manually I include b ( .Include("B") )

using(var db = new TuDBContext())
{
    db.Configuration.LazyLoadingEnabled = false;
    var Ejemplo = db.A.Include("B").Where(x =>x.Id==3).First();
    Console.WriteLine(A.Inner.OtroCampoEjemplo); //Debería dar como resultado, el contenido de B relacionado con A en la base de datos
}

I hope it is understood, if you still have problems regarding this topic, it would be good if you edit the question, adding more precisely your model!

Sorry if there is any syntax error, I'm without ide!

Greetings!

Edit: I would say, if the relations of the model are not well configured, it will not work!

answered by 25.08.2018 в 04:25

Error importing matplotlib in python how to randomize an array of strings?

score 1 · Accepted Answer

For queries with joins as you return mixed data from tables it is not recommended that you return the object of an entity, but declare an object that will contain the data of your query of several tables, for example:

public class ArticuloSaldo
{
    public int Id { get; set; }
    public string NombreProducto { get; set; }
    public decimal Precio { get; set; }
    public int Cantidad { get; set; }
}

And the query in this way that is more similar to SQL and easy to understand:

using(var ctx = new ModeloProductos())
{
    var query = from a in ctx.Articulos
                join s in ctx.Saldos on a.id equals s.id
                select new ArticuloSaldo { 
                    Id = a.id, 
                    NombreProducto = a.nombreProducto, 
                    Precio = a.Precio, 
                    Cantidad = s.Cantidad };
    return query.ToList();
}