Entity framework 6, same column for many foreign keys

Question

I need help with this issue, because after many hours of investigation I am stuck.

I created a datamodel from an existing old database, using Entity Framework 6 (I am using Code First approach). This database was multi-company oriented, so most of its tables has a column "Company" that its used as a part of almost all primary keys and foreign keys.

The datamodel creation created all the foreign keys using Fluent API. But this don't helps and when I try to select data from any table I received errors "invalid columna name 'TABLE_COLUMN'. Because in this database usually the columns has different name in every table and the Entity framework can't determine the relation, so its required to map the column names.

So, I can solve the issue using DataAnnotations, and I can do, for example:

        [Key]
    [Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [ForeignKey("BLOQHOR"), InverseProperty("CODHOR")]
    public int NUMHOR { get; set; }

    [Key]
    [Column(Order = 2)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [ForeignKey("BLOQHOR"), InverseProperty("DISTAINIC")]
    public int DISTAINIC { get; set; }

    [Key]
    [Column(Order = 3)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [ForeignKey("BLOQHOR"), InverseProperty("COMPANY")]
    public int COMPANY{ get; set; }

What happends now?

The table has another foreign key that also needs the column COMPANY. Because data annotations don't allow me to use the column twice, I can't make the table to work.

I repeat, in the data model, it created a fluent api definition for the second foreign key, but it don't works.

            modelBuilder.Entity<CABAJUSTES>()
            .HasMany(e => e.AJUSBLOQ)
            .WithRequired(e => e.CABAJUSTES)
            .HasForeignKey(e => new { e.NUMAJUST, e.COMPANY})

The fact its that everytime I try to get data I received errors like "Invalid column name CABAJUSTES_CODAJUSTE" and "Invalid column name CABAJUSTES_COMPANY". And I am unable to map this second foreign key.

What can I do?

Thanks in advance.

Answer 1

Its a bit hard to follow your table structure, so I've tried to set up a comprehensive example using some common entities anyone should be able to follow. Please comment if this does not fully describe your problem.

Note that I've deliberately used pretty shitty foreign keys to make sure the helping automapping in Entity Framework doesn't help me, and to show that this works with any legacy database design you may have.

First the expected structure in the example

• One Company holds many Articles and many Invoices.
• One Invoice holds many InvoiceRows.
• Each InvoiceRow may optionally refer to an Article.

The actual Entities

class Company
{
    public int TheCompanyKey { get; set; }

    public virtual ICollection<Invoice> Its_Invoices { get; set; }
    public virtual ICollection<Article> Its_Articles { get; set; }
}

class Invoice
{
    public int Its_CompanyKey { get; set; }
    public int TheInvoiceKey { get; set; }

    public string InvoiceNumber { get; set; }
    public DateTime InvoiceDate { get; set; }

    public virtual Company Its_Company { get; set; }
    public virtual ICollection<InvoiceRow> Its_Rows { get; set; }
}

class InvoiceRow
{
    public int Rows_Company_Key { get; set; }
    public int Its_InvoiceID { get; set; }
    public int RowNumber { get; set; }

    public int? Its_Articles_ID { get; set; }
    public string Text { get; set; }
    public double Price { get; set; }

    public virtual Invoice Its_Invoice { get; set; }
    public virtual Article Its_Article { get; set; }
}

class Article
{
    public int TheArticleCompany_Key { get; set; }
    public int TheArticleKey { get; set; }

    public string ArticleNumber { get; set; }
    public double Cost { get; set; }
    public double TargetPrice { get; set; }

    public virtual Company Its_Company { get; set; }
}

The DbContext with OnModelCreating()

There are multiple ways to generate the required structure, depending on if you think top-down or bottom-up. My take on modelling is to start with the base tables and the describe how children relate to them.

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

    public virtual IDbSet<Company> Companies { get; set; }
    public virtual IDbSet<Invoice> Invoices { get; set; }
    public virtual IDbSet<InvoiceRow> InvoiceRows { get; set;}
    public virtual IDbSet<Article> Articles { get; set; }


    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Company>()
            .HasKey(e => e.TheCompanyKey);

        modelBuilder.Entity<Article>()
            .HasKey(e => new { e.TheArticleCompany_Key, e.TheArticleKey })
            .HasRequired(e => e.Its_Company).WithMany(e => e.Its_Articles).HasForeignKey(e => e.TheArticleCompany_Key);

        modelBuilder.Entity<Invoice>()
            .HasKey(e => new { e.Its_CompanyKey, e.TheInvoiceKey })
            .HasRequired(e => e.Its_Company).WithMany(e => e.Its_Invoices).HasForeignKey(e => e.Its_CompanyKey);

        modelBuilder.Entity<InvoiceRow>()
            .HasKey(e => new { e.Rows_Company_Key, e.Its_InvoiceID, e.RowNumber });

        modelBuilder.Entity<InvoiceRow>()
            .HasRequired(e => e.Its_Invoice).WithMany(e => e.Its_Rows)
            .HasForeignKey(e => new { e.Rows_Company_Key, e.Its_InvoiceID }).WillCascadeOnDelete();

        modelBuilder.Entity<InvoiceRow>()
            .HasOptional(e => e.Its_Article)
            .WithMany()
            .HasForeignKey(e => new { e.Rows_Company_Key, e.Its_Articles_ID });
    }
}

Finally the generated migration

Running add-migration multikeys in the Package Manager Console window results in the following migration:

public partial class multikeys : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Articles",
            c => new
                {
                    TheArticleCompany_Key = c.Int(nullable: false),
                    TheArticleKey = c.Int(nullable: false),
                    ArticleNumber = c.String(),
                    Cost = c.Double(nullable: false),
                    TargetPrice = c.Double(nullable: false),
                })
            .PrimaryKey(t => new { t.TheArticleCompany_Key, t.TheArticleKey })
            .ForeignKey("dbo.Companies", t => t.TheArticleCompany_Key, cascadeDelete: true)
            .Index(t => t.TheArticleCompany_Key);

        CreateTable(
            "dbo.Companies",
            c => new
                {
                    TheCompanyKey = c.Int(nullable: false, identity: true),
                })
            .PrimaryKey(t => t.TheCompanyKey);

        CreateTable(
            "dbo.Invoices",
            c => new
                {
                    Its_CompanyKey = c.Int(nullable: false),
                    TheInvoiceKey = c.Int(nullable: false),
                    InvoiceNumber = c.String(),
                    InvoiceDate = c.DateTime(nullable: false),
                })
            .PrimaryKey(t => new { t.Its_CompanyKey, t.TheInvoiceKey })
            .ForeignKey("dbo.Companies", t => t.Its_CompanyKey, cascadeDelete: true)
            .Index(t => t.Its_CompanyKey);

        CreateTable(
            "dbo.InvoiceRows",
            c => new
                {
                    Rows_Company_Key = c.Int(nullable: false),
                    Its_InvoiceID = c.Int(nullable: false),
                    RowNumber = c.Int(nullable: false),
                    Its_Articles_ID = c.Int(),
                    Text = c.String(),
                    Price = c.Double(nullable: false),
                })
            .PrimaryKey(t => new { t.Rows_Company_Key, t.Its_InvoiceID, t.RowNumber })
            .ForeignKey("dbo.Articles", t => new { t.Rows_Company_Key, t.Its_Articles_ID })
            .ForeignKey("dbo.Invoices", t => new { t.Rows_Company_Key, t.Its_InvoiceID }, cascadeDelete: true)
            .Index(t => new { t.Rows_Company_Key, t.Its_Articles_ID })
            .Index(t => new { t.Rows_Company_Key, t.Its_InvoiceID });

    }

    public override void Down()
    {
        DropForeignKey("dbo.Articles", "TheArticleCompany_Key", "dbo.Companies");
        DropForeignKey("dbo.InvoiceRows", new[] { "Rows_Company_Key", "Its_InvoiceID" }, "dbo.Invoices");
        DropForeignKey("dbo.InvoiceRows", new[] { "Rows_Company_Key", "Its_Articles_ID" }, "dbo.Articles");
        DropForeignKey("dbo.Invoices", "Its_CompanyKey", "dbo.Companies");
        DropIndex("dbo.InvoiceRows", new[] { "Rows_Company_Key", "Its_InvoiceID" });
        DropIndex("dbo.InvoiceRows", new[] { "Rows_Company_Key", "Its_Articles_ID" });
        DropIndex("dbo.Invoices", new[] { "Its_CompanyKey" });
        DropIndex("dbo.Articles", new[] { "TheArticleCompany_Key" });
        DropTable("dbo.InvoiceRows");
        DropTable("dbo.Invoices");
        DropTable("dbo.Companies");
        DropTable("dbo.Articles");
    }
}

Summary

I believe this describes the OP problem and with a little study gives a good understanding of how Fluent can be used to map entities.

Good luck!