Introduction to the Entity Provider for Teradata

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

Support for the Entity Framework 3.5 SP1 is coming in .Net Data Provider for Teradata (TDNETDP) 13.10. This release of the Data Provider for Teradata includes the Entity Provider for Teradata.

The Entity Provider gets installed during the installation process of TDNETDP. Once installed, applications can be written that execute LINQ to Entities and Entity Sql statements.

In the following examples, an Entity Data Model that references the Northwind database is used. The Northwind EDM and database were created by Microsoft.  These items have been modified for Teradata.

The program to create the Northwind database and the EDM files are contained in the file attached to this blog, TeradataNorthwind.zip.

The download contains the directory CreateNorthwind. CreateNorthwind contains a Visual Studio project that creates a Console Application (CreateNorthwind.exe).  This application creates the tables and loads the Northwind data.  In this directory there is also directory called Data that contains the data for the LOBs that will be inserted into the tables.

Before compiling and executing the code the following setup tasks must be performed:

  1. Create a User where the tables can be created.
  2. In the CreateNorthwind project, modify "app.config".  The configuration settings for the data source, user id, and password must be correctly set.  Once this has been done, compile the code.
  3. Execute the CreateNorthwind.exe to create the Norhwind tables and to load the data.

The Northwind Entity Data Model is contained in the file NorthwindEFModelTeradata.edmx.

The following are some examples on using the Entity Framework with Teradata:

Example 1

Here is a LINQ to Entities statement that retrieves the orders that were taken for each day. 

from o in Orders
group o by o.OrderDate into byDate
orderby byDate.Key
select new { DateOfOrder = byDate.Key,
             NumberOfOrders = byDate.Count(),
             Orders = (from oo in Orders
                       where oo.OrderDate == byDate.Key
                       orderby oo.OrderID
                       select oo.OrderID)};

The columns that are returned are:

  • DateOfOrder
  • NumberOfOrders – the number of orders placed on the OrderDate
  • Orders – all the Ids of the orders placed on the OrderDate

For each row that is returned when this statement is executed, the Orders column contains a collection of Id's of Orders that were taken on the DateOfOrder. 

Here is how the result is displayed when the statement is executed using LinqPad (there will be a blog on accessing Teradata using LinqPad in the coming weeks). This is only a subset of the results:

Example 2

Here is an example of an Entity Sql statement that returns the customer and each of the orders that they have placed:

select c.CustomerID, 
       c.CompanyName,
       (select deref(rr).OrderID,
               deref(rr).OrderDate
        from navigate(c, NorthwindEFModel.CustomerOrders) as rr
        order by deref(rr).OrderDate) as OrderInfo
from NorthwindEntities.Customers as c order by c.CustomerID

This example returns the following data: 

  • CustomerID
  • CompanyName
  • OrderInfo
    • OrderID
    • OrderDate

The OrderInfo column contains a collection of orders that have been placed by the customer. 

When this statement is executed using LinqPad, a portion of the rows returned appear as follows: 

Example 3

This is another LINQ to Entities example.  

var query = 
     from od in OrderDetails join p in Products
          on od.ProductID equals p.ProductID
     select new
     {
         od.OrderID,
         p.ProductID,
         p.ProductName,
         od.Quantity,
         od.UnitPrice,
         od.Discount,
         ActualPricePerUnit =
              ((float)od.UnitPrice -
                   ((float)od.UnitPrice * od.Discount))
     };
 
var mainQuery =
     from c in Customers
     select new
     {
        c.CustomerID,
        c.CompanyName,
        Orders =
        (
            from o in Orders
            where o.Customer.CustomerID == c.CustomerID
            select new
            {
                 o.OrderID,
                 o.OrderDate,
                 ProductInfo =
                 (
                      from q1 in query
                      where q1.OrderID == o.OrderID
                      select new
                      {
                          q1.ProductID,
                          q1.ProductName,
                          q1.UnitPrice,
                          q1.Discount,
                          q1.ActualPricePerUnit
                       }
                 )
            }
        )
     };

The results that are returned contain nested collections.  Information about customers are returned.  For each customer, a collection of Orders that they have placed are returned.  For each order, information about the products included in the order are returned in a collection.

This example also shows how LINQ to Entities allows a statement to be built from other statements.

 The information that is retrieved from the mainQuery is:

  • CustomerID
  • CompanyName
  • Orders -- This is a collection that contains the following items:
    • OrderID
    • OrderDate
    • ProductInfo --This is another collection nested in the Orders collection that contains the following items:
      • ProductID
      • ProductName
      • UnitPrice
      • Discount
      • ActualPricePerUnit

  A subset of the results returned from the execution of mainQuery using LinqPad looks like:

More blogs about the Entity Framework and Teradata will be posted in the coming weeks. 

6 Comments
axb
Teradata Employee
.NET Data Provider for Teradata 13.10.00.00, that is mentioned, GCA'd on 11/16/10.
I have set this up but I can't see any Views on the EDMX pane. Does it support Views?
Teradata Employee
Sorry it has taken me so long to respond.

The Entity Provider 13.10 does not contain support for views. Support for views will be added in 13.11 which will be released very soon.

I will be writing another blog on how Views are supported in the 13.11 release.
Is it possible to adjust the databasename in the entitycontainer section of the storagemodel at runtime?
it seems that you have to use a name of an existing database, overrides of the tbuilder.Database don´t seem to work.
Teradata Employee
The value of the Database attribute cannot be used to control where the EF retrieves the data for the entities of your model. This is a "feature" of all entity providers, not only Teradata.

What you can possibly do is to use reflection to retrieve the XML of the SSDL from the resource and then change the database name. Save the SSDL to a file. When you connect, use the name of the SSDL file in the Metadata attribute of the EntityConnectionStringBuilder.

In case anyone would need it as I did, the SQL script required to create and populate tables, based on Program.cs can be found at https://gist.github.com/dleborgne/caf1e7ed9b42d6318eb89e04c89eec62