migrating to EF.core 3.1 from EF.core 2.2 breaks generated sql, adds 1 to some column names

0

Migrating from .net core 2.2 to 3.1 has broken several EF calls:

The generated sql is introducing fields with "1" appended. Any reason?

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (257ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [d].[deployment_id], [d].[days_off], [d].[days_on], [d].[dt_end], [d].[dt_start], [d].[guard_id], [d].[guard_id1], [d].[last_modified], [d].[last_modified_by], [d].[site_id], [d].[site_id1], [g].[guard_id], [g].[address], [g].[bank], [g].[dob], [g].[dt_joined], [g].[dt_trained], [g].[has_picture], [g].[height], [g].[last_modified], [g].[last_modified_by], [g].[location_id], [g].[marital_status], [g].[mobiles], [g].[name], [g].[nuban], [g].[ref_no], [g].[religion], [g].[salary], [g].[sex], [g].[state_origin], [g].[status], [s].[site_id], [s].[address], [s].[client_id], [s].[client_id1], [s].[last_modified], [s].[last_modified_by], [s].[name], [s].[state]
      FROM [Deployments] AS [d]
      LEFT JOIN [Guards] AS [g] ON [d].[guard_id1] = [g].[guard_id]
      LEFT JOIN [Sites] AS [s] ON [d].[site_id1] = [s].[site_id]
      ORDER BY [g].[name]
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'xxxx' on server 'xxxxxx'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'xxxx' on server 'xxxxxx'.
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'XXX.Api.RSGContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904):

      Invalid column name 'guard_id1'.
      Invalid column name 'site_id1'.
      Invalid column name 'guard_id1'.
      Invalid column name 'site_id1'.
      Invalid column name 'client_id1'.

The classes in use:

public class Site
  {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Int32 site_id { get; set; }

    public String name { get; set; }
    public Int32 client_id { get; set; }
    public String address { get; set; }
    public String state { get; set; }

    public virtual Client Client { get; set; }

    public List<Deployment> Deployments { get; set; }
    public List<Contract> Contracts { get; set; }
    public List<Timesheet> Timesheets { get; set; }

    public DateTime? last_modified { get; set; }
    public int? last_modified_by { get; set; }
  }

public class Deployment
  {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Int32 deployment_id { get; set; }

    public Int32 guard_id { get; set; }
    public Int32 site_id { get; set; }
    public DateTime? dt_start { get; set; }
    public DateTime? dt_end { get; set; }
    public int? days_on { get; set; }
    public int? days_off { get; set; }

    public virtual Guard Guard { get; set; }
    public virtual Site Site { get; set; }

    public DateTime? last_modified { get; set; }
    public int? last_modified_by { get; set; }
  }

public class Guard
  {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Int32 guard_id { get; set; }

    public String name { get; set; }
    public String mobiles { get; set; }
    public String address { get; set; }
    public DateTime? dob { get; set; }
    public String sex { get; set; }
    public Int32? height { get; set; }
    public String ref_no { get; set; }
    public Int32? location_id { get; set; }
    public DateTime? dt_joined { get; set; }
    public DateTime? dt_trained { get; set; }
    public String status { get; set; }
    public Decimal? salary { get; set; }
    public String state_origin { get; set; }
    public String religion { get; set; }
    public String marital_status { get; set; }
    public bool has_picture { get; set; }
    public String bank { get; set; }
    public String nuban { get; set; }

    public List<Deployment> Deployments { get; set; }

    public List<Leave> Leave { get; set; }
    public List<Timesheet> Timesheets { get; set; }

    public DateTime? last_modified { get; set; }

    public int? last_modified_by { get; set; }

    public List<GuardGuarantor> GuardGuarantors { get; set; }
  }
c#
entity-framework
ef-core-3.1
asked on Stack Overflow Feb 5, 2020 by Charles Okwuagwu • edited Feb 5, 2020 by Charles Okwuagwu

1 Answer

0

Finally Got this to work.

What was missing is the proper modelBuilder configuration

  modelBuilder.Entity<Deployment>().HasOne<Guard>(x => x.Guard).WithMany(x => x.Deployments).HasForeignKey(x => x.guard_id);
  modelBuilder.Entity<Deployment>().HasOne<Site>(x => x.Site).WithMany(x => x.Deployments).HasForeignKey(x => x.site_id);
answered on Stack Overflow Feb 5, 2020 by Charles Okwuagwu

User contributions licensed under CC BY-SA 3.0