Entity Framework (SQL Server Compact)

  • Article

The Entity Framework is a set of technologies in ADO.NET that supports development of data-oriented software applications. The Entity Framework enables developers to work with data in the form of domain-specific objects and properties, such as customers and customer addresses, without having to concern themselves with the underlying database tables and columns where this data is stored.

By enabling developers to work with data at a greater level of abstraction, the Entity Framework supports code that is independent of any particular data storage engine or relational schema. Developers can create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema.

Entity Framework applications and services consist of a conceptual model, a storage model, and a mapping between the two. To meet the needs of developers who want to derive an Entity Data Model (EDM) from an existing database, the Entity Framework provides a set of tools that generate and validate an EDM and create programmable classes based on the conceptual model.

Through the EDM, ADO.NET exposes entities as objects in the .NET environment. This makes the object layer an ideal target for Language-Integrated Query (LINQ) support. LINQ to Entities allows developers to create flexible, strongly typed queries against the Entity Framework object context by using LINQ expressions and the LINQ standard query operators directly from the development environment. For more information about the Entity Framework and LINQ to Entities, see the Entity Framework documentation.

The SQL Server Compact 3.5 SP1 provides support for the Entity Framework for the Windows Desktop platform.

Entity Framework in SQL Server Compact

To use Entity Framework with SQL Server Compact, first install the Entity Framework. The Entity Framework is a component of the .NET Framework starting with the .NET Framework 3.5 Service Pack 1 (SP1).

The development support for SQL Server Compact is provided by Visual Studio. The Entity Data Model Designer support for the Entity Framework in Visual Studio is provided by the Entity Framework Tools. Note that the Entity Data Model Designer (Entity Designer) is a component of Visual Studio starting with Visual Studio 2008 Service Pack 1 (SP1). It is a visual tool used to create and edit an Entity Data Model (EDM). For more information about this tool, see the Entity Framework documentation.

If you want to use Visual Studio 2008 instead of Visual Studio 2008 SP1, install the "Microsoft SQL Server Compact 3.5 SP1 Beta for Entity Framework Beta 3" that enables integration between the Entity Designer and SQL Server Compact. For more information on how to install this Beta release, see Microsoft Download Center.

Starting with the SQL Server Compact 3.5 SP1 release, SQL Server Compact provides a managed assembly: System.Data.SQLServerCe.Entity.dll. The System.Data.SQLServerCe.Entity.dll assembly is used by the ADO.NET managed data provider System.Data.SqlServerCE.dll internally and supports accessing data described in an Entity Data Model (EDM).

When you install the SP1 release of SQL Server Compact 3.5 (SSCERuntime-ENU.msi), the System.Data.SQLServerCe.Entity.dll is installed under the folder %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5.

Limitations of the SQL Server Compact

Some limitations of the SQL Server Compact when used with the Entity Framework include the following:

  • SQL Server Compact does not support entities with server-generated keys or values when it is used with the Entity Framework.
    When using the 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. For more information, see the Store Generated Pattern topic in the Entity Framework documentation.
    SQL Server Compact does not support entities with server-generated keys or values when it is used with the Entity Framework, although the Entity Framework allows you to define entity types with server-generated keys or values. Data manipulation operation on an entity that has server-generated values throws a "Not supported" exception.

  • SQL Server Compact does not support SKIP expressions in paging queries when it is used with the Entity Framework. On the other hand, SQL Server Compact supports the LIMIT and TOP expressions in paging queries.
    Paging queries are intended to support stateless paging (scrolling or windowing) through the results of a query.
    The following example demonstrates how to write a paging query in the Entity Framework by using the LINQ Skip and Take operators. Note that SQL Server Compact does not support SKIP expression in a paging query:

    LINQ:

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

    The following example demonstrates how to write a paging query in the Entity Framework by using the SKIP, LIMIT, and TOP constructs of Entity SQL. Note that SQL Server Compact does not support SKIP expression in a paging query:

    Entity SQL:

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

  • SQL Server Compact does not support full outer joins that use the Entity Framework, although the Entity SQL supports the full outer joins. 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

    Note that SQL Server Compact supports inner joins, left outer joins, and right outer joins that use the Entity Framework.

  • SQL Server Compact does not support COLLATE sub-clauses in the ORDER BY clause of an Entity SQL query.
    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.

    SQL Server Compact, when it is used with the 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

  • Unlike SQL Server, SQL Server Compact does not support modulo operations (denoted by %) on real, float, money, and numeric data types.
    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 ]"

  • SQL Server Compact does not support DISTINCT in aggregates (max, min, sum, count).
    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]

  • SQL Server Compact does not support command time-outs when it is used with the Entity Framework.
    The Entity Framework enables time-outs for commands to be specified by using the ObjectContext.QueryTimeout property or the EntityCommand.CommandTimeout property.
    SQL Server Compact does not support time-outs when it is used with the 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.

  • SQL Server Compact supports only Unicode strings.
    The Entity Framework provides support for both Unicode and non-Unicode strings. 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.

  • SQL Server Compact does not support stored procedures and views.

Example

To learn how to create Entity Framework applications that use the SQL Server Compact database as a data source, see Creating an Entity Framework Application (SQL Server Compact).

See Also

Other Resources

Building Managed Applications (SQL Server Compact)

Help and Information

Getting Assistance (SQL Server Compact 3.5 Service Pack 1)