Which approach is better to use in EF Core?-CodePudding

I have two objects. Team and a Player. One Player has one Team. One Team has many players.

I could design this relationship in two ways :

Without using FK

Player

int Id {get;set;}
int TeamId {get;set;}

Team

int Id;

and whenever I wanted to get the list of players playing for the team with id of 123, I would query like this :

players.Where(p=>p.TeamId == 123).ToList();

Option #2 : With using FKs.

Player

int Id {get;set;}

Team

int Id {get;set} 
public ICollection Players {get;set;}

Getting the players :

team.Players;

Which one should I use?

CodePudding user response：

It depends whether you need both Team and Players details or just Players details belonging to the certain team.

If you want to load team data and all team players, you should query Teams to find a single team by TeamId and Include all the Players.

If you just need a list of players without any particular Team data, you should query Players by TeamId. This way you don't pull the Team related data from the database which you won't use anyway and you're avoiding the join between the tables.

CodePudding user response：

Your database model might not mirror your domain model and that's perfectly fibe, being said this I would use both, in order to make the right queries when you need a different set of data. You might encounter next week you want to query something different and the way you left aside is more convenient. Your write and read models don't need to be the same too. There are many reasons why you would want to have both...

CodePudding user response：

I recommend using Relationships as described in the official Microsoft documentation, i.e., describe your models as if you didn't know you were using EF core. One advantage is, that if you are using a relational database in the background, it makes sure that your reference integrity remains intact. Another one is, that you can easily load related entities in a single statement using Include.

In that sense I recommend against managing foreign keys to entities yourself as it will lead to data integrity problems and the need for lazy loading in the long run as you can't include related entities using Include. Let EF Core handle (foreign and primary) key management in the background using its defaults and own conventions.