Double foreign key generation

This is a followup-question of this question, where i had a similar problem. But this is solved now by default foreign key convention.

My problem now is (in short), that my migrations generates

int ReferencedEntityID; int ReferencedEntity_ReferencedEntityID;

where one is an integer property in my model and the other one is a virtual property.

My migrations generates this:

"dbo.Contracts",
            c => new
                {
                    ContractId = c.Int(nullable: false, identity: true),
                    PricePerUnit = c.Double(nullable: false),
                    Unit = c.Int(nullable: false),
                    Currency = c.Int(nullable: false),
                    ClientId = c.Int(nullable: false),
                    CompanyId = c.Int(nullable: false),
                    ArticleId = c.Int(nullable: false),
                    Client_ClientId = c.Int(),
                    Article_ArticleId = c.Int(),
                })

As you can see, Client & Article are referenced twice.

Here are my models

public class Client {
    public Client() { }

    [Key]
    public int ClientId { get; set; }
    public string Number { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string ZipCode { get; set; }
    public string City { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string Memo { get; set; }
    public bool isMerchant { get; set; }

    public string Name
    {
        get
        {
            return string.Format("{0} {1}", FirstName, LastName);
        }
    }

    public int? MerchantReferenceId { get; set; }
    public virtual Client MerchantReference { get; set; }
    [Required]
    public int CompanyId { get; set; }
    public virtual Company Company { get; set; }
    public virtual ICollection<Contract> Contracts { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
}

public class Article {

    public Article() { }

    [Key]
    public int ArticleId { get; set; }
    [Required]
    public string Code { get; set; }
    public string Name { get; set; }
    public bool TrackStock { get; set; }
    public int CurrentStock { get; set; }
    public double? Price { get; set; }

    [Required]
    public int CompanyId { get; set; }
    public virtual Company Company { get; set; }
    [Required]
    public int CategoryId { get; set; }
    public virtual Category Category { get; set; }

    public virtual ICollection<Contract> Contracts { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
}

public class Contract {

    public Contract() { }

    [Key]
    public int ContractId { get; set; }
    public double PricePerUnit { get; set; }
    public int Unit { get; set; }
    public int Currency { get; set; }

    [Required]
    public int ClientId { get; set; }
  //  [ForeignKey("ClientId")]
    public virtual Client Client { get; set; }

    [Required]
    public int CompanyId { get; set; }
    //[ForeignKey("CompanyID")]
    public virtual Company Company { get; set; }

    [Required]
    public int ArticleId { get; set; }
   // [ForeignKey("ArticleId")]
    public virtual Article Article { get; set; }

}

Here is my OnModelCreating()

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
           // modelBuilder.Entity<Contract>().HasRequired(bm => bm.Company).WithMany().WillCascadeOnDelete(false);
            modelBuilder.Entity<Contract>().HasRequired(bm => bm.Article).WithMany().HasForeignKey(dl => dl.ArticleId).WillCascadeOnDelete(false);//.Map( dl => dl.MapKey("ArticleId"))
            modelBuilder.Entity<Contract>().HasRequired(bm => bm.Client).WithMany().HasForeignKey(dl => dl.ClientId).WillCascadeOnDelete(false);//.Map(dl => dl.MapKey("ClientId"))
            modelBuilder.Entity<Article>().HasRequired(bm => bm.Company).WithMany().HasForeignKey(dl => dl.CompanyId).WillCascadeOnDelete(false);//.Map(dl => dl.MapKey("CompanyId"))
            modelBuilder.Entity<Measurement>().HasRequired(bm => bm.Company).WithMany().HasForeignKey(dl => dl.CompanyId).WillCascadeOnDelete(false); //.Map(dl => dl.MapKey("CompanyId"))
            modelBuilder.Entity<Order>().HasRequired(bm => bm.Client).WithMany().HasForeignKey(dl => dl.ClientId).WillCascadeOnDelete(false); //.Map(dl => dl.MapKey("ClientId"))
            modelBuilder.Entity<Order>().HasRequired(bm => bm.Article).WithMany().HasForeignKey(dl => dl.ArticleId).WillCascadeOnDelete(false);//.Map(dl => dl.MapKey("ArticleId"))
            modelBuilder.Entity<IncomingMeasurement>().HasRequired(bm => bm.client).WithMany().HasForeignKey(dl => dl.ClientId).WillCascadeOnDelete(false);//.Map(dl => dl.MapKey("ClientId"))
            modelBuilder.Entity<Client>().HasOptional(c => c.MerchantReference).WithMany().HasForeignKey(dl => dl.MerchantReferenceId); //.Map(dl => dl.MapKey("MerchantReferenceId"))

            //Required fields


            base.OnModelCreating(modelBuilder);
        }

What do i have to do, to create them both:

  • Required
  • Both in one property in my db-schema (as it should)

Answers


It is OK, even recommended, to have primitive FK properties (like ArticleId) accompanying the "real" references. In EF this is called a foreign key association as opposed to an independent association where there is only a reference (like Article.Company).

So you can keep your model the way it is. You just have to specify the foreign keys.

I tried with a few classes in the model of your previous question and this produced the desired results:

modelBuilder.Entity<Article>().HasMany(a => a.Contracts)
    .WithRequired(c => c.Article)
    .HasForeignKey(c => c.ArticleID).WillCascadeOnDelete(false);
modelBuilder.Entity<Client>().HasMany(c => c.Contracts)
    .WithRequired(c => c.Client)
    .HasForeignKey(c => c.ClientID).WillCascadeOnDelete(false);
modelBuilder.Entity<Company>().HasMany(c => c.Articles)
    .WithRequired(a => a.Company)
    .HasForeignKey(c => c.CompanyID).WillCascadeOnDelete(false);

Note that I turned around the definitions because when I did it your way, but with HasForeignKey it still duplicated the FK fields. I'm not sure why.


Need Your Help

Asp.Net - Export two Gridviews to excel or pdf

c# asp.net gridview

I have a page that shows two grids side by side; "Expenses" and "Income".

StreamReader OutOfMemoryException

c# out-of-memory streamreader webrequest webresponse

I am using a web service that returns xml an I want to read it into a string. I am using ReadToEnd method of StreamReader class but at this moment OutOfMemory Exception occurs because of the large ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.