0

I'm using EF6 over a DB that's over 15 years old. I did not make this architecture decision. All of my experience with EF has been code-first, with models I've created myself.

One of our tables has a reference table that has some info needed in selects only. These mappings will never be used for update/insert/delete.

I have two entities. My primary table:

public class QualParam
{
    [Key]
    public string MillId { get; set; } 
    [Key]
    public string Qparam { get; set; } 
    public string ValueNum { get; set; } 
    public string ActiveFlag { get; set; } 
    public int ModifiedTimestamp { get; set; } 
    public int CreatedTimestamp { get; set; } 
    public decimal SbIncrement { get; set; } 
    public string QualityDesc { get; set; }
    public string TypeCode { get; set; }

    public QualParamHeader QualParamHeader { get; set; }
    public virtual UnitMeasure UnitMeasure { get; set; } 
}

and a reference table:

public class UnitMeasure
{
    [Key]
    public string UnitOfMeasure { get; set; } 
    public int ModifiedTimestamp { get; set; } 
    public int CreatedTimestamp { get; set; } 
    public string BaseUnits { get; set; } 
    public string UnitDesc { get; set; } 
    [Key]
    public string TableName { get; set; }
    public string RollWeightFlag { get; set; } 
    public string MetricFlag { get; set; } 
    public string MxActionCode { get; set; }
    [Key]
    public string TypeCode { get; set; }  
    public byte[] RecordVersion { get; set; } 

    public List<QualParam> QualParams { get; set; }
}

QualParam may have a UnitMeasure, and UnitMeasure can have many QualParams, easy, right?

In SQL the join is done thusly

  SELECT * 
  FROM qual_params AS q
  LEFT JOIN unit_measure AS u
   ON u.unit_meas = q.unit_meas
   AND u.table_name = 'qual_params'
   AND u.type_code = q.type_code

So yes, there's a constant, the table name, and yes the keys from the source to the reference tables don't match, and aren't even enumerated in the db to begin with. Again, legacy.

Our Db context- 

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new QualParamsConfiguration());
    modelBuilder.Configurations.Add(new UnitMeasureConfiguration());
    base.OnModelCreating(modelBuilder);
}

I'm using auto generated configs- 

public QualParamConfiguration()
        : this("dbo")
    {
    }

    public QualParamConfiguration(string schema)
    {
        ToTable(schema + ".qual_params");
        HasKey(x => new { x.MillId, x.Qparam });

        Property(x => x.Qparam).HasColumnName(@"qparam").IsRequired().IsFixedLength().IsUnicode(false).HasColumnType("char").HasMaxLength(10).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);
        Property(x => x.ValueNum).HasColumnName(@"value_num").IsRequired().IsFixedLength().IsUnicode(false).HasColumnType("char").HasMaxLength(9);
        Property(x => x.ActiveFlag).HasColumnName(@"active_flag").IsRequired().IsFixedLength().IsUnicode(false).HasColumnType("char").HasMaxLength(1);
        Property(x => x.ModifiedTimestamp).HasColumnName(@"ts_modified").IsRequired().HasColumnType("int");
        Property(x => x.CreatedTimestamp).HasColumnName(@"ts_create").IsRequired().HasColumnType("int");
        Property(x => x.SbIncrement).HasColumnName(@"sb_increment").IsRequired().HasColumnType("decimal").HasPrecision(7, 3);
        Property(x => x.QualityDesc).HasColumnName(@"quality_desc").IsRequired().IsFixedLength().IsUnicode(false).HasColumnType("char").HasMaxLength(50);
        Property(x => x.MillId).HasColumnName(@"mill_id").IsRequired().IsFixedLength().IsUnicode(false).HasColumnType("char").HasMaxLength(10).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);


        this.HasRequired(a => a.QparamHeader).WithMany(b => b.QualParams).HasForeignKey(c => c.Qparam).WillCascadeOnDelete(false);
    }

The last navigation wrote itself, for obvious reasons. Simple relationship.

This one, not so much. I'm open to everything- I'm fairly aware at this point that I'll have to create a separate model to achieve this.

The examples I've seen using .Map must be from older versions of EF, since many of the methods are not available to me. Is it even possible to achieve this relationship?

Vern Halen
  • 53
  • 1
  • 8
  • I don't see `TypeCode` in `QualParam`. What maps to `q.type_code` in your SQL? – Ivan Stoev Feb 01 '17 at 08:47
  • Thanks, edited. Yeah, it's there. The class actually has far more properties, I was just trying to boil it down to the salient points. – Vern Halen Feb 01 '17 at 11:12
  • Is modifying the database an option? I mean adding a computed column to `qual_params` table? – Ivan Stoev Feb 01 '17 at 11:58
  • Ivan, not in my current timetable. The db is third-party vendor supplied thing that's plugged into our manufacturing systems. We don't have admin rights over it, and getting any kind of modification is a long bureaucratic process. – Vern Halen Feb 01 '17 at 13:19
  • 1
    Unfortunately Code First does not support what you need. I guess you have to remove the association and use manual joins. – Ivan Stoev Feb 01 '17 at 14:15

0 Answers0