Database specific features for Google Cloud Spanner

This section illustrates the database specific features for Google Cloud Spanner which are available to you in the LLBLGen Pro Runtime Framework.

See the RuntimeConfiguration system section for details about configuring the settings through code.

Tip

For Google Cloud Spanner it's highly recommended to use the Adapter template group as Adapter supports query batching and transient error recovery through a default recovery strategy, two features which are essential in a cloud environment.

Important!

Google Cloud Spanner support requires the registration of the DbProviderFactory through the RuntimeConfiguration system on .NET Full as the Google Cloud Spanner ADO.NET provider has no automated registration of its DBProviderFactory type in the machine.config.

Example:

// connection string
RuntimeConfiguration.AddConnectionString("ConnectionString.Google Cloud Spanner (Google Cloud Client)", 
        "Data Source=projects/yourproject/instances/yourinstance/databases/yourdatabase");
// DbProviderFactory
RuntimeConfiguration.ConfigureDQE<SpannerDQEConfiguration>(c => c.SetTraceLevel(TraceLevel.Verbose)
            .AddDbProviderFactory(typeof(SpannerProviderFactory)));

Where SpannerProviderFactory is a type in the Google.Cloud.Spanner.Data ADO.NET assembly.

Hint support

Join hint support.

LLBLGen Pro Runtime Framework supports join hint specification on Google Cloud Spanner databases. See the following example:

var metaData = new LinqMetaData(adapter);
var q = (from n in metaData.TypesTab
         join i in metaData.InterleavedType
                        .WithHint("FORCE_JOIN_ORDER=TRUE") 
                on n.Id equals i.Id
         where n.Id >= 1000
         orderby n.Id ascending
         select n);

which will result in the SQL query:

SELECT
    DISTINCT `LPA_L1`.`ID` AS `Id`,
    -- rest of the columns
    FROM
    (`TypesTab` `LPA_L1` 
INNER JOIN
    @{FORCE_JOIN_ORDER=TRUE} `INTERLEAVED_TYPES` `LPA_L2` 
        ON `LPA_L1`.`ID` = `LPA_L2`.`ID`) 
WHERE ((((`LPA_L1`.`ID` >= @p1)))) 
ORDER BY `LPA_L1`.`ID` ASC

Secondary index hint support

The hint system in LLBLGen Pro can be used to specify a secundary index in a query. See the following example:

var q = (from n in metaData.Notnulltab
            .WithHint("FORCE_INDEX=NOTNULLTABIDX")
         where n.Id >= 1000
         orderby n.Id ascending
         select n);

which results in the following SQL query:

SELECT
    `LPA_L1`.`ID` AS `Id`,
    `LPA_L1`.`DESCRIPTION` AS `Description` 
FROM
    `NOTNULLTAB` @{FORCE_INDEX=NOTNULLTABIDX} `LPA_L1` 
WHERE (((`LPA_L1`.`ID` >= @p1))) 
ORDER BY `LPA_L1`.`ID` ASC

Combining Join and Secondary index hints

You can specify join hints and secondary index hints with one element. The runtime will assign the join hint with the join operator and the other hints with the table name, as shown in the following example:

var metaData = new LinqMetaData(adapter);
var q = (from n in metaData.TypesTab
         join i in metaData.Notnulltab
                        .WithHint("FORCE_JOIN_ORDER=TRUE")
                        .WithHint("FORCE_INDEX=NOTNULLTABIDX") 
            on n.Id equals i.Id
         where n.Id >= 1000
         orderby n.Id ascending
         select n);

which results in the following SQL query:

SELECT
    DISTINCT `LPA_L1`.`ID` AS `Id`,
    -- rest of the columns
FROM
    (`TypesTab` `LPA_L1` 
INNER JOIN
    @{FORCE_JOIN_ORDER=TRUE} `NOTNULLTAB` @{FORCE_INDEX=NOTNULLTABIDX} `LPA_L2` 
        ON `LPA_L1`.`ID` = `LPA_L2`.`ID`) 
WHERE ((((`LPA_L1`.`ID` >= @p1)))) 
ORDER BY `LPA_L1`.`ID` ASC