Avatar of Philippe Renaud
Philippe Renaud
Flag for Canada asked on

Help with my Model Class for API and database tables creation

Hello EE,

I am creating a ASP.NET Core API project and it's so far its going well. I got my Model classes, my controllers and I am able to migrate and update-database so that it created my tables in SQL Azure database.

My question is, I am having a hard time to properly make it work when the time comes that I have some relationships (foreign keys)

Example I want to create those Apis / Tables for :

I want a Customer table (ID, firstname, lastname)

I want a Profession table (ID, customerID, location, title) and 1 customer could have many profession.

I want a ProfessionFavorites Table (ID, customerID, professionID) .. so 1 customer could many favorites

My question is, i dont know how to write those in my Model class so that all foreign keys properly make its way to the database, 

Could you help me write the Model class for all three ? Also, the next goal is to Scaffold all this to create the controller for API

Microsoft SQL ServerC#ASP.NETCloud Computing* ASP.NET Core

Avatar of undefined
Last Comment
Philippe Renaud

8/22/2022 - Mon

not quite sure if I hit the point...
A foreign key contraint inside the database is just a constaint, so you can not write a record, if the dependent value is not provided.
To put the correct relation into your database, you have to take care yourself. So you have to find out the related IDs for the foreign records and then save them into the according table.
So several records in the profession table which the same Customer ID
And several records in the ProfessionFavorites table with the corresponding CustomerID and professionID.
From the construction I would interpret profession as a property of the customer.
So you have a customer table with customerID
You have a profession table with a professionID (definition of profession)
You have a customer_profession table, which contain the CutomerID and professionID to put them together.

With the favorites you can either extend the customer_profession table with a boolean tag to declare an existing profession as favorite... (means a few of existing  professions are favorites)
or, if there are favorites, where you do not have a profession for...
another customer_favorites table, which contain the CutomerID and professionID

Chinmay Patel

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Philippe Renaud

Hi Patel,  many thanks those were the kind of answers I was looking for.

For the Favorite section, im trying to say that 1 customer could have many favorites
so im trying to say that:  Customer ID 1  could put in favorites Profession ID 100, 101 and 102 and 103

so table could  look like :

ID   custID   professID
1       1        101
2       1        102
3       1        103

Also last question.. in order for the database to do all the proper foreign keys, i ahve nothin else to do? the management console by typing update-database should do all this, am i correct ?

I remember I think i had an issue with the "ProfessionFavorite" i was having an error of 'cannot cascade delete' blabla

Hello Philippe,

in general yes, I just would design it a little bit different...

Customer (Core data)
- CustomerID
- Customer Name

Profession (Core data)
- ProfessionID
- Profession Name

Customer Profession (Assingment data)
- ID
- Customer ID
- Profession ID

Customer Profession Favorite (Assingment data)
- ID
- Customer ID
- Profession ID

and as you can see, the Profession and Favorites tables are the same, you could also define just one table

Customer Profession (Assingment data)
- ID
- Customer ID
- Profession ID
- IsFavorite (yes / no)

A cascade delete is not necessary as you do not want to delete a cutomer or profession, if you delete an assignement.
The foreign key relation ist Customer ID to Cutomer ID and ProfessionID to ProfessionID, where the assignement tables are the N side and the core tables the 1 side.

Your help has saved me hundreds of hours of internet surfing.
Philippe Renaud

ok thank you both.
Philippe Renaud

Hi Patel, another new question you may know the answer :