We wanted to add the project options to a new entity, marked in red, that had a one-to-one relation to the existing BusinessProject entity. And because of the one-to-one relation between the business project and its options, we wanted to use the same table for both the business project and its options. In Entity Framework, this is called a table split. Unfortunately, we ran into serious problems using the table split approach, from migrations that couldn’t be created to Entity Framework not understanding that all three keys (the Project, BusinessProject and BusinessProjectOptions IDs) needed to use the same value. As soon as we'd fixed one problem, a new one popped up. In the end, we abandoned the table split and mapped the options to their own table.
But what went wrong? Had we found a bug in Entity Framework or simply reached the limits of its capabilities? Or had we made a mistake in the implementation? Let’s take a moment to find out.
To properly investigate the problem let's look at it step by step. First of all, we'll create a one-to-zero-or-one relation and perform some CRUD operations on it. Next, we'll implement a split table and perform the same type of CRUD operations. Finally, we'll combine these two components into what we tried to do and see if we can reproduce the problem.