Entity Framework LeftJoinAttribute [Mapping]

(from the official guide)

Where we are querying database, we tend to make many joins because of relations. Most of these joins are LEFT or INNER joins.

With Serenity entities, you'll usually be using LEFT JOINs.

Database admins prefers to define views to make it easier to query a combination of multiple tables, and to avoid writing these joins again and again.

Serenity entities can be used just like SQL views, so you can bring in columns from other tables to an entity, and query it as if they are one big combined table.

public class CustomerRow : Row
{
   [ForeignKey("Cities", "Id"), LeftJoin("c")]
   public Int32? CityId
   {
      get { return Fields.CityId[this]; }
      set { Fields.CityId[this] = value; }
   }

   [Expression("c.[Name]")]
   public string CityName
   {
      get { return Fields.CityName[this]; }
      set { Fields.CityName[this] = value; }
   }

Here we specified that Cities table should be assigned alias c when joined, and its join type should be LEFT JOIN. The join ON expression is determined as c.[Id] == T0. [CityId] with some help from [ForeignKeyAttribute](ForeignKeyAttribute [Mapping]) attribute.

LEFT JOIN is preferred as it allows to retrieve all records from left table, Customers, even if they don't have a CityId set.

CityName is a view field (not actually a column of Customer table), which has an expression c.Name. It is clear that CityName originates from Name field in Cities table.

Now, if we wanted to select city names of all customers, our query text would be:

SELECT
c.Name AS [CityName]
FROM Customer T0
LEFT JOIN Cities c ON (c.[Id] = T0.CityId)

What if we don't have a CountryId field in Customer table, but we want to bring Country names of cities through CountryId field in city table?

public class CustomerRow : Row
{
   [ForeignKey("Cities", "Id"), LeftJoin("c")]
   public Int32? CityId
   {
      get { return Fields.CityId[this]; }
      set { Fields.CityId[this] = value; }
   }

   [Expression("c.[Name]")]
   public string CityName
   {
      get { return Fields.CityName[this]; }
      set { Fields.CityName[this] = value; }
   }

   [Expression("c.[CountryId]"), ForeignKey("Countries", "Id"), LeftJoin("o")]
   public Int32? CountryId
   {
      get { return Fields.CountryId[this]; }
      set { Fields.CountryId[this] = value; }
   }
   
   [Expression("o.[Name]")]
   public string CountryName
   {
      get { return Fields.CountryName[this]; }
      set { Fields.CountryName[this] = value; }
   }
}

This time we did a LEFT JOIN on CountryId field in Cities table. We assigned o alias to Countries table and bring in the name field from it.

You can assign any table alias to joins as long as they are not reserved words, and are unique between other joins in the entity. Sergen generates aliases like jCountry, but you may rename them to shorter and more natural ones.

Let's select CityName and CountryName fields of all Customers:

SELECT
c.[Name] AS [CityName],
o.[Name] AS [CountryName]
FROM Customer T0
LEFT JOIN Cities c ON (c.[Id] = T0.CityId)
LEFT JOIN Countries o ON (o.[Id] = c.[CountryId])

We'll see how to build such queries in FluentSQL chapter.

So far, we used LeftJoin attribute with properties that has a ForeignKey attribute with them.

It is also possible to attach LeftJoin attribute to entity classes. This is useful for joins without a corresponding field in main entity.

For example, let's say you have a CustomerDetails extension table that stores some extra details of customers (1 to 1 relation). CustomerDetails table has a primary key, CustomerId, which is actually a foreign key to Id field in Customer table.

[LeftJoin("cd", "CustomerDetails", "cd.[CustomerId] = T0.[Id]")]
public class CustomerRow : Row
{
   [Identity, PrimaryKey]
   public Int32? Id
   {
      get { return Fields.Id[this]; }
      set { Fields.Id[this] = value; }
   }
   
   [Expression("cd.[DeliveryAddress]")]
   public string DeliveryAddress
   {
      get { return Fields.DeliveryAddress[this]; }
      set { Fields.DeliveryAddress[this] = value; }
   }

And here what it looks like when you select DeliveryAddress:

SELECT
cd.[DeliveryAddress] AS [DeliveryAddress]
FROM Customer T0
LEFT JOIN CustomerDetails cd ON (cd.[CustomerId] = T0.[Id])

(end test from official guide)

Or, for cases in which primare key is composed of two or more field, with join with multiple Keys (see issue #560):

Main table definition:

public sealed class TableONERow : Row, IIdRow, INameRow
{
   [DisplayName("IdOne"), Column("IDONE"), PrimaryKey]
   public Int32? IdOne
   {
      get { return Fields.IdOne[this]; }
      set { Fields.IdOne[this] = value; }
   }

   [DisplayName("IdDateFrom"), Column("IDDateFrom"), PrimaryKey]
   public DateTime? IdDateFrom
   {
      get { return Fields.IdDateFrom[this]; }
      set { Fields.IdDateFrom[this] = value; }
   }

   [DisplayName("Id"), Column("ID"), Identity]
   public Int32? Id
   {
      get { return Fields.Id[this]; }
      set { Fields.Id[this] = value; }
   }

Other table:

[LeftJoin("jONE", "[dbo].[TableONE]", "T0.[IDTWO] = jONE.[IDONE] and T0.[IDDateFrom] = jONE.[IDDateFrom]")]
public sealed class TableTWORow : Row, IIdRow
{
   [DisplayName("IdTwo"), Column("IDTWO"), PrimaryKey, ForeignKey("[dbo].[TableONE]", "IDONE")]        
   public Int32? IdTwo
   {
      get { return Fields.IdTwo[this]; }
      set { Fields.IdTwo[this] = value; }
   }

   [DisplayName("IdDateFrom"), Column("IDDateFrom"), PrimaryKey, ForeignKey("[dbo].TableONE]", "IDDateFrom")]
   public DateTime? IdDateFrom
   {
      get { return Fields.IdDateFrom[this]; }
      set { Fields.IdDateFrom[this] = value; }
   }

   [DisplayName("IdCounter"), Column("IDCounter"), PrimaryKey]
   public Int32? IdCounter
   {
      get { return Fields.IdCounter[this]; }
      set { Fields.IdCounter[this] = value; }
   }

   [DisplayName("Id"), Column("ID"), Identity]
   public Int32? Id
   {
      get { return Fields.Id[this]; }
      set { Fields.Id[this] = value; }
   }