When using the ADO.NET Entity Framework, an entity’s keys may be marked as server generated. This enables the database to generate a value for the key on insertion or entity creation. Additionally, zero or more properties of an entity may be marked as server-generated values. A SQL Server Compact database may generate default values for null-valued properties when an entity is created or updated. This might happen if a default value has been specified as part of the column definition.

This release of the SQL Server Compact does not support entities with server-generated keys or values when it is used with the ADO.NET Entity Framework, although the ADO.NET Entity Framework allows you to define entity types with server-generated keys or values in the conceptual schema definition language (CSDL) or store schema definition language (SSDL) files.

LIMIT and TOP expressions are supported in paging queries. Paging queries are intended to support stateless paging (scrolling or windowing) through the results of a query. Paging queries in the ADO.NET Entity Framework can be specified by using the LINQ Skip and Take operators. For example:

LINQ:

customers.OrderBy(c => c.Name).Skip(10).Take(20) 

Paging queries can also be expressed by using the SKIP, LIMIT, and TOP constructs of Entity SQL. For example:

Entity SQL:

SELECT value c 
FROM NW.Customers AS c 
ORDER BY c.Name skip 10 limit 20; 

This release of SQL Server Compact does not support SKIP expressions in paging queries when it is used with the ADO.NET Entity Framework. On the other hand, the LIMIT and TOP expressions are supported.

Entity SQL supports multiple joins including inner joins, left outer joins, and full outer joins.

This release of SQL Server Compact does not support Entity SQL queries with full outer joins that use the ADO.NET Entity Framework. For example, the following query is not supported:

Entity SQL:

SELECT c.Name, c.Id, o.Id 
FROM NW.Customers AS c full 
OUTER JOIN NW.Orders AS o ON c.Id = o.CustomerId 

Entity SQL enables a COLLATE sub-clause to be specified as part of each key in an ORDER BY clause. The COLLATE sub-clause is applicable only for string-valued expressions, and determines the comparison semantics to use for that expression.

This release of SQL Server Compact, when it is used with the ADO.NET Entity Framework, does not support the use of a COLLATE sub-clause in the ORDER BY clause of an Entity SQL query. For example, the following query is not supported:

Entity SQL:

SELECT value c 
FROM NW.Customers AS c 
ORDER BY c.Name COLLATE Traditional_Spanish_ci_ai 

In SQL Server Compact, the following queries cause an error message:

Entity SQL:

( CAST ( 1 AS Edm.Int16) % CAST ( 1 AS Edm.Decimal) ) 

Transact-SQL:

SELECT cast (1 as smallint) %cast (1 as decimal(28,4)) 

When you run such a query, the following error message will be displayed: "Modulo is not supported on real, float, money, and numeric data types. [ Data type = numeric ]"

If you try to write Entity SQL and Transact-SQL queries that use DISTINCT in aggregates (max, min, sum, count), a "Not supported" exception will be thrown. The following example demonstrates an Entity SQL query that uses DISTINCT in aggregate count.

Entity SQL:

SELECT count(distinct [TaskId]) FROM ArubaContainer.TaskSet AS [Task]

The ADO.NET Entity Framework enables time-outs for commands to be specified by using the ObjectContext.QueryTimeout property or the EntityCommand.CommandTimeout property.

This release of SQL Server Compact does not support time-outs when it is used with the ADO.NET Entity Framework. In other words, the command time-out must not be set to a value other than zero. If a connection time-out property is set, a NotSupportedException(“CommandTimeout”) exception is thrown by the SQL Server Compact database.

The ADO.NET Entity Framework provides support for both Unicode and non-Unicode strings. This release of SQL Server Compact supports only Unicode strings. The literal <literal> of type 'String' is not supported by the current provider. The "near constant literal" exception is thrown by SQL Server Compact in non-Unicode strings.