IBM Data Server LINQ Entity Framework Limitations

Known Limitations
-----------------

Invocation of store specific functions is not supported.
Invocation of Canonical functions other than the aggregates count, sum, min, max, avg is not supported.
Connection properties set in the Server Explorer Add Connection dialog (eg. trusted context, filtering options, table type) do not get passed to the Entity Framework connections - this is an acknowledged limitation with the Server Explorer.

The following list has additional limitations specific to individual supported servers.

Note: Unless stated otherwise, the below limitations are applicable to all supported versions of the particular server type.

DB2 for Linux, Unix, and Windows
--------------------------------

An exception similar to "The data types of corresponding columns are not compatible in a fullselect that includes a set operator or in the multiple rows of a VALUES clause of an INSERT or fullselect" might be generated when OfType or TypeOf ('is' operator) kinds of expressions are used, and the projection list includes any large object types. Examples:
```
1) var query = context.Employees.OfType<PreviousEmployee>();
2) var query = from e in context.Employees where e is DBModel.PreviousEmployee select e;
```

DB2 for IBM i
-------------

'Generate from database' option of Entity Data Model (EDM) wizard/designer is not supported. An EDM has to be created manually by using an 'Empty model' option. Fixes for iSeries APARs SE34943 and SE35512 are expected to fix this issue (PTFs SI32963 and SI32962 for V5R4).
Invocation of Store specific functions is not supported.
An exception similar to "The SQL statement failed because of a non-severe system error. Subsequent SQL statements can be processed." might be generated for certain queries with correlated subqueries. Some typical scenarios include: A correlated subquery with paging, AnyElement over a correlated subquery or over a collection produced by navigation, LINQ queries that use grouping methods that accept an element selector (example below), A query that has a DEREF construct over a REF construct etc. Example:
```
1) var query = from p in context.Products
               group p by p.Category.CategoryID into g
               select new
               {
                   CategoryID = g.Key,
                   CheapestProducts = from p2 in g
                                      where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
                                      select p2
               };
```

DB2 for z/OS
------------

Data type 'real' is not supported. Applications need to either use 'float' in the server table schema, or specify the type as 'float' in the client schema (EDM) even if the actual type on the server is 'real'.
An exception indicating some kinds of syntax error might be generated for certain queries with correlated subqueries. Some typical scenarios include: A correlated subquery with paging, AnyElement over a correlated subquery or over a collection produced by navigation, LINQ queries that use grouping methods that accept an element selector (example below), A query that has a DEREF construct over a REF construct etc. Example:
```
1) var query = from p in context.Products
               group p by p.Category.CategoryID into g
               select new
               {
                   g.Key,
                   MostExpensiveProducts = from p2 in g
                                           where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
                                           select p2
               };
```
V8/V7 specific: An exception similar to "The data types of corresponding columns are not compatible in a fullselect that includes a set operator or in the multiple rows of a VALUES clause of an INSERT or fullselect" might be generated when OfType or TypeOf ('is' operator) kinds of expressions are used, and the projection list includes any large object types. Examples:
```
1) var query = context.Employees.OfType<PreviousEmployee>();
2) var query = from e in context.Employees where e is DBModel.PreviousEmployee select e;
```

V8/V7 specific: An exception indicating some kinds of syntax error might be generated for queries that include Take/Top/First/Intersect/Except expressions. The result of the queries that include those expressions is undefined. 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);

IBM Informix Dynamic Server
---------------------------

The Entity Framework Designer will not display any tables, views or procedures with the schema "informix"

IBM Informix Dynamic Server may crash or return incorrect results when the following type of queries that access related entities are used. This happens especially when scalar subquery kinds of scenarios are generated. While the crash behavior is specific to 11.10.xC1, all other supported versions return incorrect results. In the first example query below, the order of the result set will be incorrect since the ordering is based on the count function used in the subquery.

1) SELECT VALUE c2.c FROM 
(SELECT c,  ANYELEMENT(SELECT VALUE Count(o.OrderId) FROM c.Orders AS o) AS orderCount FROM 
Customers AS c) AS c2 ORDER BY c2.orderCount
2) var query = from c in context.Customers
               group c by c.Address.Region into g
               select new
               {
                   Region = g.Key,
                   FreightTotal = g
                       .SelectMany(c2 => c2.Orders)
                       .Sum(o => o.Freight)
               };
3) SELECT VALUE c2.c FROM 
(SELECT c,  ANYELEMENT(SELECT VALUE Count(o.OrderId) FROM c.Orders AS o 
WHERE Year(o.OrderDate) = 1996) AS orderCount FROM Customers AS c) AS c2 
WHERE c2.OrderCount > 0 ORDER BY c2.orderCount";
4) var query = from p in context.Products
               where p.OrderDetails.Count(od => od.Order.Customer.Address.Country == p.Supplier.Address.Country) > 2
               select p;

An exception similar to "Corresponding column types must be compatible for each UNION statement" might be generated when OfType or TypeOf ('is' operator) kinds of expressions are used, and the projection list includes any large object types. Examples:
```
1) var query = context.Employees.OfType<PreviousEmployee>();
2) var query = from e in context.Employees where e is DBModel.PreviousEmployee select e;
```

An exception similar to "Not implemented yet" or "Syntax error" might be generated for certain queries with correlated subqueries. Some typical scenarios include: A correlated subquery with paging, AnyElement over a correlated subquery or over a collection produced by navigation, LINQ queries that use grouping methods that accept an element selector (example below), A query that has a DEREF construct over a REF construct etc. Example:
```
1) var query = from p in context.Products
               group p by p.Category.CategoryID into g
               select new
               {
                   g.Key,
                   ExpensiveProducts = from p2 in g
                                       where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
                                       select p2
               };
```

IBM UniVerse and UniData (U2) Data Servers
------------------------------------------

EDM Tools and the Entity Designer is not supported.

Due to U2 SQL engine limitations, the following features of LINQ to Entity are not supported in U2:

Group
Skip
Functions, such as TRIM, LEFT, RIGHT, DIFFERENCE, etc
Except
Intersect
Concat *
Union with Distinct
OrderBy with FK Collection
Instead of Concat, use Union