EF Code First and SqlServer Ce 4.0 – the simplest example ever.

Recently I needed to write a simple database application. I chose the Entity Framework Code First (Code Only) as a Object Relational Mapper (ORM) because its simplicity and quick development process.

If you did not yet heard about EF Code First then this is a good place to start.

Entity framework is a very powerful technology. It totally makes the whole dirty work which in the past was creating CRUD queries, managing connections, passing query parameters and so on. It offers few creating approaches depending on what we already have, a database schema, model classes or both.

Code first variant of Entity framework is the simplest approach. Using it the developer, in the most cases, does not need to know anything about the database, sql queries, and all database related stuff. He just need to know where he want to store the data, that’s all!

It is always better to show something on the examples. The real life application here is a little program to store work time. It just register when user press “Start work” and “Stop work” and stores these two times in the database. Additionally user may assign a project to the work time.

Before we start our sample we have to download Microsoft ADO.NET Entity Framework Feature Community Technology Preview 5 which is at the moment the latest ctp version of Entity Framework and contains Code First required libraries.

To support SqlServer Ce 4.0 we need to download this. At the moment this kind of database is yet supported by both Visual Studio (here the support will come with Service Pack 1 which is already available as Beta version) and Sql Server Management Studio, but we can use SQL Server Compact Toolbox Visual Studio plug-in which allows to query and explore SqlServer Ce 4.0 databases.

If we have all installed we can start creating our sample. We have following model classes

and we want to have these classes synchronizing with SqlServer Ce 4.0 database. In order to do this we create another class which will represent our database context

And in our main application somewhere in initialization we put following line of code

Ready! We have just coded the whole Data Access Layer of our application ;-).

Above line of code creates the default connection factory which in the result tries to connect to the database file located under main application directory and named as the database context class WorkTime.WorkContext.sdf.

If we want to have the connection string configurable from App.config file we have to add an connection string with the name matching our database context class name which in this example is WorkContext. We have to add it to the connectionStrings section and remember to remove above line of code to prevent overwriting the connection string.

Now our application tries to connect to SqlServer Ce 4.0 database located in main application folder and named Work.sdf.

Ok, our database and model is ready. Now it is enough to create the context object

and if we for example want to get all Work entities with some filter we just use LinqToEntities

That’s all we have whole model and database layer of our application done. now we can focus on a business logic and UI design. That is what the “Code First” name states for. Developer can focus on the code and does not have to worry about the database model or schema, because all that is managed by the framework.

Lets see how does the database look like

As you can see the Entity Framework creates all necessary keys. By default the integer typed Id field is treated as primary key for the entity table. Additionally the Framework detects the reference in Work class to the WorkProject class and created appropriate foreign key. This is generally very convenient functionality that we do not need to worry about the basics of the database schema.

Someone could ask: what if we want to have some custom mappings and fields. EF Code First naturally allows that, but it requires using either Data Annotations attributes or Fluent API. Data Annotations is the set of attributes that are applied directly to the model class and informs the EF how to deal with the field. Fluent API allows to dynamically shape the model and database and gives a mass of design possibility.
An good description of Data Annotations attributes can be found here.