DbContext Relationships

Advanced DbContext Concepts - Relationships

Relationships in Entity Framework are how you create foreign key relationships and easily do join queries.

ForeignKey

The ForeignKey annotation helps EntityFramework know how tables are related to each other.

An example that comes up all the time in database design. If you have specific statuses for a job (like New, In Progress, Complete, etc), store those values in their own table and then in the Job table you only need to store the Id value.

Example:

using namespace System.ComponentModel.DataAnnotations.Schema;
using namespace System.ComponentModel.DataAnnotations;
using namespace System.Collections.Generic;

Class JobStatusName{
    [Key()]
    [short]$Id

    [string]$Name
}

Class Job {
    [Key()]
    [int]$Id

    [short]$StatusId

    [DateTime]$LastModified
}

Here's a simple job table that stores status name in it's own table. Now, we should tell Entity Framework these two tables relate to each other!

using namespace System.ComponentModel.DataAnnotations.Schema;
using namespace System.ComponentModel.DataAnnotations;
using namespace System.Collections.Generic;

Class JobStatusName{
    [Key()]
    [short]$Id

    [string]$Name

    [ICollection[Job]]$Jobs
}

Class Job {
    [Key()]
    [int]$Id

    [short]$StatusId
    [ForeignKey("StatusId")]
    [JobStatusName]$StatusName

    [DateTime]$LastModified
}

So, what did I do? In JobStatusName I added an ICollection (array) of Jobs. This is because each status could have multiple jobs associated with it. If I have a New status, multiple jobs could be in New status.

In the Job class, I added a StatusName property that's the JobStatusName type. This is not an ICollection because each job can only be in one status at a time. This is considered a "OneToMany" relationship in EntityFrameworkCore. I also added the attribute [ForeignKey("StatusId)] which tells Entity Framework to get this value based on the StatusId key.

How does it know to map StatusId to Id in the JobStatusName table? It will always map to the Key on the other table.

Now, if you create a database with these classes, Entity Framework will set up a foreign key relationship. But, there's some other cool things you can do. You can now query through the relationship:

Search-EFPosh -Entity Job -Expression { $_.StatusName.Name -eq 'New' }

The above code will convert to this SQL query:

Select j.* FROM Job j
JOIN JobStatusName js ON js.Id = j.StatusId
WHERE js.Name = 'New'

You can also include JobStatusName with Job results:

Search-EFPosh -Entity Job -Include JobStatusName

Now when results come back, JobStatusName will have a value and you don't have to do another trip to the DB!

InverseProperty

ForeignKeys are cool, but what about a super advanced scenario?

using namespace System.ComponentModel.DataAnnotations.Schema;
using namespace System.ComponentModel.DataAnnotations;
using namespace System.Collections.Generic;

Class User {
    [Key()]
    [int]$Id

    [string]$Name

    [ICollection[Ticket]]$CreatedTickets
    [ICollection[Ticket]]$AssignedTickets
}

Class Ticket {
    [Key()]
    [int]$Id

    [int]$CreatedByUserId
    [ForeignKey("CreatedByUserId")]
    [User]$CreatedBy

    [int]$AssignedToUserId
    [ForeignKey("AssignedToUserId")]
    [User]$AssignedTo
}

The thing about navigation properties is, you can go from either direction. So, logically, it makes sense that we're giving Entity Framework enough information to look up who created a ticket, or who assigned a ticket. But what about the other way?

If I have a user object, how does entity framework know how to look up all the tickets they created, or all the assigned tickets? This is where InverseProperty comes in!

using namespace System.ComponentModel.DataAnnotations.Schema;
using namespace System.ComponentModel.DataAnnotations;
using namespace System.Collections.Generic;

Class User {
    [Key()]
    [int]$Id

    [string]$Name

    [InverseProperty("CreatedBy")]
    [ICollection[Ticket]]$CreatedTickets
    [InverseProperty("AssignedTo")]
    [ICollection[Ticket]]$AssignedTickets
}

Class Ticket {
    [Key()]
    [int]$Id

    [int]$CreatedByUserId
    [ForeignKey("CreatedByUserId")]
    [User]$CreatedBy

    [int]$AssignedToUserId
    [ForeignKey("AssignedToUserId")]
    [User]$AssignedTo
}

Add the InverseProperty attributes, tell it what property this works with, and now Entity Framework has enough information to be able to do the queries any direction you want!