The information that the Entity Framework generates when it processes a LINQ to Entities or Entity Sql statement can cause the Entity Provider for Teradata to generate several derived tables in the SQL statement. Under specific scenarios, this information can direct the Entity Provider for Teradata to create a derived table that references a column that is outside its scope. What is meant by "scope" can best be explained through an example. The following example uses the DBC.Tables view:

   select xxx.databasename, xxx.tablename, xxx.tablekind
   from (
      select yyy.databasename, yyy.tablename, yyy.tablekind, yyy.creatorname
         from (
            select databasename, tablename, tablekind, creatorname, createtimestamp
            from dbc.Tables
            where DatabaseName = xxx.Databasename
         ) as yyy
   ) as xxx

In this example, there is a nested derived table. The inner derived table references a column (xxx.databasename) of the outer/parent derived table in its WHERE clause. This column is not in the "scope" of the inner derived table. The Advanced SQL Engine does not support this feature. In this case the error message returned by the SQL Engine will be:

[Teradata Database][3807] Object 'xxx' does not exist.

One scenario where the Entity Framework will direct the Entity Provider for Teradata to build an SQL that causes this problem is when a statement uses relationship navigation and calls a canonical function. The argument to the canonical function is another relationship navigation that calls another canonical function. The following is an example using LINQ to Entities:

public void ExampleScopingProblem()
{
   // 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();

   EDMExample context = new EDMExample(ebuilder.ToString());

   var query = from c in context.Customers
      where c.Orders.Sum(o => o.Customer.Orders.Count(oo => oo.Freight > 2)) > 10
      select c;

   foreach(var result in query)
   {

       // Process each record returned

   }
}

This example retrieves the Customers who have more than 10 Orders that have Freight charges that are greater than 2.

The information that is created by the Entity Framework directs the Entity Provider for Teradata to create a SQL statement that contains nested derived tables. Unfortunately, one of the inner derived tables references a column from an outer derived table. Because this occurs, the LINQ statement in the example cannot be executed.