CODEWITHSUNDEEP
c#entity-frameworkef-code-firstentity-framework-5
NicoJuicy
NicoJuicy

Reputation: 3528

Foreign Key duplicate in database

Some foreign key's are created double in my contract table. (Article and Client). And Company is ok!

My models:

public class 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; }
    public virtual Client Client { get; set; }

    [Required]
    public int CompanyID { get; set; }
    public virtual Company Company { get; set; }

    [Required]
    public int ArticleID { get; set; }
    public virtual Article Article { get; set; }

}

public class 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);
        }
    }

    //[Required]
    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 Company
{
    [Key]
    public int CompanyID { get; set; }
    public string Name { get; set; }
    public int DeviceIncomingWeight { get; set; }
    public string ZipCode { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string City { get; set; }
    public bool Admin { get; set; }
    public int UnitForMeasurements { get; set; }
    public int UnitForDisplayOnDocuments { get; set; }

    public virtual ICollection<User> Users { get; set; }
    public virtual ICollection<Category> Categories { get; set; }
    public virtual ICollection<Article> Articles { get; set; }
    public virtual ICollection<Client> Clients { get; set; }
    public virtual ICollection<Location> Locations { get; set; }
    public virtual ICollection<Contract> Contracts { get; set; }
    public virtual ICollection<IncomingMeasurement> IncomingMeasurements { get; set; }
    public virtual ICollection<Measurement> Measurements { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
}

public class 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; }
}

This is my OnModelCreating, where probably the fault lies:

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       // modelBuilder.Entity<Contract>().HasRequired(bm => bm.Company).WithMany().WillCascadeOnDelete(false);
        modelBuilder.Entity<Contract>().HasRequired(bm => bm.Article).WithMany().WillCascadeOnDelete(false);
        modelBuilder.Entity<Contract>().HasRequired(bm => bm.Client ).WithMany().WillCascadeOnDelete(false);
        modelBuilder.Entity<Article>().HasRequired(bm => bm.Company).WithMany().WillCascadeOnDelete(false);
        modelBuilder.Entity<Measurement>().HasRequired(bm => bm.Company).WithMany().WillCascadeOnDelete(false);
        modelBuilder.Entity<Order>().HasRequired(bm => bm.Client).WithMany().WillCascadeOnDelete(false);
        modelBuilder.Entity<Order>().HasRequired(bm => bm.Article).WithMany().WillCascadeOnDelete(false);
        modelBuilder.Entity<IncomingMeasurement>().HasRequired(bm => bm.client).WithMany().WillCascadeOnDelete(false);
        modelBuilder.Entity<Client>().HasOptional(c => c.MerchantReference).WithMany().HasForeignKey(c => c.MerchantReferenceID);

        //Required fields


        base.OnModelCreating(modelBuilder);
    }

And there is something weird happening in my db (my sql server), namely this is my create table schema.

These are my fields:

CREATE TABLE [dbo].[Contracts](
[ContractID] [int] IDENTITY(1,1) NOT NULL,
[PricePerUnit] [float] NOT NULL,
[Unit] [int] NOT NULL,
[Currency] [int] NOT NULL,
[ClientID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[ArticleID] [int] NOT NULL,
[Client_ClientID] [int] NOT NULL,
[Article_ArticleID] [int] NOT NULL,
[Client_ClientID1] [int] NULL,
[Article_ArticleID1] [int] NULL,

If you notice it, [Client_ClientID] has a duplicate: [Client_ClientID1] and also [Article_ArticleID] in [Article_ArticleID1]. But company doesn't.

Any thoughts on how to fix this?

Upvotes: 0

Views: 491

Answers (2)

Daniel Br&#252;ckner
Daniel Br&#252;ckner

Reputation: 59645

This happens because you include a redundant (foreign key) column in your entity classes. For example look at the category in your Contract class.

public class Contract
{
    public Int32 CategoryID { get; set; }

    public virtual Category Category { get; set; }
}

You manually specify a property and therefore column CategoryID and then the Entity Framework generates another column to hold the foreign key for the Category referenced by the property Category.

So just remove the property CategoryID and use contract.Category.CategoryID instead if you need the ID of the referenced category.

UPDATE

I was not aware of the suggestion to include a foreign key property but looking at the article linked in the comment to Jeff Siever's answer I probably spotted the answer in the section Configuring Unconventional Foreign Key Names.

The Entity Framework uses a convention to match the name of the navigation property and the foreign key property and the default convention is either NavigationPropertyNameId or NavigationPropertyName_Id while you use NavigationPropertyNameID with uppercase D.

So you have several options - change your naming to use Id, replace the convention or override the convention.

Upvotes: 1

Jeff S
Jeff S

Reputation: 7484

Eliminate the duplicate information from your model. The id's for the referenced objects aren't needed and are what is causing your problem.

public class Contract {
    [Key]
    public int ContractID { get; set; }
    public double PricePerUnit { get; set; }
    public int Unit { get; set; }
    public int Currency { get; set; }

    public virtual Client Client { get; set; }

    public virtual Company Company { get; set; }

    public virtual Article Article { get; set; }
}

And instead of the Required attribute on the id, you need to setup your entities so that the children are required.

Upvotes: 0

Related Questions