CODEWITHSUNDEEP
c#asp.netentity-frameworkef-code-first
aescript
aescript

Reputation: 1925

Entity Framework - Code First - Foreign Key Constraint

I am developing a database using a code-first approach with entity framework.

I want to use foreign keys between a few tables, for instance:

 public class Producer
    {

        [Key]
        public int Id { get; set; }

        [Required]
        [StringLength(254)]
        public string Name { get; set; }
    }

then another files model is simplified as

 public class Product
    {

        [Key]
        public int Id { get; set; }

        [Required]
        [StringLength(254)]
        public string Name { get; set; }
    }

What i want is to define a ProducerId field on the Product and have it link to the Producers ID field, but i cant tell how to tell it what model/table its linking to.

Can anyone clarify? I think based on what ive read it may seem like i need to make the ID fields more descriptive and EF will find the field, is ProductId for the Products Id field - but im still not positive it will work across the tables.

Is that correct or have i missed something?

Edit:

I tried the first answer below, but using more tables than just the two, and got this error:


fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (46ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Product` (
          `ProductId` int NOT NULL AUTO_INCREMENT,
          `ProducerId` int NOT NULL,
          `ProductCategoryId` int NOT NULL,
          `ProductStyleId` int NOT NULL,
          `Name` varchar(254) NULL,
          `Year` datetime NOT NULL,
          `CreatedAt` datetime NOT NULL,
          `CategoryId` int NOT NULL,
          `StyleId` int NOT NULL,
          `Image` varbinary(4000) NULL,
          `TastingNotes` text NULL,
          `Description` text NULL,
          PRIMARY KEY (`ProductId`),
          CONSTRAINT `FK_Product_Producers_ProducerId` FOREIGN KEY (`ProducerId`) REFERENCES `Producers` (`ProducerId`) ON DELETE CASCADE,
          CONSTRAINT `FK_Product_ProductCategory_ProductCategoryId` FOREIGN KEY (`ProductCategoryId`) REFERENCES `ProductCategory` (`ProductCategoryId`) ON DELETE CASCADE,
          CONSTRAINT `FK_Product_ProductStyle_ProductStyleId` FOREIGN KEY (`ProductStyleId`) REFERENCES `ProductStyle` (`ProductStyleId`) ON DELETE CASCADE
      );
Failed executing DbCommand (46ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `Product` (
    `ProductId` int NOT NULL AUTO_INCREMENT,
    `ProducerId` int NOT NULL,
    `ProductCategoryId` int NOT NULL,
    `ProductStyleId` int NOT NULL,
    `Name` varchar(254) NULL,
    `Year` datetime NOT NULL,
    `CreatedAt` datetime NOT NULL,
    `CategoryId` int NOT NULL,
    `StyleId` int NOT NULL,
    `Image` varbinary(4000) NULL,
    `TastingNotes` text NULL,
    `Description` text NULL,
    PRIMARY KEY (`ProductId`),
    CONSTRAINT `FK_Product_Producers_ProducerId` FOREIGN KEY (`ProducerId`) REFERENCES `Producers` (`ProducerId`) ON DELETE CASCADE,
    CONSTRAINT `FK_Product_ProductCategory_ProductCategoryId` FOREIGN KEY (`ProductCategoryId`) REFERENCES `ProductCategory` (`ProductCategoryId`) ON DELETE CASCADE,
    CONSTRAINT `FK_Product_ProductStyle_ProductStyleId` FOREIGN KEY (`ProductStyleId`) REFERENCES `ProductStyle` (`ProductStyleId`) ON DELETE CASCADE
);
MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add foreign key constraint
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
C

Upvotes: 1

Views: 11227

Answers (2)

jaabh
jaabh

Reputation: 1004

To add a foreign key just add this on the Product class add:

public int ProducerId { get; set; }

[ForeignKey("ProducerId")]  //This attribute is optional bc EF should recognize Product obj specifi

public virtual Producer Producer { get; set; }

By adding public virtual Producer Producer EF Core should recognize the ProducerId property as a foreign key. virtual is used to enable Lazy Loading if you like.

The [ForeignKey()] is an optional attribute to explicitly point to the foreign and navigation keys. This post should further explain the optional use of [ForeignKey()]. How Should I Declare Foreign Key Relationships Using Code First Entity Framework (4.1) in MVC3?

To be clear, to simply add a foreign key, all that is required is adding this to class:

public int ProducerId { get; set; }
public virtual Producer Producer { get; set; }

Upvotes: 2

jtucker13
jtucker13

Reputation: 58

I'd lay it out like so, assuming your producers have a one to many relationship with products:

public class Producer
    {

        [Key]
        public int ProducerID { get; set; }

        [Required]
        [StringLength(254)]
        public string Name { get; set; }

        public ICollection<Product> Products { get; set; }
    }

public class Product
    {

        [Key]
        public int ProductID { get; set; }

        public int ProducerID { get; set; }

        [Required]
        [StringLength(254)]
        public string Name { get; set; }

        public Producer Producer { get; set;}
    }

EF will do the heavy lifting during the migration from there and you can check the context model snapshot to ensure it looks right before updating your DB. I'd recommend checking out the EF tutorials in the Microsoft Docs if you haven't already.

Upvotes: 1

Related Questions