DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
Take advantage of the Microsoft ADO.NET Entity Framework with IBM® data servers by using the IBM Data Server Provider for .NET. You can write and execute Entity SQL and LINQ statements and generate entity data model (EDM) schemas to Entities applications with the supported server versions.
You must have Microsoft .NET Framework 3.5 SP1 or later with the Microsoft ADO.NET Entity Framework. To manipulate entity data models by using the Microsoft Entity Data Model wizard or ADO.NET Entity Designer, you also require Microsoft Visual Studio 2008 or later. Support for .NET Framework 4.0 with Visual Studio 2010 has been introduced starting in DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 4.
If you are connecting to a DB2 for i V6R1 database using Version 9.7 Fix Pack 5 and later fix packs, the DB2 for i V6R1 database must have PTF SI29764 applied.
The following table lists the canonical functions that the IBM entity provider supports. Canonical functions are translated to the corresponding data source functions by the data provider.
| Canonical function type | LINQ function name | DB2 for Linux, UNIX, and Windows server | DB2 for z/OS server | DB2 for i server | Informix database server server |
|---|---|---|---|---|---|
| Aggregate | Average | Y | Y | Y | Y |
| BigCount | Y | Y | Y | Y | |
| Count | Y | Y | Y | Y | |
| Maximum | Y | Y | Y | Y | |
| Minimum | Y | Y | Y | Y | |
| NewGuid1 | Y* | Y* | Y* | Y* | |
| StDev | Y | Y | Y | Y | |
| StDevP | Y | Y | Y | Y | |
| Sum | Y | Y | Y | Y | |
| Var | Y | Y | Y | Y | |
| VarP | Y | Y | Y | Y | |
| Bitwise | BitWiseAnd1 | Y | Y* | Y* | Y |
| BitWiseNot1 | Y | Y* | Y* | Y | |
| BitWiseOr1 | Y | Y* | Y* | Y | |
| BitWiseXor1 | Y | Y* | Y* | Y | |
| Math | Abs | Y | Y | Y | Y |
| Ceiling | Y | Y | Y | Y | |
| Floor | Y | Y | Y | Y | |
| Power | Y | Y | Y | Y | |
| Round (value,digits) | Y | Y | Y | Y | |
| Truncate (value,digits) | Y | Y | Y | Y | |
| String | Concat | Y | Y | Y | Y |
| Contains1 | Y | Y | Y | Y* | |
| EndsWith | Y | Y | Y | Y | |
| IndexOf1 | Y | Y | Y | Y* | |
| Left | Y | Y | Y | Y | |
| Length | Y | Y | Y | Y | |
| LTrim | Y | Y | Y | Y | |
| Replace | Y | Y | Y | Y | |
| Right | Y | Y | Y | Y | |
| RTrim | Y | Y | Y | Y | |
| StartsWith | Y | Y | Y | Y | |
| Substring | Y | Y | Y | Y | |
| ToLower | Y | Y | Y | Y | |
| ToUpper | Y | Y | Y | Y | |
| Trim | Y | Y | Y | Y | |
| Datetime | AddNanoseconds | Y | Y | Y | Y |
| AddMicroseconds | Y | Y | Y | Y | |
| AddMilliseconds | Y | Y | Y | Y | |
| AddSeconds | Y | Y | Y | Y | |
| AddMinutes | Y | Y | Y | Y | |
| AddHours | Y | Y | Y | Y | |
| AddDays | Y | Y | Y | Y | |
| AddMonths | Y | Y | Y | Y | |
| AddYears | Y | Y | Y | Y | |
| CreateDateTime | Y | Y | Y | Y | |
| CreateDateTimeOffset | Y | ||||
| CurrentDateTimeOffset1 | Y | ||||
| CreateTime | Y | Y | Y | Y | |
| CurrentDateTime | Y | Y | Y | Y | |
| CurrentUtcDateTime | Y | Y | Y | ||
| Day | Y | Y | Y | Y | |
| DayOfYear | Y | Y | Y | Y | |
| DiffNanoseconds1 | Y | Y | Y | Y* | |
| DiffMicroseconds1 | Y | Y | Y | Y* | |
| DiffMilliseconds1 | Y | Y | Y | Y* | |
| DiffSeconds1 | Y | Y | Y | Y* | |
| DiffMinutes1 | Y | Y | Y | Y* | |
| DiffHours1 | Y | Y | Y | Y* | |
| DiffDays1 | Y | Y | Y | Y* | |
| DiffMonths1 | Y | Y | Y | Y* | |
| DiffYears1 | Y | Y | Y | Y* | |
| GetTotalOffsetMinutes1 | Y | ||||
| Hour | Y | Y | Y | Y | |
| Millisecond | Y | Y | Y | Y | |
| Minute | Y | Y | Y | Y | |
| Month | Y | Y | Y | Y | |
| Second | Y | Y | Y | Y | |
| Truncate (datetime exp) | Y | Y | Y | Y | |
| Year | Y | Y | Y | Y |
1. This canonical function is supported only in Version 9.7 Fix Pack 4 and later.
In Version 9.7 Fix Pack 6 and later, the following table lists store-specific functions that the IBM entity provider supports. The store-specific functions are specific to DB2 for Linux, UNIX, and Windows server, DB2 for z/OS server, DB2 for i server, and Informix database server. The store-specific functions belong to the IBM.Data.DB2.Entity.DB2Functions class. You can improve the application performance by specifying DB2 and Informix functions in the LINQ to Entities queries. Specifying DB2 and Informix functions in the LINQ to Entities queries causes these functions to become part of the SQL query that is executed on the database server.
| LINQ function name | DB2 server | Informix database server |
|---|---|---|
| Acos | Yes | Yes |
| Asin | Yes | Yes |
| Atan | Yes | Yes |
| Atan2 | Yes | Yes |
| Cos | Yes | Yes |
| Exp | Yes | Yes |
| Log | Yes | Yes |
| Log10 | Yes | Yes |
| Sin | Yes | Yes |
| SquareRoot | Yes | Yes |
| Tan | Yes | Yes |
The following example demonstrates a call to a store-specific function Asin within a LINQ to Entities query:
//CREATE TABLE EF_TEST1(ID INTEGER,NAME CHAR(12),C1 FLOAT,PRIMARY KEY(ID))
using (sampledbEntities ContextObj = new sampledbEntities())
{
var query = from Tab1 in ContextObj.EF_TEST1
where IBM.Data.DB2.Entity.DB2Functions.Asin( Tab1.C1 ) < 1
select Tab1;
foreach (var cust in query)
{
Console.WriteLine("ID = {0}, NAME = {1}",
cust.ID.ToString(),
cust.NAME.Trim() );
}
}
//SQL sent to the server:
//SELECT
//Extent1.ID AS ID
//Extent1.NAME AS NAME,
//Extent1.C1 AS C1
//FROM NEWTON.EF_TEST1 AS Extent1
//WHERE (ASIN(Extent1.C1)) < CAST(1 AS float)Examples:
1) var query = from o in context.Orders
where o.ShipCity == "Seattle"
select o;
var result = query.First();
2) var mexico =
context.OrderDetails.Where(od => od.Order.ShipCountry == "Mexico").Select(od => od.Product);
var canada =
context.OrderDetails.Where(od => od.Order.ShipCountry == "Canada").Select(od => od.Product);
var query = mexico.Intersect(canada);
3) var query =
context.Customers.Select(e => e).Except(context.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Customer));
4) var query = context.Orders.Include("OrderDetails").Top("1");
5) var query = context.Orders.Include("OrderDetails").Include("OrderDetails.Product").Take(3).Select(o => o);For information about Entity Framework limitations that are associated with updating the database model, see the troubleshooting technote.