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. All the examples use the code first approach, since that is easy to visualize, however you can easily define the relationship at runtime using fluent registration.

One-to-many relationships

• 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.

One-to-many relationships for entities with 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; }
}

One-to-many relationships for entities with composite keys

Usage

In order to use a JOIN statement and get the properties linked to the related entities populated, all you have to do is 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, unless the child entity has foreign key properties made out of non-nullable value types, in which case an INNER JOIN statement will be created. 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

    dbConnection.Find<Workstation>(statement => statement  
                .Where($"{nameof(Workstation.InventoryIndex):C} >= @WorkstationMinInventoryIndex")  
                .Include<Employee>(join => join
                        .InnerJoin()
                        .Where($"{nameof(Employee.FirstName"):C} = @EmployeeFirstName")
                        .OrderBy($"{nameof(Employee.LastName):C}"))
                .WithParameters(new {
                    WorkstationMinInventoryIndex = 10,
                    EmployeeFirstName = "John"
                 });

The Include method comes with its own set of options, not only controlling how the relationship is going to appear in the statement, but also offering an isolated context, linked to the related entity, for all the formattable strings passed in clauses such as Where and OrderBy.

Count record set

    dbConnection.Count<Employee>(statement => statement  
                .Include<Workstation>(join => join
                        .InnerJoin()
                        .Where($"{nameof(Workstation.Name):C} = @WorkstationName"))
                .WithParameters(new {WorkstationName = "Weaving Station #1"}));