Root schema. Building paths to columns

Introduction

The root schema is a schema (i.e. a database table) in relation to which the paths are built to all query columns, including the columns of the connected tables.

When building the column paths, the connections are established via the lookup fields. You can build an arbitrary name of a column added to the query as a chain of interconnected items, each of which represents a “context’ of a specific schema linked to the previous schema via an external key (fig. 1).

Fig. 1. Schemas interconnected via external keys

In general cases, the format of building an arbitrary column from schema N can be represented as follows:

[Schema 1 context].[...].[Schema N context].[Column_name]

Examples of forming a column name for adding a column to query

Example 1

In this case, the column name is specified as [Column name in the root schema].

• Column containing the city name
• Column name: Name
• Example of creating EntitySchemaQuery returning the values of this column:

// Creating an EntitySchemaQuery query instance with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding a column with the city name to the query.
esqQuery.AddColumn("Name");

// Receiving the text of the resulting sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText(); 

• Resulting sql-query(MS SQL):

SELECT
[City].[Name] [Name]
FROM
[dbo].[City] [City]

Example 2

The column name is configured as follows: [Name of lookup column].[Name of lookup schema column].

In the resulting query, a “Country” lookup schema will be connected to the City root schema by the JOIN operator (LEFT OUTER JOIN by default). The connection condition (ON condition of the JOIN operator) is formed as follows:

[Name of the connected schema].[Id] = [Name of the root schema].[Name of the lookup column that refers to the connected schema + Id]

• Column containing the name of the country, where the city is located
• Column name: Country.Name
• Example of creating EntitySchemaQuery returning the values of this column:

// Creating an EntitySchemaQuery query instance with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding a column with the name of the country, where the city is located to the query.
esqQuery.AddColumn("Country.Name");

// Receiving the text of the resulting sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText();  

• Resulting sql-query(MS SQL):

SELECT
[Country].[Name] [Country.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])

• Name of the contact who added the country of a specific city
• Column name: Country.CreatedBy.Name
• Example of creating EntitySchemaQuery returning the values of this column:

// Creating an EntitySchemaQuery query instance with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding a column with the contact name who added the country of a specific city to the query.
esqQuery.AddColumn("Country.CreatedBy.Name");

// Receiving the text of the resulting sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText();   

• Resulting sql-query(MS SQL):

SELECT
[CreatedBy].[Name] [CreatedBy.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])
LEFT OUTER JOIN [dbo].[Contact] [CreatedBy] ON ([CreatedBy].[Id] = [Country].[CreatedById])

Example 3

The column name is built as follows: [Name_of_connected_schema:Column_name_for _linking_the_connecetd_schema:Column_name_for_linking_the_current_schema].

This method of connecting columns suggests applying reverse connections, i.e., the connection of the connected entity lookup column with any of the columns of the primary entity.

If the Id column of the current schema acts as a connecting column, it can be omitted, i.e., the column name will look as follows:

[Name_of_the_connected_schema:Name_of_the_column_for_linking_the_connected_schema].

• Column with the name of the contact, whose page contains the city selected by the query
• Column name: [Contact:City:Id].Name
• Example of creating EntitySchemaQuery returning the values of this column:

// Creating an EntitySchemaQuery query instance with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

//  Adding the column with the contact name, whose page contains the specified city to the query.
esqQuery.AddColumn("[Contact:City:Id].Name");

// Receiving the text of the resulting sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText(); 

• Resulting sql-query(MS SQL):

SELECT
[Contact].[Name] [Contact.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Contact] [Contact] ON ([Contact].[CityId] = [City].[Id])

In the below example, we consider an alternative variant of building a column name containing the name of the contact that added the country of a specific city (see example 2).

• Column with the name of the contact that added the country of a specific city
• Column name: Country.[Contact:Id:CreatedBy].Name
• Example of creating EntitySchemaQuery returning the values of this column:

// Creating an EntitySchemaQuery query instance with the "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding the column with the contact name who added the country of the specific city to the query.
esqQuery.AddColumn("Country.[Contact:Id:CreatedBy].Name");

// Receiving the text of the resulting sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText();

• Resulting sql-query(MS SQL):

SELECT
[Contact].[Name] [Contact.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])
LEFT OUTER JOIN [dbo].[Contact] [Contact] ON ([Contact].[Id] = [Country].[CreatedById])