AttachmentSize
Package icon TeradataNorthwind.zip188.47 KB

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. 

Discussion
axb 1 comment Joined 05/09
22 Nov 2010

.NET Data Provider for Teradata 13.10.00.00, that is mentioned, GCA'd on 11/16/10.

rhart75 1 comment Joined 03/11
11 Mar 2011

I have set this up but I can't see any Views on the EDMX pane. Does it support Views?

dsakai 6 comments Joined 05/09
11 Apr 2011

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.

OliverWorm 1 comment Joined 03/11
12 May 2011

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.

dsakai 6 comments Joined 05/09
18 May 2011

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.

dleborgne 1 comment Joined 04/14
08 Aug 2016

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

You must sign in to leave a comment.