Linq adds a nonexistent column to the SQL query

Question

Linq adds a nonexistent column to the SQL query

Navigation

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

2

I'm having a problem with LINQ, using EntityFramework 6

First, to enter the context, I leave the Problem Model ..

[Table("Datos_Garantizados")]
public partial class Datos_Garantizados
{
[Key]
public long Version { get; set; }

public decimal? Perdidas_Vacio { get; set; }

public decimal? Corriente_Vacio { get; set; }

public decimal? Perdidas_Cortocircuito_P_S { get; set; }

public decimal? Perdidas_Cortocircuito_P_T { get; set; }

public decimal? Perdidas_Cortocircuito_S_T { get; set; }

public decimal? Tension_Cortocircuito_P_S { get; set; }

public decimal? Tension_Cortocircuito_P_T { get; set; }

public decimal? Tension_Cortocircuito_S_T { get; set; }

public decimal? Ref_Tens_Cortocircuito_P_S { get; set; }

public decimal? Ref_Tens_Cortocircuito_P_T { get; set; }

public decimal? Ref_Tens_Cortocircuito_S_T { get; set; }

public decimal? Bil_Primario { get; set; }

public decimal? Bil_Secundario { get; set; }

public decimal? Bil_Terciario { get; set; }

public decimal? Aplicada_Primaria { get; set; }

public decimal? Aplicada_Secundaria { get; set; }

public decimal? Aplicada_Terciaria { get; set; }

public decimal? SobreTemp_Aceite_Max { get; set; }

public decimal? SobreTemp_Arroll_Medio { get; set; }

public decimal? Ref_Perdidas_Cortocircuito_Primario_Secundario { get; set; }

public decimal? Ref_Perdidas_Cortocircuito_Primario_Terciario { get; set; }

public decimal? Ref_Perdidas_Cortocircuito_Secundario_Terciario { get; set; }

public decimal? Potencia_Ref { get; set; }

}

The context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Perdidas_Vacio)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Corriente_Vacio)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Perdidas_Cortocircuito_P_S)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Perdidas_Cortocircuito_P_T)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Perdidas_Cortocircuito_S_T)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Tension_Cortocircuito_P_S)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Tension_Cortocircuito_P_T)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Tension_Cortocircuito_S_T)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Ref_Tens_Cortocircuito_P_S)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Ref_Tens_Cortocircuito_P_T)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Ref_Tens_Cortocircuito_S_T)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Bil_Primario)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Bil_Secundario)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Bil_Terciario)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Aplicada_Primaria)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Aplicada_Secundaria)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Aplicada_Terciaria)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.SobreTemp_Aceite_Max)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.SobreTemp_Arroll_Medio)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Ref_Perdidas_Cortocircuito_Primario_Secundario)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Ref_Perdidas_Cortocircuito_Primario_Terciario)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Ref_Perdidas_Cortocircuito_Secundario_Terciario)
    .HasPrecision(18, 0);

modelBuilder.Entity<Datos_Garantizados>()
    .Property(e => e.Potencia_Ref)
    .HasPrecision(18, 0);
}

The issue is as follows, when I try to get the list of Guaranteed Data with LINQ in the following way

var a = db.Datos_Garantizados.ToList();

Skip an exception of type SqlException

The column name 'Transformador_Codigo_delfos' is not valid.

When I debug, seeing the Query SQL generated by linq, it can be seen that it adds the column "Transformador_Code_delfos" that does not exist in the table.

Clarification There is a model, which is related to the one of Datos_Garantizados, I do not know if this echo can be relevant

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

    [Key]
    public string Codigo_delfos { get; set; }
    //varios campos más
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Datos_Garantizados> Datos_Garantizados { get; set; }

It is my first project with EF and this is complicating my existence: P

Since many thanks!

sql c# linq asp.net-mvc entity-framework-6

asked by Juan Salvador Portugal 28.03.2018 в 16:18

source

2 answers

0

Of course that relationship has relevance, if you tell Transformer that you have a collection of Guaranteed Data then EF assumes that Guaranteed_Data must have a TransformerId (In this case, Code_delfos that was what you established as Key) to be able to do 1 to many . I recommend adding to Datos_Garantizados:

public string Codigo_delfos { get; set; }
public Transformador Transformador { get; set; }

in this way EF recognizes the relationship with Transformer

after migrating it should work. I hope it helps you

answered by 28.03.2018 в 16:39

Group 2 count () in a query in PostgreSQL I need to treat an enumerated as if it were an array in a jsp with jstl

score 1 · Accepted Answer

Good morning:

Indeed, the navigation you have from Transformer to the collection of Guaranteed Data causes this self-generated temporary mapping. This is because you have a cardinality that a Transformer has many Guaranteed_Data, you must mark the return navigation, that is, an object Guaranteed_Data belongs to a Transformer.

Try to put the navigation back in your GuaranteedData class.

public string Codigo_delfos { get; set; }

[ForeignKey("Codigo_delfos")]
public virtual Transformador Transformador { get; set; }

Assuming that you are using entity, I recommend updating your model's migration from the NuGet console with the following command:

update-database

Greetings.

Update:

If it's many to one you can avoid the middle table and EF will generate the collection for you. If your cardinality is many to many and you want to keep an intermediate table, then in the Model Builder you should declare it like this:

modelBuilder.Entity<TransformadoresDatos>()
                .HasMany(r => r.Transformadores)
                .WithMany(p => p.Datos_Garantizados)
                .Map(rp =>
                {
                    rp.MapLeftKey("Codigo_delfos");
                    rp.MapRightKey("Versión");
                    rp.ToTable("TransformadoresDatos");
                });