JOINs

FastCRUD supports JOINs in operations such as Get, Find and Countby following an opt-in approach. There is no restriction on the number of entities that you can use in a statement.

Usage

In order to use a JOIN statement and get the properties linked to the related entities populated, use the Include method in the statement options. You can do this when querying for entities on either end of a relationship.

Select by primary key(s)

dbConnection.Get(new Workstation {WorkstationId = 10}, 
                 statement => statement.Include<Employee>());

Without specifying the type of the join, a LEFT OUTER JOIN is going to be used by default. To avoid any confusion though, it is recommended that you explicitly set the desired type of join via calls to InnerJoin or LeftOuterJoin in the join statement options.

Select All

dbConnection.Find<Workstation>(statement => statement
                .Include<Employee>(join => join.InnerJoin()));

Select record set

var queryParams = new {
  EmployeeFirstName = "Jane",
  EmplopyeeLastName = "Doe",
  WorkstationMinInventoryIndex = 5
}

dbConnection.Find<Workstation>(statement => statement  
      .WithAlias("ws")
      .Include<Employee>(join => join.InnerJoin().WithAlias("em")
      .Where($@"
             {nameof(Employee.FirstName):of em} = {nameof(queryParams.EmployeeFirstName):P}
             AND {nameof(Employee.LastName):of em} = {nameof(queryParams.EmployeeLastName):P}
             AND {nameof(Workstation.InventoryIndex):of ws} = {nameof(queryParams.WorkstationMinInventoryIndex):P}
       ")
       .WithParameters(queryParams)
       .OrderBy($"{nameof(Employee.LastName):of em}"));

The Include method comes with its own set of options, one of the most important one being the alias. It is very important, whenever you work with a number of entities in a join, to assign aliases to every one of them.

FastCrud will take care of populating the navigation properties with unique entities, including the children collection properties.

Count record set

var queryParams = new {
  EmployeeFirstName = "Jane",
}

dbConnection.Count<Employee>(statement => statement  
      .WithAlias("em")
      .Include<Workstation>(join => join.InnerJoin().WithAlias("ws"))
      .Where($"{nameof(Employee.FirstName):of em} = {nameof(queryParams.EmployeeFirstName):P}")
      .WithParameters(queryParams));

Registration

One-to-many relationships

For attribute registration:

• The child entity requires to have the ForeignKey attribute applied on the foreign key(s), pointing to the navigation property.
• The parent entity should have a property of type IEnumerable<TChildEntity>.

[Table("Workstations")]
public class WorkstationDbEntity
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public long WorkstationId { get; set; }
  public string Name { get; set; }
  public int AccessLevel { get; set; }
  public int InventoryIndex { get; set; }

  public IEnumerable<Employee>? Employees { get; set; }
}

[Table("Employees")]
public class EmployeeDbEntity
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public Guid EmployeeId { get; set; }
  public string LastName { get; set; }
  public string FirstName { get; set; }
  public DateTime BirthDate { get; set; }

  [ForeignKey(nameof(Workstation)] 
  public long? WorkstationId { get; set; }
  public WorkstationDbEntity? Workstation { get; set; }
}

The foreign key attribute marks the relationship between the two entities. It is also meant to point to the navigation property that will hold the instance of the related entity when FastCrud is instructed to do so.

In case you're using fluent registration, add your relationships when setting up your entities:

OrmConfiguration.RegisterEntity<EmployeeDbEntity>()
    .SetTableName("Employees")
    ....
    .SetChildParentRelationship<WorkstationDbEntity>(
        employee => employee.Workstation,
        employee => employee.WorkstationId);

OrmConfiguration.RegisterEntity<WorkstationDbEntity>()
    .SetTableName("Workstations")
    ....
    .SetParentChildrenRelationship<EmployeeDbEntity>(
        workstation => workstation.Employees);

Composite keys

When a composite key is present on the parent entity, you have to use the extra Column(Order = x) attributes on the primary keys of the parent entity with order values matching the ones set up on the foreign key properties on the child entity, otherwise you run into the risk that they won't be matched properly.

[Table("Students")]
public class StudentDbEntity
{
  [Key]
  [Column(Order = 1]
  public string FirstName { get; set; }

  [Key]
  [Column(Order = 2)]
  public string LastName { get; set; }

  public IEnumerable<BookDbEntity>? Books { get; set; }
}

[Table("Books")]
public class BookDbEntity
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public Guid BookId { get; set; }

  public string BookName { get; set; }

  [ForeignKey(nameof(Student))]
  [Column(Order = 1]
  public string FirstName { get; set; }

  [Key]
  [Column(Order = 2)]
  public string LastName { get; set; }

  public StudentdbEntity? Student { get; set; }
}

Referencing the same entity multiple times

When we have to deal with multiple relationships to the same entity, we have to make use of the InverseProperty attribute on the navigation property denoting the collection of child entities pointing to the corresponding navigation property on the child entity.

[Table("Students")]
public class StudentDbEntity
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int StudentId { get; set; }
  
  public string FirstName { get; set; }
  public string LastName { get; set; }

  [ForeignKey(nameof(OnlineTeacher))]
  public int OnlineTeacherId { get; set; }
  [ForeignKey(nameof(ClassroomTeacher)]
  public int ClassroomTeacherId {get; set; }

  public TeacherDbEntity? OnlineTeacher { get; set; }
  public TeacherDbEntity? ClassroomTeacher { get; set; }
}

[Table("Teachers")]
public class TeacherDbEntity
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int TeacherId { get; set; }

  [InverseProperty(nameof(Student.OnlineTeacher)]
  public IEnumerable<StudentDbEntity> OnlineStudents { get; set; }
  [InverseProperty(nameof(Student.ClassroomTeacher)]
  public IEnumerable<StudentDbEntity> ClassroomStudents {get; set; }
}

For fluent registration, you have pass the referenced properties when you register the parent-child relationships:

OrmConfiguration.RegisterEntity<StudentDbEntity>()
    .SetTableName("Students")
    ....
    .SetChildParentRelationship<TeacherDbEntity>(
        student => student.OnlineTeacher,
        student => student.OnlineTeacherId)
    .SetChildParentRelationship<TeacherDbEntity>(
        student => student.ClassroomTeacher,
        student => student.ClassroomTeacherId);

OrmConfiguration.RegisterEntity<TeacherDbEntity>()
    .SetTableName("Teachers")
    ....
    .SetParentChildrenRelationship<StudentDbEntity>(
        teacher => teacher.OnlineStudents,
        student => student.OnlineTeacherId)
    .SetParentChildrenRelationship<StudentDbEntity>(
        teacher => teacher.ClassroomStudents,
        student => student.ClassroomTeacherId);

In the query, we have to provide additional information in order to properly identify the relationship involved in the JOIN.

databaseConnection.Find<StudentDbEntity>(statement => statement
    .WithAlias("student")
    .Include<TeacherDbEntity>(join => join.WithAlias("onlineTeacher")
                                          .Referencing<StudentDbEntity>(relationship => relationship
                                                     .FromProperty(student => student.OnlineTeacher)
                                                     .ToProperty(teacher => teacher.OnlineStudents)))
    .Include<TeacherDbEntity>(join => join.WithAlias("classroomTeacher")
                                          .Referencing<StudentDbEntity>(relationship => relationship
                                                     .FromProperty(student => student.ClassroomTeacher)
                                                     .ToProperty(teacher => teacher.ClassroomStudents)))
    .Where($"{nameof(Student.FirstName):of student} = {nameof(queryParams.FirstName):P}");

One-to-one relationships

In a one-to-one relationship, the child entity shares the same primary keys as the parent entity. We'll use the ForeignKey and the InverseProperty to properly map such a relationship.

[Table("Employees")]
public class EmployeeDbEntity
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int EmployeeId { get; set; }
  
  public string FirstName { get; set; }
  public string LastName { get; set; }

  [InverseProperty(nameof(BadgeDbEntity.Employee))]
  public BadgeDbEntity? Badge { get; set; }

}

[Table("Badges")]
public class BadgeDbEntity
{
  [Key]
  [ForeignKey(Employee)]
  public int EmployeeId { get; set;}

  public string AccessCode {get; set; }  

  public EmployeeDbEntity? Employee { get; set; }
}

The query will look normal in this case though.

databaseConnection.Find<EmployeeDbEntity>(options => options.Include<BadgeDbEntity>());