Code Renaissance is about building great teams and great software. By exploring best practices, team interactions, design, testing and related skills Code Renaissance strives to help you create the team and codebase that you've always wanted.

Using SQLCE with the Entity Framework and Linq to Entity: Part 1

SQLCE is a compact database that is click once deployable and runs as part of your application (as opposed to SQL Express which has a much larger footprint and runs as a service). To make SQLCE so small and light weight a lot of things have been pulled out. Most notably its missing stored procedures but fortunately we can use LINQ to Entity to compensate for that.

So lets get started.

Create a new project. For simplicity sake I created a class library.

Add a SQL CE Database

Just click finish when the wizard appears.
Your Project should now look like this:

You can go ahead and delete AppDBDataSet.xsd… we won’t need it for what we’re doing. You can delete class1 too while you’re at it.

Now your project should look like this:

Now double-click on AppDB.sdf. You should be taken to your sever explorer which should look like this:

Right click on Tables and select Create table and enter the following values:

Press OK to accept the parent table. Now add another table.

Press OK to accept the child table. Your server explorer should now look like this:

Now right click on the child table and select table properties and then select Add Relations.

Enter the information below and click Add Columns

Your Table Relationship be listed as below.

Now click Add Relation.

Congratulations you’re now done with your SQLCE Database, now let’s create an entity mapping for it.

Now return to your solution explorer, right click the project file and select add new and select ADO.NET Entity DataModel (see below)

Click Add, then click Next until you get to the last page.

Click the check box for Tables and click finish. Your Solution Explorer should now look like

Congratulations your entity model has been created. Click on AppDBModel.edmx and let’s take a look at it:

Now right click on the class name Parent in your code and select refactor / extract interface (select all). Your code should now look like this.

And you should have a new interface that looks like this.

Next follow the same steps to create a Child class and interface. Your solution window should now look like this:

Ok, now you’ve generated interfaces for your entity classes and you can also use your partial classes to extend your generated entity classes without worrying about anything being lost when your entity classes are regenerated and updated.

You should now be able to access your data using standard linq to entity queries.

In Part-2 I'll cover CRUD operations using the Entity Framework and Linq to Entity.

3 - What do you think?:

royashbrook said...

Hi there. I was able to get this all working fine, however trying any of the cheap/quick/easy scaffolded apps produces errors for me. I have tried dynamic data entities/domain/linq2sql projects and all seem to fail telling me some or another particular operation isn't supported. .skip seems to be the main one i have run into thus far. just curious if you have had any better luck. it's easy enough to just write some crud stuff, but i was hoping to go cheap and fast and just utilize one of the existing scaffolding projects.

DH said...

Not certain what you mean by scaffolded apps; if you're referring to some resource out on the net please provide a link. What I can tell you is that a lot of the standard Linq methods are not available for SQLCE... it's a very lightweight framework and requires developers to make up for the missing functionality. I'd planned to cover this in my next post but I've gotten a bit tied up lately.

sriram s said...

Hi guys,

There is no design time support for SQL CE 4.0 CTP1 in VS2010. so you can not use SQL CE 4.0 with Entity Framework.

But can be created using few hacks.

Below is a link to Tutorial for using Entity Framework for SQL Ce.