In my previous post, I demonstrated how to use ADO.NET to connect to Teradata in your OLTP projects. In this sample, I am going to implement the same sample using ADO.NET Entity Model, Entity Framework and LINQ.
Connect to Teradata Database via .NET Data Provider
Prerequisites
- Entity Framework 5.x or any version above 3.5
- .NET Data Provider for Teradata 15.01.00.01 (http://downloads.teradata.com/download/connectivity/net-data-provider-for-teradata)
- Teradata Database 12.0 or later.
Step 1 Create ADO.NET Entity Data Model
In the wizard, create ‘TDSample.edmx’ through item ‘ADO.NET Entity Data Model’.
https://api.kontext.tech/resource/ba293c61-ad33-57b9-9671-f3319f57d789
Select Generate from Database:
https://api.kontext.tech/resource/fce23375-fdd4-5b30-8e57-a401e5265ba1
Create a connection to Teradata database:
https://api.kontext.tech/resource/8060dbbf-e5cc-5ed8-b6a8-9c463ae3f1ef
Remember to set the option ‘Use X Views’ to false and set the Database to the database you are using. I also changed the timeout options as my local VM responds slowly.
The entity connection string looks like:
metadata=res://*/TDSample.csdl|res://*/TDSample.ssdl|res://*/TDSample.msl;provider=Teradata.Client.Provider;provider connection string="connection timeout=90;database=TD_MS_SAMPLE_DB;user id=dbc;data source=192.168.121.128;persist security info=True;use x views=False;restrict to default database=True;command timeout=90"
* Change the server and user id/password to yours.
Click next to choose the entity framework version.
https://api.kontext.tech/resource/ae0f5d2b-52f8-5845-8572-d7c586982e02
And then choose the right tables required.
https://api.kontext.tech/resource/5d9b0a1d-62ce-570c-ba61-24557b6f4e68
Click Finish button to close the wizard. In my sample, the model has two tables added.
https://api.kontext.tech/resource/e0a97602-6480-588c-868f-bc044abf9cb5
Step 2 LINQ to Teradata
With the generated entity objects, it will be very straightforward to query from the database.
The sample code looks like:
static void Main(string[] args)
{
DisplayEmployeeInfoFromTeraData2();
Console.ReadLine();
}
private static void DisplayEmployeeInfoFromTeraData2()
{
var db = new TDSampleContainer();
var query = from emp in db.Employees.AsQueryable()
select new
{
EmployeeID = emp.EmployeeID,
EmployeeName = emp.EmployeeName,
EmployeeSalutation = emp.Gendar == "M" ? "Dear MR " + emp.EmployeeName :
"Dear MS " + emp.EmployeeName
};
foreach (var e in query)
{
Console.WriteLine("EmployeeID = {0}", e.EmployeeID);
Console.WriteLine("EmployeeName = {0}", e.EmployeeName);
Console.WriteLine("Employee Salutation = {0}", e.EmployeeSalutation);
Console.WriteLine();
}
Console.WriteLine("{0} Records", query.Count());
}
The result
https://api.kontext.tech/resource/e1d79fd9-d2be-5d43-9714-87cf2a8567e0
Summary
With .NET Data Provider for Teradata, you can easily operate on the database with Linq. If you want to learn more about LINQ, please visit the following link: