By using this site, you acknowledge that you have read and understand our Cookie and Privacy policy. Your use of Kontext website is subject to this policy. Accept

Working with SQL Server Compact 4.0 using Entity Framework 6 and ADO.NET

11837 views last modified about 5 years ago Raymond

SQL Server Compact 4.0 (CE 4.0) is a free SQL Server embedded database ideal for building standalone and occasionally connected applications for mobile devices, desktops, Web clients and others. In one of my projects, I used it as the database for logging errors, which assumes the errors will only occur occasionally. It is not very straightforward compared with working with SQL Server, but it is quite handy with the helps of some tools and components.

SQL Server Compact Toolbox

Visual Studio doesn’t build in the toolbox for SQL Server Compact. However you can install one AddIn “SQL Server Compact Toolbox” which can help you to create, manipulate and generated scripts for SQL CE databases. It even allows you to analyze the execution plans.

You can download this Addin from both MSDN or CodePlex.



This tool provides both database and table level functions, which enables you to generate EntityFramework models and SQL scripts.

Database level utilities include the following items:


The table level functions consist of :


ADO.NET and System.Data.SqlServerCe 4.0

You can install System.Data.SqlServerCe package from NUGET in Visual Studio. By referencing to the library System.Data.SqlServerCe.dll, you can communicate with SQL CE database as you did for SQL Server. There are a  number of derived classes which you can get familiar easily. E.g. SqlCeCommand, SqlCeConnection, SqlCeDataAdapter and SqlCeDataReader.

Learn more details and find more code samples at MSDN

The configuration in your app.config/web.config is similar like the following:

      <remove invariant="System.Data.SqlServerCe.4.0" />
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />


Entity Framework 6.0

You can also use ORM tool like Entity Framework 6.0 to manipulate data in SQL CE databases.  Before you start, you need to install two packages in NuGet: EntityFramework.SqlServerCompact 6.0 and EntityFramework 6.0.


System.Data.Entity.SqlServerCompact. SqlCeProviderServices need to be configured in the providers of Entity Framework configurations.

    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
        <parameter value="v12.0" />
      <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

If you forget to add this provider, you will encounter the following error:

No Entity Framework provider found for 'System.Data.SqlServerCe.4.0' ADO.NET provider. Make sure the provider is registered in the 'entityFramework' section of the application config file. See for more information.

If you configure it incorrectly, you will get the following error:

The Entity Framework provider type 'System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' for the 'System.Data.SqlServerCe.4.0' ADO.NET provider could not be loaded. Make sure the provider assembly is available to the running application. See for more information.

If you use “System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91” to specify the type in provider configuration you will get a different error (the reason is because SqlCeProviderFactory class is not inherited from DbProviderServices):

An exception of type 'System.InvalidOperationException' occurred in mscorlib.dll but was not handled in user code

Additional information: The 'Instance' member of the Entity Framework provider type 'System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' did not return an object that inherits from 'System.Data.Entity.Core.Common.DbProviderServices'. Entity Framework providers must inherit from this class and the 'Instance' member must return the singleton instance of the provider. This may be because the provider does not support Entity Framework 6 or later; see for more information.

Make sure you use the correct configurations and add the references to the assemblies to avoid all the above issues.

One you are done, you can generate the models using Wizard for the SQL Server Compact Toolbox:


Have fun in your development journey with SQL CE 4.0 + Entity Framework 6.0.

Related pages

Querying Teradata and SQL Server - Tutorial 1: The SELECT Statement

33432 views   7 comments last modified about 4 years ago

SELECT is one of the most commonly used statements. In this tutorial, I will cover the following items: Two of the principal query clauses—FROM and SELECT Data Types Built-in functions CASE expressions and variations like ISNULL and COALESCE. * The functio...

View detail

Querying Teradata and SQL Server - Tutorial 2 Filtering and Sorting

3502 views   2 comments last modified about 4 years ago

Tutorial –1 For Teradata developers, if you have no SQL Server installed, please go to the following link to download the SQL Server 2014 Expression Edition. ...

View detail

Querying Teradata and SQL Server - Tutorial 0: Preparation

3143 views   0 comments last modified about 4 years ago

In this serial, I will compare Teradata SQL with T-SQL with samples provided. This is mainly prepared for SQL Server DBAs, Developers and other users to help them quickly master the common used SQLs in Teradata platform. Similar to T-SQL, Teradata SQL implements most part of the ANSI SQL with ext...

View detail

[SQL Server 2012] 使用自带功能简化SQL

864 views   0 comments last modified about 6 years ago

在低版本SQL Server中,对于一些常用的查询功能,我们可能需要运用复杂的逻辑,如临时表,游标等等来实现,这样可能会导致代码难维护,同时产生性能问题。本文将简述用自带的功能去简单的实现这些操作。 注意:一些功能仅在SQL Server 2012以及以上版本支持。 1.查询分页 (OFFSET, FETCH) 在很多Web应用程序中,我们需要实现分页功能,在过去,我们往往通过top语句实现或者通过ROW_NUMBER;在2012中加入了FETECH以及OFFSET标准SQL中支持的功能。下面的例子是查询第11到20行数据: ...

View detail

将远程 Sql Server 服务器上的数据库备份到本地的方法

728 views   0 comments last modified about 10 years ago


View detail

SQL Server 附加没有日志文件ldf的数据库mdf的方法

829 views   0 comments last modified about 10 years ago

Keywords: how to attach database without log file

View detail

Add comment

Comments (0)

No comments yet.


  • enquiry[at]