Database specific features

This section illustrates the database specific features which are available to you through configuration, either through the .config file of your application or through code.

See the Application configuration through .config files section for the details about the configuration settings defined through .config files.

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

SQL Server Specific features

SQL Server 2016+ Always Encrypted support

Enabling Always Encrypted is done through the connection string. Registration of key store providers is done by adding a partial class to either DataAccessAdapter (Adapter) or CommonDaoBase (SelfServicing). Below in the code snippet, at the place of '...' the datastructures with the provider information has to be passed. For details for that, see the SQL Server documentation regarding Always Encrypted.

// Adapter, in partial class of DataAccessAdapter
protected override DbConnection CreateNewPhysicalConnection(string connectionString)
{
    var toReturn = base.CreateNewPhysicalConnection(connectionString);
    ((SqlConnection)toReturn).RegisterColumnEncryptionKeyStoreProviders(...);
    return toReturn;
}

// Selfservicing, in partial class of CommonDaoBase
public override DbConnection CreateConnection(string connectionString)
{
    var toReturn = base.CreateConnection(connectionString);
    ((SqlConnection)toReturn).RegisterColumnEncryptionKeyStoreProviders(...);
    return toReturn;
}

For usage of key provider structures, .NET 4.6.1 or higher is required.

SQL Server 2016+ Temporal table support

On SQL Server 2016 or higher, Temporal tables are supported for select / fetch queries in Linq and QuerySpec. For SQL Server, System Time predicates are supported. Below is an example using Linq where the Employee entities are queried using a BETWEEN System Time predicate.

var q = from e in metaData.Employee
                                .ForSystemTime("BETWEEN {0} AND {1}", 
                                                fromDate, toDate)
        where e.EmployeeId == 1
        select e;

Target hint support

On all supported SQL Server versions, target hints are supported through Linq and QuerySpec. These hints are applied to elements in the FROM clause in a select query. Below is an example using Linq where a join is performed between two entities where one of them receives two hint directives.

var q = from c in metaData.Customer
                                .WithHint("NOLOCK")
                                .WithHint("FORCESEEK")
        join o in metaData.Order on c.CustomerId equals o.CustomerId 
        where o.EmployeeId > 4
        select c;

NEWSEQUENTIALID() support

When you're using unique_identifier types for primary keys on SQL Server 2005 or higher, you can benefit from a feature called NEWSEQUENTIALID(). This feature allows you to auto-generate new GUIDs for your primary keys which are sequential, so they are friendly for clustered indexes. To use this feature in LLBLGen Pro, you have to specify as the default for the primary key field in the table definition: NEWSEQUENTIALID().

Furthermore, you shouldn't set the PK field to a new GUID value when you're saving the entity. The DQE will then let the database insert the NEWSEQUENTIALID() produced value and it's automatically retrieved for you into the entity's PK.

LocalDB support

The LLBLGen Pro Runtime Framework supports LocalDB at runtime. To make sure the generated code works with the LocalDB database, the connection string has to be adjusted to match the requirements for LocalDB. Please consult the LocalDB documentation for this.

SQL Server 2012+ sequence support

The runtime supports SQL Server 2012+ sequence objects on pk and non-pk fields. There's no check for SQL Server 2012+ compatibility, however the queries will fail on SQL Server instances which are not capable of working with SQL Server 2012+ sequences. The generated INSERT queries look like (in the example, Field2 is sequenced)

SELECT @p2=NEXT VALUE FOR [schema].[sequence];
INSERT INTO table (Field1, Field2, .., Fieldn) VALUES (@p1, @p2, .. @pn); 

The schema name is taking schema name overwriting into account, so you can use the normal schema name overwriting (per call, equal to Oracle).

SQL Server compatibility mode

The SQL Server DQE is usable for a variety of different SQL Server versions, from 7 to the latest version, and from CE 3.1 to CE 4.0. The SQL Server DQE uses a compatibility mode to determine for which database to generate the SQL so the SQL matches the supported features for the SQL Server version used.

You can set the SQL Server DQE's compatibility mode in two different ways: using the application's .config file or use a code statement. For the application's .config file method, please see Generated code - Application configuration through .config files. For using the code method, please see for SelfServicing: Generated code - CommonDaoBase functionality and for Adapter: Generated code - DataAccessAdapter functionality. For the RuntimeConfiguration method, please see Runtime Configuration - SQL Server - Set default compatibility level.

The SqlServerCompatibilityLevel is defined as follows:

• 0 for SqlServerCompatibilityLevel.SqlServer7
• 1 for SqlServerCompatibilityLevel.SqlServer2000
• 2 for SqlServerCompatibilityLevel.SqlServer2005 (also for 2008/2008 R2, which doesn't require a separate compatibility level)
• 3 for SqlServerCompatibilityLevel.SqlServerCE3x (Sqlserver CE 3.0 and 3.1)
• 4 for SqlServerCompatibilityLevel.SqlServerCE35
• 5 for SqlServerCompatibilityLevel.SqlServerCE40
• 6 for SqlServerCompatibilityLevel.SqlServer2012 (also for 2014/2016, which don't require a separate compatibility level)

By default the compatibility mode is set to SqlServerCompatibilityLevel.SqlServer2005.

The SQL differences per compatibility mode

Setting the compatibility level has the following effect on the generated SQL

• the sequence retrieval logic to use by default:
  • @@IDENTITY with compatibility level SqlServer7, SqlServerCE3x, SqlServerCE35, SqlServerCE40
  • SCOPE_IDENTITY() with compatibility level SqlServer2000, SqlServer2005, SqlServer2012
• the ability to use NEWSEQUENTIALID(), with compatibility level SqlServer2005, SqlServer2012
• the SQL produced for a paging query
  • using a temp table approach with compatibility level SqlServer7, SqlServer2000
  • a CTE approach is used with compatibility level SqlServer2005
  • an OFFSET/FETCH using query with compatibility level SqlServer2012
  • a client-side paging approach (skip rows on datareader/close datareader when page is read) with compatibility level SqlServerCE3x, SqlServerCE35, SqlServerCE40
• the TOP value in a SELECT query:
  • a parameter is used with compatibility level SqlServer2005, SqlServer2012
  • an in-line constant without brackets (e.g. SELECT TOP 5 ... ) with compatibility level SqlServer7, SqlServer2000
  • an in-line constant with brackets (e.g. SELECT TOP(5) ... ) with compatibility level SqlServerCE35, SqlServerCE40
  • a client-side limiter approach (read TOP rows from datareader, close datareader afterwards) with compatibility level SqlServerCE3x

ArithAbort support

When you're using indexed views in your database, and you're inserting data into tables which are used in these indexed views, you'll run into the problem that you have to set ARITHABORT ON before the particular insert statement is executed.

To signal that the SQL Server DQE has to emit the ARITHABORT statement prior to an insert statement, you can use the ArithAbort flag implemented on the CommonDaoBase class (SelfServicing) or DataAccessAdapter class (Adapter). Please see for SelfServicing: Generated code - CommonDaoBase functionality and for Adapter: Generated code - DataAccessAdapter functionality.

Spatial types / User Defined Types support

SQL Server 2005 or higher supports User Defined Types (UDTs) written in a CLR language like C# or VB.NET. The SQL Server driver can read these fields and if you're using UDTs in your tables, the fields which have a UDT as their type will be read by the driver and their UDT type is considered their valid type.

Entities mapped onto these tables (or views) have fields which .NET type is equal to the UDT of the target field in the table/view they're mapped on. The generated entity classes will have properties which refer to the UDT type as the type of the property, as the UDT is a normal CLR type.

In these situations you have to reference the assembly which contains the UDT in your generated Visual Studio project. (For Adapter, the database generic project). Usage of the field in .NET code is like any other code: you can set the field to an instance of the UDT type and normally save it and load it. Saving a UDT requires that the UDT is serializable to string, which is a requirement for SQL Server as well.

The same mechanism is used for loading and persisting Spatial Typed data.

SQL Server CE Desktop support

LLBLGen Pro supports SQL Server CE Desktop v3.1 or higher. SQL Server CE Desktop is the win32 runnable version of the same database known from the compact framework, SQL Server CE 3.0. SQL Server CE Desktop is SQL Server CE v3.1 or higher, but embeds roughly the same features as SQL Server CE 3.0 or higher for the compact framework: no stored procedures, a single schema and no meta-data retrieval.

It's recommended that you use the latest CE Desktop version, as it contains more features. To be able to target SQL Server CE Desktop, you first has to create a SQL Server project.

Stored procedures aren’t supported on CE Desktop, although they might be generated into the generated code. LLBLGen Pro uses the normal SQL Server DQE assembly for query production for CE Desktop. You also have to specify the compatibility level for the DQE, to signal it that it has to generate queries for SQL Server CE. For more information about this compatibility level, please see Generated code - Application configuration through .config files.

To be able to connect to a SqlServerCE desktop database, one has to adjust the connection string, as this connection string is the one used to connect to the SQL Server catalog from which the LLBLGen Pro project was created. It has to have the format shown by the following example:

<add key="Main.ConnectionString"  value="data source=c:\path\to\dbdatabasename.sdf;"/>

As SQL Server CE Desktop doesnt support multiple catalogs nor multiple schemas, these features arent available. Also System.Transactions transactions aren’t supported. All other LLBLGen Pro native features, like dependency injection, validation, authorization etc. are supported.

Linq is supported with SQL Server CE Desktop v3.5 or higher, however there are limitations in SQL Server CE Desktop which make it a bit of a struggle. For example the lack of scalar query support can lead to a lot of errors at runtime because a scalar query in a projection or WHERE clause isn't supported by SQL Server CE Desktop.

SqlServerCe provider registration

It's not necessary to reference System.Data.SqlServerCe.dll, however on the machine the application is ran which uses compatibility level 3 or 4, this dll has to be installed as documented in the SQL Server CE Desktop documentation about deployment: via the .msi shipped with SQL Server CE Desktop.

If you can’t run this .msi installer, be sure your application’s .config file contains the appropriate provider registration for the DbProviderFactory. (this information is installed in the machine.config file by the .msi installer of SQL Server CE Desktop). More details about this are available in the SQL Server CE Desktop documentation (the 'Books online' documents of SQL Server CE Desktop)

SQL Azure support

LLBLGen Pro supports SQL Azure out of the box, at runtime. To make your application run on SQL Azure you have to do the following:

• Use Catalog name overwriting to overwrite your catalog's name to "". See Catalog Name Overwriting in application config files for details how to do this. This works for Selfservicing and adapter. As 'old' catalog name you specify the name of the catalog you're using in the project, e.g. Northwind. As 'new' catalog you specify the empty string: "".
• Use only one catalog in your project. If you use multiple catalogs in your project, they'll be seen as one catalog on SQL Azure, as there's no catalog name allowed in SQL queries.
• Make sure you utilize the LLBLGen Pro Runtime Framework feature Transient Error Recovery on code which can fail on Azure due to long running connections/transactions.

Oracle specific features

Ansi joins

By default, the Oracle DQEs will use Ansi joins, i.e. LEFT/INNER/RIGHT OUTER JOIN syntax. To switch this off,  and use join syntaxis like SELECT .. FROM A, B, C, you can switch this off by using one of the following:

1. A setting in the application's .config file. Please see: Generated code - Application configuration through .config files for the details.

2. Use the DQE Configuration of the RuntimeConfiguration system. Please see Runtime Configuration - Oracle - Disabling Ansi joins

Trigger based sequence values

It can be that your project's Oracle database schema is used by multiple applications, among them your LLBLGen Pro based software. This can give the situation that you've to deal with the situation that the schema is configured to use triggers to insert sequence values on row insert. To tell the Oracle DQE DQE of choice that this is the case, and thus that it shouldn't ask for a new sequence value when a new entity is inserted, you've to add a setting to the application's .config file. Please see: Generated code - Application configuration through .config files for the details.

Oracle compatibility mode

The Oracle DQEs (both ODP.NET and MS Oracle) support a wide range of Oracle versions, from 9i to 12c and higher. To enable your application to use specific features of the used Oracle version, the Oracle compatibility mode.

You can set the Oracle DQE's compatibility mode in two different ways: using the application's .config file or use a code statement. For the application's .config file method, please see Generated code - Application configuration through .config files. For using the code method, please see for SelfServicing: Generated code - CommonDaoBase functionality and for Adapter: Generated code - DataAccessAdapter functionality. For the RuntimeConfiguration method, please see Runtime Configuration - Oracle - Set default compatibility level.

By default the compatibility mode is set to Oracle 9i, 10g, 11g.

The differences per compatibility mode

• Oracle 9i, 10g, 11g. Value 0, or OracleCompatibilityLevel.Oracle9i10g11g. Default, generates paging queries with rownum filters. Generates separate sequence retrieval queries for obtaining sequence values during inserts.
• Oracle 12c. Value 1, or OracleCompatibilityLevel.Oracle12c. Generates OFFSET FETCH ONLY paging queries which are more efficient than rownum queries and don't run into the problem with nesting through too much wrapping brackets. Also supports identity fields with default sequences in this mode by omiting the sequence retrieval query for identity fields. This compatibility mode is recommended for Oracle 12c or higher.

Sequence value query batching

In insert queries, sequence value queries are batched together with the insert statement into one command, using wrapping BEGIN, END statements.

ODP.NET Managed provider support (ODP.NET v12+)

By default, the first factory the ODP.NET DQE will look for will be Oracle.ManagedDataAccess.Client, which is the managed provider shipped in the 12c ODP.NET version. If this factory isn't found, the Oracle.DataAccess.Client factory is tried. If that one isn't found either, the DQE/driver will give up.

.NET Standard 2.0+/.NET Core support

Oracle is a supported database for the LLBLGen Pro Runtime Framework on .NET Standard 2.0. To use Oracle with LLBLGen Pro on .NET standard 2.0, generate code for .NET Standard 2.0 from your ODP.NET using LLBLGen Pro project and add a reference to the Oracle.ManagedDataAccess.Core package from NuGet (with version v2.18.0 or higher) to your main project, which use the generated code project(s).

As the .NET Standard variant of ODP.NET is the managed provider, The RuntimeConfiguration settings' method SetAlwaysChooseUnmanagedProviderwhich allow you to select the unmanaged provider isn't available for the .NET Standard 2.0 builds of the ODP.NET DQE.

When registering the ADO.NET Factory using RuntimeConfiguration, you should choose for invariant name "Oracle.ManagedDataAccess.Client". When using TSN names, it's key to define these again using the OracleConfiguration class for .NET Core, as there's no CLI/tsnnames.ora file. For an example how to do that, please see the dotnet examples for ODP.NET.

Setting to always pick unmanaged propvider

In some situations it might be necessary to choose the unmanaged provider, as XmlType isn't supported on the managed provider, and as the managed provider is chosen by default, this could lead to a catch-22, because ODP.NET installs both the unmanaged and the managed provider.

To make sure the unmanaged provider is chosen at runtime, use one of the following:

1. Use the DQE Configuration of the RuntimeConfiguration system. Please see Runtime Configuration - Oracle - Always use the unmanaged DbProviderFactory

2. Add ODPNETAlwaysChooseUnmanagedProvider as setting in the application's appSettings section in the application's .config file with the value true as shown below.

<add key="ODPNETAlwaysChooseUnmanagedProvider" value="true"/>

By default this setting is false. If it's ok to use the managed provider, it's not required to specify the setting.

Firebird specific features

Trigger based sequence values

It can be that your project's Firebird database schema is used by multiple applications, among them your LLBLGen Pro based software. This can give the situation that you've to deal with the situation that the schema is configured to use triggers to insert sequence values on row insert. To tell the Firebird DQE of choice that this is the case, and thus that it shouldn't ask for a new sequence value when a new entity is inserted, use one of the following:

1. Add a setting to the application's .config file. Please see: Generated code - Application configuration through .config files for the details.

2. Use the DQE Configuration of the RuntimeConfiguration system. Please see Runtime Configuration - Firebird - Trigger based sequence values

DB2 specific features

Temporal table support

For DB2, temporal tables are supported on DB2 10 or higher, for select / fetch queries using Linq or QuerySpec. Both Business Time and System Time predicates are supported. Below is an example using a linq query, where an entity is filtered using the System Time.

var q = metaData.Policy
                    .ForSystemTime("FROM {0} TO {1}", 
                                        new DateTime(2010, 1, 1), 
                                        new DateTime(2017, 1, 1))
                    .Where(p=>p.Vin == "A1111")
                    .Count();

MySQL specific features

Index hint support

For MySQL, index hint specifications for elements in the FROM clause in select queries are supported through Linq and QuerySpec. Below is an example using a linq query, where the query is directed to use a specific index for the target of Address.

var q = metaData.Address.WithHint("USE INDEX (idx_fk_city_id)").Count();