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.