My First Entity Application Against SQLCE

Now that SQL Server Compact 3.5 SP1 Beta is released it’s time to take the first step, making your first entity application using SQLCE as a backend.

In his previous post, Ambrish gave steps on how to install SQL Server Compact SP1 Beta, along with Ado.Net Entity Framework Beta 3 and writing a small program to query the Northwind Database via LINQ.

In this post, I will briefly walk you through what Entity Framework Beta 3 has offer to, and how can we leverage it to write different types of entity queries against SQL Server Compact.

Going by EF Definition at:

The most unique selling point in my opinion becomes the part where you can run the same program/application against any backend (assuming the database schema is same), you have to just modify connection strings. Let’s say even if the schema is little different, you can get that easily fixed in your metadata files, and boom, your app works.

I am including few screenshots which will guide you on how to go by making your own application and the complete program.

Please do read the previous post (by Ambrish) carefully as I will assume that you are done with the setup and we are ready to go. For those who could get things set up I am posting some screen shots to help you with that.

First step is to include an Ado.Net Entity Data Model into your project, give it a name that makes sense, I gave it SSCNorthwind.

Add Ado.NET Entity Data Model


Generate Model From DB


The screenshot below, shows the database file that we selected, its connection string that will go into your App.config file and also the name you want to give it. For simplicity I chose NorthwindEntities.

Choosing the Connection and Saving App.Config


The screenshot below shows you how to include the database file that you placed in your project root for generating data model.

Select Northwind DB from your samples directory


The wizard below allows you to choose the tables that you want to be part of entity set, as SQL CE does not support stored procedures and views as of now, we will ignore those two options.

Choose the Tables you want. SP and Views are not supported for SQLCE


The red box in below shows the change that we have to make in App.Config to make this working J i.e. renaming the provider:

provider=Microsoft.SqlServerCe.Client.3.5 to provider=System.Data.SqlServerCe.3.5

This is being fixed, so in RTM version you won’t have to do this manual step.

You can see that the name of Entity set is NortwindEntities, so whenever we make connection to this set, the connection string provided in App.Config file will be used.





This is how the solution explorer should look now:


If you face any error while adding Northwind.sdf file into your project, just ignore it for now.

I have tried to include some comments in my program below to help you understand what’s going on.

Points to notice:

To connect to a Database using an Entity Connection, you can do it in two ways.

  1. First way is to specify your own connection string, like I have done at the start of program. Using that you can directly create an Entity Connection and work in the same way you used to work with SqlCeConnection and SqlCeCommand. That way can be seen in ESQL Query region.

  2. The second approach is using Contexts or what we call here NorthwindEntities, (remember at top we renamed Entities to this for better understanding). For using context, you can do that in multiple ways

    • First, you can give a connection string and it will create the set.

    • Second, you can also first create an Entity Connection like above and then pass it on the NorthwindEntities, which is what I have done in Object Query section.

    • And the last approach for which we created App.Config in first place. You don’t specify anything at all, the entity set (NorthwindEntities) picks up your connection string from App.Config and creates a connection. LINQ to Entites Query section is done using this approach.

Program (Contents of Program.cs):

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.EntityClient;

using NorthwindModel;

using System.Data;


namespace MyFirstEntityApplicationForSSC


    class Program



        #region Static Declarations


        //SQLCE Connection string for the sample database Northwind.sdf

        static string _sqlCeConnnectionString = @”Data Source = Northwind.sdf”;


        //entityConnectionString: If your metadata(CSDL, SSDL and MSL, or in this case EDMX) files lie in the same place as application, you can use “.”

        static string _entityConnectionString =

            String.Format(@”metadata=.;Provider=System.Data.SqlServerCe.3.5;provider connection string=””{0}”””, _sqlCeConnnectionString);


        //New Values that you might want to use for changing the data

        static string _newFirstName = “AGENT”;

        static string _newLastName = “SMITH”;

        static string _newCountry = “MATRIX”;


        #endregion Static Declarations


        #region Main

        /// <summary>

        /// MAIN: It calls the three different functions for following type of queries

        /// Description in layman language


        /// ESQL – This takes you back to old .Net 2.0 days where you used to set T-SQL statements and execute them, just that it here works on entities now instead of tables

        /// Example Demonstrates: Simple Select Query using Entity SQL.


        /// Object Query – Working on Data Objects was never so easy. This type of queries give you the power to work on data objects and most of all intellisense helps with things you can do

        /// Example Demonstrates: Query and Updation of data and then Re-Querying


        /// LINQ to Enity – LINQ means Language Integrated Query. This is a new and powerful way of writing queries that are checked for syntax at compile time itself. So, chances of failing in query logic at runtime become minimal

        /// Example Demostrates: Query the data using default settings for connection in App.Config that was configured via EF Designer.

        /// </summary>

        /// <param name=”args”></param>

        static void Main(string[] args)


            Console.WriteLine(“SQL CE Northwind Entity App:”);



                EntityConnection entityConnection = new EntityConnection(_entityConnectionString); //Creating a new entity connection using the entity connection string



                Console.WriteLine(“\nESQL Query:”);

                ESQL_Query(entityConnection); //This does not use context, it’s a simple esql command execution


                Console.WriteLine(“\nLINQ To Entity Query:”);

                LINQ_To_Entity_Query(); //This will make a new connection using the connection string in the App.Config file


                Console.WriteLine(“\nObject Query:”);

                ObjectQuery(entityConnection); //This will create the context with existing connection




            catch (Exception ex)


                Console.WriteLine(“\nFAIL! Oops, it was not expected, an exception has been thrown, details below:\n”);



            Console.WriteLine(“\nPress a Key to exit…”);



        #endregion Main



        #region ESQL_Query

        /// <summary>

        /// ESQL_Query

        /// (Executing a simple ESQL query against Northwind Entity Set)

        /// Using the entity connection which is passed on as parameter

        /// it executes a simple entity command on the Employees entity set in the Northwind Context.

        /// The result of that query is later on printed on the screen using a data reader.

        /// </summary>

        /// <param name=”entityConnection”></param>

        private static void ESQL_Query(EntityConnection entityConnection)


            EntityCommand entityCommand = entityConnection.CreateCommand();

            entityCommand.CommandText = @”Select Emp.Employee_Id as Id, Emp.First_Name as Name from NorthwindEntities.Employees as Emp order by Emp.Employee_Id”;


            EntityDataReader entityDataReader = entityCommand.ExecuteReader(CommandBehavior.SequentialAccess);

            //Note: You have to use CommandBehavior as SequentialAccess, otherwise an exception will be thrown


            while (entityDataReader.Read())


                for (int i = 0; i < entityDataReader.FieldCount; i++)

                    Console.Write(entityDataReader[i].ToString() + “\t”);




        #endregion ESQL_Query



        #region LINQ_To_Entity_Query

        /// <summary>

        /// LINQ_To_Entity_Query

        /// (Executing a simple LINQ query against Northwind Entity Set)

        /// This function creates a new Northwind Context based on the setting provided for it in the App.Config File

        /// It executes a simple LINQ query on the Employees entity set in the Northwind Context.

        /// The result of that query is later on printed on the screen using the IQueryable structure.

        /// </summary>

        private static void LINQ_To_Entity_Query()


            NorthwindEntities nwind = new NorthwindEntities(); //Uses the settings for connection string in App.Config File


            IQueryable<string> emps = from e in nwind.Employees where (e.Employee_ID%2)==0 select e.First_Name; //Even Number Employee Ids


            foreach (string e in emps)






        #endregion LINQ_To_Entity_Query



        #region ObjectQuery

        /// <summary>

        /// ObjectQuery

        /// (Executing a simple Object query against Northwind Entity Set and Updating the data)

        /// This function creates a new Northwind Context based on the entity connection thas has been passed to it as parameter

        /// It executes a simple Object query on the Employees entity set in the Northwind Context.

        /// The result of that query is later on printed on the screen and some changes are made to it.

        /// The changes are then saved using SaveChanges on Context and then re-querying them to see if things went fine.

        /// </summary>

        /// <param name=”entityConnection”></param>

        private static void ObjectQuery(EntityConnection entityConnection)


            NorthwindEntities nwind = new NorthwindEntities(entityConnection);


            var emps = nwind.Employees.Where((delegate(Employees e)


                                                    return e.Employee_ID > 5;




            //var emps = nwind.Employees.Where(“it.Employee_ID > 5”); //This query is also equivalent to above one and will give same results

            //var emps = nwind.Employees.Where(“it.Employee_ID > @empid”,new ObjectParameter(“@empid”,5)); //This query is also equivalent to above one, but it uses parameters for the values


            foreach(Employees e in emps)


                Console.WriteLine(e.Employee_ID + “\t” + e.First_Name + “\t” + e.Last_Name + “\t” + e.Country);

                if (e.Employee_ID == 10) //As soon as we encounter employee id as 10, we want to change some data


                    Console.WriteLine(“Changing Data”);

                    e.First_Name = _newFirstName;

                    e.Last_Name = _newLastName;

                    e.Country = _newCountry;




            Console.WriteLine(“Saving Data Changes”);

            nwind.SaveChanges(); //Any changes made to the context are saved using this command


            emps = nwind.Employees; //This is simplest way to select all employees. If you happen to choose particular data you may modify the query accordingly, like nwind.Employees.Where(“it.Employee_ID = 10”)


            Console.WriteLine(“Display Data Again:”);

            foreach (Employees e in emps)


                Console.WriteLine(e.Employee_ID + “\t” + e.First_Name + “\t” + e.Last_Name + “\t” + e.Country);




        #endregion ObjectQuery




The output of the program would look like this:

SQL CE Northwind Entity App:


ESQL Query:

1       Nancy

2       Andrew

3       Janet

4       Margaret

5       Steven

6       Michael

7       Robert

8       Laura

9       Anne

10      Albert

11      Tim

12      Caroline

13      Justin

14      Xavier

15      Laurent


LINQ To Entity Query:









Object Query:

6       Michael Suyama  UK

7       Robert  King    UK

8       Laura   Callahan        USA

9       Anne    Dodsworth       UK

10      Albert  Hellstern       USA

Changing Data

11      Tim     Smith   USA

12      Caroline        Patterson       USA

13      Justin  Brid    France

14      Xavier  Martin  France

15      Laurent Pereira France

Saving Data Changes

Display Data Again:

1       Nancy   Davolio USA

2       Andrew  Fuller  USA

3       Janet   Leverling       USA

4       Margaret        Peacock USA

5       Steven  Buchanan        UK

6       Michael Suyama  UK

7       Robert  King    UK

8       Laura   Callahan        USA

9       Anne    Dodsworth       UK


11      Tim     Smith   USA

12      Caroline        Patterson       USA

13      Justin  Brid    France

14      Xavier  Martin  France

15      Laurent Pereira France


Press a Key to exit…


If you want your changes to persist or not to persist, modify the properties for the Northwind.sdf file in your solution properties as per your desire. By default it is “copy if newer” which means the database file in your execution folder will retain the changes. By changing it to “copy always” you can refresh your database each time.

I hope now you can now go back and start writing advanced programs using various techniques available.

Ado.Net Entity Framework is a new weapon to the list of a current developer’s arsenal, opening multiple ways in which he can write his programs without worrying about the under lying intricacies.

The shift to entity plane seems very lucrative, easy and promising. And what more, all of this comes with handy tools which make life easy. Writing your code from the word Go!

The above topic was in relation to LINQ to Entities, if you are looking for LINQ to SQL refer to this blog post:

Happy Programming!


Ravi Tandon

SDET, SQL Server Compact

Comments (8)

  1. Lexis says:

    It’s all fine, but….

    How Add and Save new entity object?

    Trouble in this: "4.0 Known Issues … 4.1.1 There is no support for server-generated keys and server-generated values"…

    Entity framework trying to genrate it…((( How to fix?

  2. J.C. Novoa says:

    Is Microsoft planning on building a browser-based database add-on (ActiveX/CAB) to enable the installation and access of relational data via SQL CE for storing user data for a web application (similar to iso storage, but against a SQL CE engine). Similar to what Google is doing with Gears?

    I was thinking on building an ActiveX browser control in .Net, and have that install and access SQL CE for my web application (not mobile or desktop)…. are there any efforts on building something like this for SQL 2008 perhaps?

  3. David Jacobson says:

    Is it possible to get a clear statement about whether or not Visual Studio 2008 express editions are supported as a development environment for Sql Server Compact edition?

  4. SQLCEBLOG says:

    Visual Basic 2008 Express Edition and Visual C# 2008 Express Edition both support application development for SQL Server Compact 3.5



  5. Ricardo says:


    I want to use Entity Framework for Windows Mobile. Is it possible ? Is this the right tool, with the Entity FrameWork Beta 3 ?

    Thanks a lot!

  6. SQLCEBLOG says:


    The 4.1.1 known issue you are talking about will cause problems only when you try to do DML operations on a table with Identity or Rowversion column.

    In terms of Select Statements there is no issue, everything should work.

    If you need to have DML statements in your application against SQLCE, you should not have Identity and Rowerversion column in schema. Rest everything will work fine and Entity Framework will add the data for you.

    If you still face any issues after this, please let me know, I will be glad to help.


    Ravi Tandon

  7. ms44cn says:

    I have installed all the msi you metioned .

    But when I choose the datasource , it has no sqlce 3.5 provider .

    So what’s the problem here ?

  8. SQLCEBLOG says:


    I think you missed the part where you need to change the provider name from Microsoft.SqlServerCe.Client.3.5 to System.Data.SqlServerCe.3.5 in App.Config

    Thats why it can’t find the provider.

    This manual step will removed when 3.5 SP1 ships.

    Also check in App.Config that you are referring to the right database file in data source.