Introduction
In order to test out IHP, I've converted part of the Contoso University tutorial for ASP.NET Core to IHP.
This step in the tutorial shows a data model diagram. The part that I'll focus on in this question involves Instructor and OfficeAssignment which have a one-to-zero-or-one relationship, according to that page.
Instructor
The model in C# for Instructor is:
public class Instructor
{
public int ID { get; set; }
[Required]
[Display(Name = "Last Name")]
[StringLength(50)]
public string LastName { get; set; }
[Required]
[Column("FirstName")]
[Display(Name = "First Name")]
[StringLength(50)]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
[Display(Name = "Hire Date")]
public DateTime HireDate { get; set; }
[Display(Name = "Full Name")]
public string FullName
{
get { return LastName + ", " + FirstMidName; }
}
public ICollection<Course> Courses { get; set; }
public OfficeAssignment OfficeAssignment { get; set; }
}
This resulted in the following table in sqlite:
CREATE TABLE IF NOT EXISTS "Instructor" (
"ID" INTEGER NOT NULL CONSTRAINT "PK_Instructor" PRIMARY KEY AUTOINCREMENT,
"LastName" TEXT NOT NULL,
"FirstName" TEXT NOT NULL,
"HireDate" TEXT NOT NULL
);
So in IHP, I used the following:
CREATE TABLE instructors (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
last_name TEXT NOT NULL,
first_mid_name TEXT NOT NULL,
hire_date DATE NOT NULL
);
OfficeAssignment
The model in C# for OfficeAssignment is:
public class OfficeAssignment
{
[Key]
public int InstructorID { get; set; }
[StringLength(50)]
[Display(Name = "Office Location")]
public string Location { get; set; }
public Instructor Instructor { get; set; }
}
This resulted in the following table in sqlite:
CREATE TABLE IF NOT EXISTS "OfficeAssignment" (
"InstructorID" INTEGER NOT NULL CONSTRAINT "PK_OfficeAssignment" PRIMARY KEY,
"Location" TEXT NULL,
CONSTRAINT "FK_OfficeAssignment_Instructor_InstructorID" FOREIGN KEY ("InstructorID") REFERENCES "Instructor" ("ID") ON DELETE CASCADE
);
So in IHP, I used the following:
CREATE TABLE office_assignments (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
instructor_id UUID NOT NULL,
"location" TEXT NOT NULL
);
CREATE INDEX office_assignments_instructor_id_index ON office_assignments (instructor_id);
ALTER TABLE office_assignments ADD CONSTRAINT office_assignments_ref_instructor_id FOREIGN KEY (instructor_id) REFERENCES instructors (id) ON DELETE NO ACTION;
Difference in columns
Note that the ASP.NET Core version of the table for OfficeAssignment only has the following columns:
InstructorID
Location
whereas, the IHP table has:
id
instructor_id
location
I.e. it has an id column. I left it in there because it's added by default by the IHP schema editor.
One-to-zero-or-one relationship
Given the generated code for Instructor in Types.hs:
data Instructor' officeAssignments = Instructor {
id :: (Id' "instructors"),
lastName :: Text,
firstMidName :: Text,
hireDate :: Data.Time.Calendar.Day,
officeAssignments :: officeAssignments,
meta :: MetaBag
} deriving (Eq, Show)
it seems like IHP is interpreting things such that:
- One instructor can have many office assignments
(I.e. the field officeAssignments is plural.)
However, according to the diagram in the ASP.NET Core tutorial, an instructor can have 0 or 1 office assignments. (I.e. they have an office or not.)
It seems that Entity Framework Core gets the signal that there should be at most one office assignment per instructor from the presence of the following navigation property on Instructor:
public OfficeAssignment OfficeAssignment { get; set; }
UPDATE: this has been confirmed. See section below titled Update 1.
Desired semantics - create instructor with an office
In the C# app, let's say I create an Instructor, specifying an office:
We see the following in sqlite:
sqlite> SELECT * FROM Instructor; SELECT * FROM OfficeAssignment;
ID LastName FirstName HireDate
-- ----------- --------- -------------------
1 Fakhouri Fadi 2002-07-06 00:00:00
2 Harui Roger 1998-07-01 00:00:00
3 Kapoor Candace 2001-01-15 00:00:00
4 Zheng Roger 2004-02-12 00:00:00
5 Abercrombie Kim 1995-03-11 00:00:00
10 Curry Haskell 1920-01-01 00:00:00
InstructorID Location
------------ ------------
2 Gowan 27
3 Thompson 304
10 Haskell Room
Desired semantics - create instructor without an office
Now, in the C# app, let's create an instructor and not specify an office:
We see the following in sqlite:
sqlite> SELECT * FROM Instructor; SELECT * FROM OfficeAssignment;
ID LastName FirstName HireDate
-- ----------- --------- -------------------
1 Fakhouri Fadi 2002-07-06 00:00:00
2 Harui Roger 1998-07-01 00:00:00
3 Kapoor Candace 2001-01-15 00:00:00
4 Zheng Roger 2004-02-12 00:00:00
5 Abercrombie Kim 1995-03-11 00:00:00
10 Curry Haskell 1920-01-01 00:00:00
11 Church Alonzo 1940-01-01 00:00:00
InstructorID Location
------------ ------------
2 Gowan 27
3 Thompson 304
10 Haskell Room
11
Interestingly, if I edit an instructor and leave the office blank:
we see the following in sqlite:
sqlite> SELECT * FROM Instructor; SELECT * FROM OfficeAssignment;
ID LastName FirstName HireDate
-- ----------- --------- -------------------
1 Fakhouri Fadi 2002-07-06 00:00:00
2 Harui Roger 1998-07-01 00:00:00
3 Kapoor Candace 2001-01-15 00:00:00
4 Zheng Roger 2004-02-12 00:00:00
5 Abercrombie Kim 1995-03-11 00:00:00
10 Curry Haskell 1920-01-01 00:00:00
11 Church Alonzo 1940-01-01 00:00:00
InstructorID Location
------------ ------------
2 Gowan 27
3 Thompson 304
I.e. the OfficeAssignment is removed.
This code is what implements that:
if (String.IsNullOrWhiteSpace(instructorToUpdate.OfficeAssignment?.Location))
instructorToUpdate.OfficeAssignment = null;
Question
Is this a good way to set things up on the IHP side to model the C# app? Or is there something I should change on the IHP side to more closely model this one-to-zero-or-one relationship?
I looked through the Relationships section of the IHP manual, but didn't notice anything regarding this sort of one-to-zero-or-one relationship. Would just like to make sure I have the models setup correctly before I venture off into the forms side of things.
Project repository
In case it's helpful, the project repository with the above is at:
https://github.com/dharmatech/ContosoUniversityIhp/tree/2021-09-04-02-queryOr-fix
(It's very messy as it's for experimentation.)
Note
I realize this is a complex question but my hope is that it can serve as an example for folks setting up a similar relationship scenario in IHP in the future.
Update 1
The Entity Framework Core documentation has the following section:
It mentions:
One to one relationships have a reference navigation property on both sides. They follow the same conventions as one-to-many relationships, but a unique index is introduced on the foreign key property to ensure only one dependent is related to each principal.
So that is indeed what we see in the C# models for Instructor and OfficeAssignment. So I guess the question is, does IHP explicitly support this sort of relationship? And if not, what's a good way to simulate it given the current mechanisms.
Possible model for Instructor
It seems like in order for Instructor to model the fact that they can have one or zero offices, the generated model should have a field that's something like this:
officeAssignment :: Maybe OfficeAssignment
as mentioned earlier, it's currently as follows:
data Instructor' officeAssignments = Instructor {
id :: (Id' "instructors"),
lastName :: Text,
firstMidName :: Text,
hireDate :: Data.Time.Calendar.Day,
officeAssignments :: officeAssignments,
meta :: MetaBag
} deriving (Eq, Show)
Update 2
If we look at office_assignments table on the IHP side:
CREATE TABLE office_assignments (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
instructor_id UUID NOT NULL,
"location" TEXT NOT NULL
);
it's clear that since there's an id column, we can have any number of office_assignment rows for a given instructor_id.
However, if we look at the C# version:
CREATE TABLE IF NOT EXISTS "OfficeAssignment" (
"InstructorID" INTEGER NOT NULL CONSTRAINT "PK_OfficeAssignment" PRIMARY KEY,
"Location" TEXT NULL,
CONSTRAINT "FK_OfficeAssignment_Instructor_InstructorID" FOREIGN KEY ("InstructorID") REFERENCES "Instructor" ("ID") ON DELETE CASCADE
);
we note:
- There is no
idcolumn. - There is only an
InstructorIDcolumn. InstructorIDis thePRIMARY KEY- Thus, this seems to enforce the fact that there can only be one row in
OfficeAssignmentsfor any givenInstructor.
So, perhaps it's as simple as changing the IHP schema to:
CREATE TABLE office_assignments (
instructor_id UUID PRIMARY KEY NOT NULL,
"location" TEXT NOT NULL
);
CREATE INDEX office_assignments_instructor_id_index ON office_assignments (instructor_id);
ALTER TABLE office_assignments ADD CONSTRAINT office_assignments_ref_instructor_id FOREIGN KEY (instructor_id) REFERENCES instructors (id) ON DELETE NO ACTION;
Result
OK, using the schema editor I updated office_assignments such that it now looks like this:
CREATE TABLE office_assignments (
instructor_id UUID PRIMARY KEY NOT NULL,
"location" TEXT NOT NULL
);
Here's the result during compilation:
[ 4 of 23] Compiling Generated.Types ( build/Generated/Types.hs, interpreted )
build/Generated/Types.hs:133:71: error:
• Couldn't match type ‘"instructors"’ with ‘"office_assignments"’
arising from a use of ‘QueryBuilder.filterWhere’
• In the fifth argument of ‘Instructor’, namely
‘(QueryBuilder.filterWhere
(#instructorId, id) (QueryBuilder.query @OfficeAssignment))’
In the expression:
Instructor
id lastName firstMidName hireDate
(QueryBuilder.filterWhere
(#instructorId, id) (QueryBuilder.query @OfficeAssignment))
def {originalDatabaseRecord = Just (Data.Dynamic.toDyn theRecord)}
In an equation for ‘theRecord’:
theRecord
= Instructor
id lastName firstMidName hireDate
(QueryBuilder.filterWhere
(#instructorId, id) (QueryBuilder.query @OfficeAssignment))
def {originalDatabaseRecord = Just (Data.Dynamic.toDyn theRecord)}
|
133 | let theRecord = Instructor id lastName firstMidName hireDate (QueryBuilder.filterWhere (#instructorId, id) (QueryBuilder.query @OfficeAssignment)) def { originalDatabaseRecord = Just (Data.Dynamic.toDyn theRecord) }
| ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Failed, three modules loaded.
build/Generated/Types.hs:234:20: error:
• Couldn't match type ‘OfficeAssignment' instructorId0
-> instructorId0’
with ‘Id' "office_assignments"’
arising from a use of ‘QueryBuilder.filterWhere’
• In the second argument of ‘(|>)’, namely
‘QueryBuilder.filterWhere (#instructorId, instructorId)’
In the expression:
builder |> QueryBuilder.filterWhere (#instructorId, instructorId)
In an equation for ‘QueryBuilder.filterWhereId’:
QueryBuilder.filterWhereId instructor_id builder
= builder |> QueryBuilder.filterWhere (#instructorId, instructorId)
|
234 | builder |> QueryBuilder.filterWhere (#instructorId, instructorId)
| ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
