Entity Framework - Code First - Foreign Key Constraint

Question

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

On the `Product` class add `public int PRoductId {get;set;}` then add `public virtual Product Product { get; set; }` — jaabh, Sep 18 '20 at 20:48
I dont think i follow what youre saying... can you elaborate? You want me to change the id field to ProductId, then add a virtual constructor? and i dont understand what your second comment means — aescript, Sep 18 '20 at 20:54
It appears you have made edits to the question asking for different details, unrelated to the original question, after answers have been posted to answer the original question. — jaabh, Sep 18 '20 at 22:22
The edit is not asking for different details... i tried what you suggested and got errors and have been unable to solve them - its not a new detail. — aescript, Sep 18 '20 at 22:22
I see. Sometimes adding a foreign key to a table with existing data will cause problems adding the migration because of the constraints. Is the table already populated? — jaabh, Sep 18 '20 at 22:26
I will post them soon - But quick question in the mean time. I am prematurely testing this and havent made a controller for every model yet only "producers" has a controller. And this is the only table getting created. is it because they dont have controllers? — aescript, Sep 18 '20 at 22:45
No tables don't require controllers to exist. But I would suggest creating an empty application with an empty database, then adding the models and database tables, just to see how it works. — jaabh, Sep 18 '20 at 22:46
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/221712/discussion-between-aescript-and-janzen). — aescript, Sep 18 '20 at 23:06

jaabh · Accepted Answer · 2020-09-18T21:23:27.867

2

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

edited Sep 18 '20 at 21:23

answered Sep 18 '20 at 21:00

jaabh

815
6
22

I have provided one way of doing it. But there are many ways to indicate foreign and navigation keys. Also looking into Fluent Api is also a good way of going about it. https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key – jaabh Sep 18 '20 at 21:08
Im trying this way now... I tried Janzens, and i probably did something wrong but my DB wont build now. – aescript Sep 18 '20 at 22:22
Sometimes if there is already data in the table that you are adding a foreign key to. It will not build because of the constraints. – jaabh Sep 18 '20 at 22:24
This isnt giving me an error, but I now have a new problem that when i drop my database and start fresh after having set things up this way (with your final example) and then i make a migration and update, im only getting ONE table. For producers, but not the rest – aescript Sep 18 '20 at 22:34
What are your new classes? Can you add that to the post? Are using migrations? – jaabh Sep 18 '20 at 22:38
Eventually, following the final exampple in this answer, and realizing my other mistkaes, i beleive I have this working. Thanks for this! – aescript Sep 18 '20 at 23:23
Glad to help Anytime. – jaabh Sep 18 '20 at 23:25

score 1 · Answer 2 · answered Sep 18 '20 at 20:57

1

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.

answered Sep 18 '20 at 20:57

jtucker13

58
7

What does ICollection Products appear is in the db? What type of field does a collection make? And just name a PrducerId and then giving it a field for producer will set those properly and link the fields? thers no need to add a foreignkey attribute? – aescript Sep 18 '20 at 21:01
ICollection represents a group of objects- it's assumed a producer will have more than one product to offer. If this was simply a one to one relationship, you would have a single `Public Product Product` there instead. **Products** and **Producer** are actually considered _navigation properties_ and won't have columns in the DB when EF builds everything out, but are necessary for providing EF context and allowing you to access related data. Because you're specifying the name of the class in **ProducerID**(which will be your foreign key), there's no need to explicitly deem it a foreign key. – jtucker13 Sep 21 '20 at 12:11
Seeing is believing when it comes to EF, so I'd recommend checking out the model context snapshot once you create the migration, or even run the update and view the objects in SQL to confirm. Here's a more detailed explanation on navigation properties as well: https://learn.microsoft.com/en-us/ef/ef6/fundamentals/relationships#:~:text=Navigation%20properties%20provide%20a%20way,relationship%20in%20which%20it%20participates. – jtucker13 Sep 21 '20 at 12:14

Entity Framework - Code First - Foreign Key Constraint

2 Answers2