JOINs

This feature could see breaking changes introduced while version 2.4.0 is passing through the BETA period

FastCRUD supports JOINs in operations such as Get, Find and Countby following an opt-in approach. A maximum number of 7 entity types can participate in a query.

Assuming we're dealing with two tables linked with a foreign key, their mapped entities taking part in a one-to-many relationship would look like the following:

public class Workstation
{
  [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; }
}

public class Employee
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public Guid EmployeeId { get; set; }
  public string LastName { get; set; }
  public string FirstName { get; set; }
  public DateTime BirthDate { get; set; }
  // marks a foreign key and also points to the property holding the related entity
  [ForeignKey("Workstation")] 
  public long? WorkstationId { get; set; }
  public Workstation Workstation { get; set; }
}

In this case, we used attributes to define the mappings. You also have the option of defining mappings at runtime or in separate metadata classes.

The foreign key attribute marks the relationship between the two entities. It is also meant to point to the property that will hold the instance of the related entity when FastCrud is instructed to do so. Notice that only the child entity requires to have the relationship defined. The parent entity only holds an optional property of type IEnumerable<TChildEntity>.

When a composite key is present on the parent entity, it is recommended to use extra Column(Order = x) attributes on the primary keys of the main entity with order values matching the ones set up in the extra attributes on the foreign key properties on the child entity.

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)

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.

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

Select All

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

Select record set

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.

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

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