.NET Framework This feature is not supported by the .NET Framework implementation of the Data Provider.
Overview
There are limitations when using the EFCore Provider to access a Teradata SQL Engine. The SQL Engine does not support all the features provided by Entity Framework Core. When possible, the EFCore Provider will throw a NotSupportedException if it detects an unsupported Entity Framework Core feature or a LINQ query that is not supported by the SQL Engine. The Data Provider will throw a TdException if the EFCore Provider does not detect an unsupported feature or generates an unsupported SQL statement.
In the discussions of the limitations, examples may be given on what causes these exceptions to be thrown. There may not always be a concise example that demonstrates a pattern in a LINQ statement that will cause an exception, because the structure of the SQL statements is affected by the Model and the LINQ statements.
Modeling Limitations
The following Entity Framework Core concepts are not supported by the EFCore Provider because they are not supported by the SQL Engine:
- Computed Columns. The EFCore Provider will throw a NotSupportedException if an application tries to use this feature.
- Identity Column as part of a composite key. Multiple properties may be configured to be the key of an entity as long as none of them are configured as Identity Column. The EFCore Provider will throw a NotSupportedException if an application tries to use this feature.
- Sequences. The EFCore Provider will throw a NotSupportedException if an application tries to use this feature. The EFCore Provider supports Identity Columns with the provided ForTeradataUseIdentityAlways and ForTeradataUseIdentityByDefault extensions.
- ROWVERSION columns or [Timestamp] attribute. Application must use the ForTeradataUseRowVersion extension to configure a property to enable optimistic concurrency control.
- The System.Guid data type. See the notes in Data Type Mappings for a workaround.
- The Teradata SQL Engine does not have a default length for array data types. Any properties of type System.Byte[], System.Char[] or System.String must either be mapped to a BLOB or a CLOB column or have the maximum length specified via Maximum Length or Data Type annotations. NOTE: The Maximum Length and Data Type annotations are mutually exclusive. See Data Type Mappings for more information and an example.
- EFCore Provider 3.1.x: Due to a bug in EFCore 3.1.x related to the Table Splitting feature (see issue #21612), the Discriminator property is marked as NonNullable by EFCore even if it maps to a nullable column. This may result in Teradata SQL Engine error "[2689] Field is null for column defined as NOT NULL." The workaround is to mark the property as nullable in the OnModelCreating method, for example: modelBuilder.Entity().Property<string>("Discriminator").Metadata.IsNullable = true;
The following SQL Engine features are not supported by the EFCore Provider:
- User-defined types (UDT).
- User-defined functions (UDF).
Query Limitations
The following query limitations exist:
- The FromSql extension method:
- Does not support "CREATE PROCEDURE" or "REPLACE PROCEDURE" SQL statements. Use the TdCommand ExecuteCreateProcedure method instead.
- Supports named and positional parameters, but named parameters may not be re-used -- every parameter in the RawSqlString must have a unique name.
- The LINQ Skip method is not supported if it is not executed locally. Depending on the LINQ query, Entity Framework Core decides whether Skip is executed on the server or locally on the client. The SQL Engine does not support a SQL equivalent of the LINQ Skip method.
- The String == operator and String.Compare(String) methods are not case-sensitive in Teradata session transaction mode and are case-sensitive in ANSI session transaction mode. The String.Compare(strA, strB, ignoreCase) method may be used to specify desired behavior without regard for transaction mode. The ForTeradataIsCaseSensitive extension may also be used to configure the case sensitivity of a property.
- The SQL Engine does not support Cascade Delete and deletion of rows if doing so violates referential constraints.
- When column name and alias names are the same, the SQL Engine references the column name, not the alias name for operations like "ORDER BY". This may not result in exceptions, but may lead to incorrect results.
- Conversion of System.DateTimeOffset and System.TimeSpan to System.String using object.ToString() or Convert.ToString(object) method will always be performed on the client, because there is no native Convert.ToString() method for either type.
- The SQL Engine does not support queries that contain:
- "ORDER BY" in a subquery. Such SQL may be generated by the following LINQ query:
os => os.OrderBy(o => o.OrderID).Select(o => o.OrderID).Take(10).Max()
If EFCore Provider 2.2.x detects this condition, it will throw a NotSupportedException, otherwise the Data Provider will throw a TdException. EFCore Provider 3.1.x does not detect this condition -- the Data Provider will throw a TdException.
- Parametrized "CASE WHEN", which may be generated by the LINQ Contains method. The EFCore Provider does not detect this condition -- the Data Provider will throw a TdException.
- Parametrized "TOP N" (i.e. "TOP ?"), if the object for the parameter is not a ConstantExpression. The EFCore Provider does not detect this condition -- the Data Provider will throw a TdException.
- "TOP N" in a subquery. The LINQ methods First, FirstOrDefault, and Take are not supported in subqueries. If EFCore Provider 2.2.x detects this condition, it will throw a NotSupportedException, otherwise the Data Provider will throw a TdException. EFCore Provider 3.1.x does not detect this condition -- the Data Provider will throw a TdException.
- "TOP N" with DISTINCT option. Such SQL may be generated by the following LINQ query:
os.Distinct().Take(2)
If the EFCore Provider detects this condition, it will throw a NotSupportedException, otherwise the Data Provider will throw a TdException.
- References to outer alias from a derived table or to outer query objects from a subquery. The EFCore Provider does not detect this condition -- the Data Provider will throw a TdException with error number 3807 ("Object does not exist").
- EFCore Provider 3.1.x: The Teradata SQL Engine does not support the APPLY operator, which may be generated by EFCore when a JOIN operation is performed without a predicate. If the EFCore provider detects that an APPLY operator was generated, a NotSupportedException will be thrown.
- EFCore Provider 3.1.x: The Teradata SQL Engine does not support the syntax generated by EFCore for the LINQ .Contains(item) extension method if the item ends up in a projection, like in the following examples:
- context.Customers.Select(c => c.CustomerID).Contains("ALFKI")
- context.Orders.Where(o => o.CustomerID == "VINET").Contains(context.Orders.Single(o => o.OrderID == 10248));
A NotSupportedException will be thrown if the EFCore Provider detects this condition. The LINQ .Contains(item) method is supported if the item ends up in a predicate.
- EFCore Provider 3.1.x: Query tags are not supported for stored procedure calls.
Migrations Limitations
The Teradata SQL Engine does not support the relational database concept of "schema" and only supports a two-part naming convention for qualified object names (i.e., "DatabaseName.TableName", not "catalog.schema.table"). However, a schema may be considered functionally equivalent to a database object in the Teradata SQL Engine. Therefore, schemas are mapped to database objects by the Teradata EFCore Provider. ModelBuilder extensions are provided to annotate such database objects appropriately.
The EnsureSchema migration operation is supposed to only create the schema if it does not already exist. Due to limitations of the SQL Engine, this operation requires the use of a stored procedure. A procedure named "__EFEnsureSchemaProc" is created in the default database automatically during the initial migration for this purpose. This procedure is only required for Migrations with schemas and may be dropped manually if it is no longer needed.
Migrations of entity properties of type System.String will generate a model snapshot with entities that use an incomplete column type "VARCHAR". This must be manually corrected by specifying the maximum length of the VARCHAR column, such as: b.Property<string>("Url").HasColumnType("VARCHAR(2048)");
Scaffolding Limitations
- When providing both, a list of schemas and a list of tables for scaffolding, if any of the tables in the list are not schema-qualified, scaffolding will search for these tables in the default database.
- A RowVersion column cannot be scaffolded, since the SQL Engine does not have support for this feature.
- The Teradata SmallInt data type will always map to System.Int16. If System.Byte is desired instead, the user must properly annotate the property after scaffolding. See "Teradata Asymmetric Type Mapping" section in Data Type Mappings.
Limitations Using the Current Session Character Set
It is recommended to use a Unicode session character set in order to fully support all object names in the EFCore Provider. If a character cannot be represented within the current client session character set, the Teradata SQL Engine will return a translation error character in the object name. The object name in this form cannot be submitted back to Teradata. For this reason, the session character set that is used to generate the Model must also be used in generating queries.
For more information on extended object name support in the .NET Data Provider see Extended Object Names in the Data Provider.