I am loving how much time Entity Framework (EF) is saving me in developing my database. It’s easy to abstract ideas into code and let it handle the SQL. I do worry a bit that I will start to get rust on my raw SQL knowledge, so I will have to make a note to sharpen that on my own. Still, for what it can do, I think it’s going to be well worth it to continue learning it as I get this D&D project off the ground.
That said, I need to always keep in mind a fundamental property of how foreign keys work. That is, when I am defining a foreign key relationship by saying a class will have another class in it, I am not actually getting all the members of that class as I would expect if I was just looking at the code. Take this Quest class for instance:
When I look at this, I would normally expect a quest to have all the attributes of the foreign key relationships and to use them as such, but of course, a SQL table can’t contain objects themselves directly, so I wouldn’t have a full Location with whatever functions came with it. What do I have? Simple, I have a FK that links to whatever Location is using as its primary key.
Whatever value is under the [Key] annotation, in this case Id, will be used as a foreign key. You’ll see similarly that location has a Campaign class on it as well, this will also have an Foreign Key relationship to campaign’s foreign key. You can start to quickly see how this could be much more difficult to manage in the tables themselves than through code as the application grows.
Here’s what the location object looks like in SQL:
You may have noticed that we defined Locations as having a List of Quests, but no such list appears on SQL. This is because it is just a navigation property to represent the relationship between Location and Quests, not to define the structure of the table itself. There won’t be a foreign key on the location table anywhere for the quests. By including it here, it gives us access to lazy and eager loading and other EF features. It’s not strictly necessary, but why not save ourselves some work down the road.
And that’s it. There’s nothing complicated here, I just wanted to express the basics of defining foreign keys through navigation properties in Entity Framework; there’s going to be a lot of need for that in most applications.