0

How can I define a relationship for two tables where neither of the columns are a primary key and neither follow default conventions?

I am struggling to find the right combination of Data Annotations to override the default conventions and join these correctly without using Fluent API.

In this example, say I wanted to join SchoolId to SchoolNumberPS. The generated SQL would look like this;

SELECT *
FROM
    Students stu
    LEFT JOIN Schools sch ON stu.SchoolId = sch.SchoolNumberPS

Example tables:

public class Student
{
    public int Id { get; set; }
    public int StudentNumber { get; set; }
    public string? LastName { get; set; }
    public string? FirstName { get; set; }
    public int? SchoolId { get; set; }

    public virtual School? School { get; set; }
}

enter image description here

public class School
{
    public int Id { get; set; }
    public int SchoolNumber { get; set; }
    public int? SchoolNumberPS { get; set; }
    public string SchoolName { get; set; }

    public virtual ICollection<Student>? Students { get; set; }
}

enter image description here

I've tried multiple combinations of ForeignKey or InverseProperty, but cannot figure out how to simultaneously define both columns.

0 Answers0