JOINs

FastCRUD supports JOINs in operations such as Get, Find and Count by following an opt-in approach. There is no restriction on the number of entities that you can use in a statement. Keep in mind that navigation properties are always excluded from Insert and Update operations. It is your responsibility to perform manual changes separately on the related entities.

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));

In this case, when the COUNT is being used having entities in a JOIN, a DISTINCT clause is also being added, to ensure that the behavior matches the one of a regular select query, where the matched entities are being uniquely set up in the navigation properties.

Custom JOIN ON clause

If you don't have a relationship registered, or you need a completely custom ON clause for the JOIN, you can provide your own SQL:

databaseConnection.Find<EmployeeDbEntity>(options => options
    .WithAlias("em")
    .Include<WorkstationDbEntity>(join => join
      .WithAlias("ws")                        
      .On($"{nameof(EmployeeDbEntity.WorkstationId):of em} = {nameof(WorkstationDbEntity.WorkstationId):of ws}")
      .MapResults(true)                                                                                                          
      .Referencing<EmployeeDbEntity>(relationship => relationship
          .FromAlias("em")
          .FromProperty(employee => employee.Workstation)
          .ToProperty(workstation => workstation.Employees))));

When using the On override, you're forfeiting any relationships you might have registered. If you do wish to map the results onto some properties on the types present on either side of the relationship, you're gonna have to manually add them into the mix:

databaseConnection.Find<EmployeeDbEntity>(options => options
    .WithAlias("em")
    .Include<WorkstationDbEntity>(join => join
      .WithAlias("ws")                        
      .On($"{nameof(EmployeeDbEntity.WorkstationId):of em} = {nameof(WorkstationDbEntity.WorkstationId):of ws}")
      .MapResults(true)                                                                                                          
      .Referencing<EmployeeDbEntity>(relationship => relationship
          .FromAlias("em")
          .FromProperty(employee => employee.Workstation)
          .ToProperty(workstation => workstation.Employees))));

Pagination

In case of child-parent relationships, there is nothing special when it comes to constructing your query. Use FastCrud's .OrderBy, .Skip and .Top as normal:

var searchParams = new {
  FirstName: "John"
}
var records = dbConnection.FindAsync<Roles>(statement => statement
    .WithAlias("r")
    .Include<User>(join => join.InnerJoin().WithAlias("u"))
    .Where($"{nameof(User.FirstName):of u} = {nameof(searchParams.FirstName):P}}")
    .WithParameters(searchParams)
    .OrderBy($"{nameof(user.LastName):of u}")
    .Skip(20)
    .Top(10)
);

Same can't be said about parent-children relationships. Some parent entities will come back duplicated because of their children. You might be thinking you're paginating the parents, but in fact you're paginating relationships (e.g. parent#1, child#1, parent#1, child#2, parent#2, child#3, etc). FastCrud does help by grouping the rows by the parent entities and adding the children to the navigation properties, but your .Skip and .Top won't be accurate. The solution to this problem is to move all the pagination logic in a subquery:

var records = dbConnection.FindAsync<User>(x => x
    .Include<Roles>(o => o.LeftOuterJoin())
    .Where($@"{nameof(User.Id):TC} IN 
      (
          SELECT {nameof(User.UserId):C}
		  FROM {nameof(User):T}
		  WHERE {nameof(User.FirstName):C} = {nameof(myParamObject.FirstName):P}
		  -- ORDER BY is required in case of paginations
		  ORDER BY {nameof(User.LastName):C}
		  -- the paging comes here
		  OFFSET 10 ROWS          -- skip 10 rows
		  FETCH NEXT 10 ROWS ONLY -- take 10 rows
       )")
    .WithParameters(myParamObject)
    // repeat the OrderBy to be consistent with the paginated records
    .OrderBy($"nameof(User.LastName):TC")
);

Don't forget that you can always access other tables and columns in the subquery by using {Sql.Entity<TOther>(other => other.Prop):format_specifier} as described here.

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 to 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>());