Null Literals in Set Operations

.NET CORE This feature is not supported by the .NET Core implementation of the Data Provider.

An operand of a Set operation is a SELECT statement. If a NULL literal is specified in the columns-list of one of the operands the Entity Provider for Teradata will cast the NULL to the type that corresponds to the column in the opposite operand. There is a problem when the type of this column is a Interval or Period type. The Advanced SQL Engine is unable to correctly process the SELECT statement generated by the Entity Provider for Teradata. A TdException will be thrown indicating the the columns-list of the operands do not match or are incompatible.

This problem with the SQL Engine will be fixed in a future release.

This problem can appear under the following two scenarios:

A NULL literal is specified in the projection of a LINQ to Entities or Entity Sql statement.

For example, the entity PeriodEntity contains two properties that have an EDM type of EDM.String --EDM.String is mapped to the Period Teradata types. The corresponding columns of these properties are PERIOD(DATE).

NullPeriod Example	Copy Code
public void ExampleNullPeriod() { // Setting up the connection string to the data provider TdConnectionStringBuilder tbuilder = new TdConnectionStringBuilder(); tbuilder.Database = "Database"; tbuilder.DataSource = "Source"; tbuilder.UserId = "user"; tbuilder.Password = "password"; EntityConnectionStringBuilder ebuilder = new EntityConnectionStringBuilder(); // Setting up the connection for the Entity Framework ebuilder.Metadata = @"res://Example/EDMExample.csdl\|res://Example/EDMExample.msl\|res://Example/EDMExample.ssdl"); ebuilder.Provider = "Teradata.Client.Provider"; ebuilder.ProviderConnectionString = tbuilder.ToString(); EntityConnection eConn = new EntityConnection(ebuilder.ToString()); EntityCommand eCmd = eConn.CreateCommand(); eCmd.CommandText = "(select p.c1_integer, cast(NULL as String) from PeriodEntity as p) " + "union all " + "(select pp.c1_integer, pp.period1 from PeriodEntity as pp)"; EntityDataReader eDr = eCmd.ExecuteReader(CommandBehavior.SequentialAccess); // Process each record returned }

NullPeriod Example

Copy Code

public void ExampleNullPeriod()
{
   // Setting up the connection string to the data provider
   TdConnectionStringBuilder tbuilder = new TdConnectionStringBuilder();
   tbuilder.Database = "Database";
   tbuilder.DataSource = "Source";
   tbuilder.UserId = "user";
   tbuilder.Password = "password";

   EntityConnectionStringBuilder ebuilder = new EntityConnectionStringBuilder();

   // Setting up the connection for the Entity Framework
   ebuilder.Metadata = @"res://Example/EDMExample.csdl|res://Example/EDMExample.msl|res://Example/EDMExample.ssdl");
   ebuilder.Provider = "Teradata.Client.Provider";
   ebuilder.ProviderConnectionString = tbuilder.ToString();

   EntityConnection eConn = new EntityConnection(ebuilder.ToString());
   EntityCommand eCmd = eConn.CreateCommand();

   eCmd.CommandText = "(select p.c1_integer, cast(NULL as String) from PeriodEntity as p) " +
      "union all " +
      "(select pp.c1_integer, pp.period1 from PeriodEntity as pp)";

   EntityDataReader eDr = eCmd.ExecuteReader(CommandBehavior.SequentialAccess);

    // Process each record returned

}

The SQL statement that will be generated by the Entity Provider for Teradata will be similar to the following:

SELECT Project1.c1_integer, cast(NULL as period(date)) from PeriodTable as Project1
UNION ALL
SELECT Project2.c1_integer, Project2.period1 from PeriodTable as Project2

When this statement is processed by Teradata a TdException will get thrown.

This problem will also occur when the OfType method is called on an Entity Set that contains a Horizontal Partition and a property that maps to either an Interval or Period type.

For example, the Entity Set Employees has been defined with a Horizontal Partition. The partition consists of data contained in the Employee and PreviousEmployee tables. This entity set contains the property EmploymentPeriod that is defined as EDM.String. The EmploymentPeriod maps to a column that is defined as a PERIOD(DATE). The following LINQ to Entities statement will cause this problem to occur:
```
from e in Employees.OfType<PreviousEmployee>()
select e;
```
The SELECT statement generated by the Entity Provider for Teradata will be similar to the following:
```
SELECT Project1.EmployeeID, Project2.EmploymentPeriod from PreviousEmployee as Project1
UNION ALL
SELECT CAST(NULL as INTEGER) as c1, CAST(NULL as PERIOD(DATE))
```
This SQL statement will cause the TdException to be thrown.