Entity Framework – SQL Server CE

This post is based upon trying to get a simple SQL Server CE database up and running with Entity Framework. Something that should have been simple based upon my experience with SQL Server, but there’s a few gotcha’s I came across so I thought I’d post them.

First off, for this I’m using Visual Studio 2010 for this application (I will be using NuGet as well).

So to start off create a Console Application
Right mouse click on the project and select Manage NuGet Packages
In the search textbox type EntityFramework
Installed EntityFramework (accept licenses etc). Note: I did have a strange problem where it complained about the project or the likes being used by another process, clicking Install again worked fine.

At this point I decided to create my model in a Code First fashion, refer to my post for more info. on that.

So I’ve been working on and off (less on than off) on a simple little RSS reader, just using it to try out ideas etc. So in this post I’m going to start creating a little SQL Server CE database for it. The database design will not be comprehensive and you may prefer to implement it differently, feel free to do so.

I start off by creating the following classes

public class Feed
{
   public Guid Id { get; set; }
   public string Title { get; set; }
   public string Address { get; set; }
   public string Author { get; set; }
   public string Homepage { get; set; }
   public DateTime? LastUpdated { get; set; }
   public string Comment { get; set; }

   public virtual IList<Post> Posts { get; set; }
}

public class Post
{
   public Guid Id { get; set; }
   public string Content { get; set; }

   public virtual Feed Feed { get; set; }
}

public class FeedContext : DbContext
{
   public FeedContext(string connectionString) :
	base(connectionString)
   {  
   }

   public DbSet<Feed> Feeds { get; set; }
   public DbSet<Post> Posts { get; set; }
}

Note: You may have noticed I’m using GUID’s for what will be our primary keys. I actually started with integers but found out that I would need to generate the id values myself and whilst it’s do-able I decided to switch to a key I could create more easily. In the case of the Feed table we might actually be happy with the Address being the key, I’ll leave it to the reader to decide how they’d prefer to proceed with this, either use GUID’s or create your own integer key generator or switch, in the case of the Feed to using the Address.

Next I create the code in Program.Main to simply create the database for me based upon my model

using (FeedContext context = new FeedContext("Data Source=c:\\Dev\\Feed\\Feed.sdf"))
{
   context.Database.Create();				
}

Obviously change the Data Source to point to a valid folder

I’m going to delete the App.config to remove all the code that was created during the installation of EntityFramework for this example, so feel free to do the same (you can always put it back later).

Deleting the App.config means we’re missing information on the DefaultConnectionFactory to be used, so add the following line above the using(FeedContext…) line that we added previously.

Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.3.5");

If we run the code now we’ll hit another issue – “CreateDatabase is not supported by the provider.”. It appears we cannot generate a database from our model using SQL Server CE’s 3.5 provider.

So we are going to have to code the DB ourselves, either via the Server Explorer in Visual Studio or via SQL Server Management Studio (or any other alternative out there). I’m going to use SQL Server Management Studio as I find the Server Explorer route limiting.

In SQL Server Management Studio

Click on the Connect button
Choose SQL Server Compact
Click the drop down on the Database file combo box
Select New Database
Enter the location and name of your file, i.e. c:\Dev\Feed\Feed.sdf
Press OK then Yes where asked whether you want to continue with a blank password
Now press the connect button

If not selected, select your new DB and press the new query button, then paste and run the following

CREATE TABLE Feeds(
	Id uniqueidentifier NOT NULL,
	Title nvarchar(100) NOT NULL,
	Address nvarchar(100) NULL,
	Author nvarchar(100) NULL,
	Homepage nvarchar(100) NULL,
	LastUpdated datetime NULL,
	Comment nvarchar(100) NULL,
    PRIMARY KEY (Id)
)
GO
CREATE TABLE Posts(
	Id [uniqueidentifier] NOT NULL,
	Content [nvarchar](100) NULL,
	FeedId [uniqueidentifier] NULL,
    PRIMARY KEY (Id)
)
GO
ALTER TABLE Posts ADD CONSTRAINT FK_Feed_Post FOREIGN KEY (FeedId) REFERENCES Feeds(Id)

At this point we should now have our code model and our DB model in sync.

Note: If you’re wondering why I didn’t just generate the code model from the DB, it’s a fair question. The reason I didn’t is I wanted the minimalism of Code First plus, I did try a Model First approach as well and I couldn’t get it to use the DbContext and POCO’s.

I’m going to finish up by added a couple of bits of data into the DB and then retrieve them. So first change your Program.Main code to

using (FeedContext context = new FeedContext(@"Data Source=c:\Dev\Feed\Feed.sdf"))
{
   Feed a = new Feed
   {
      Id = Guid.NewGuid(),
      Address = "http:/a/rss",
      Title = "rss a",
      Posts = new List<Post>
      {
         new Post
         {
            Id = Guid.NewGuid(),
            Content = "Item 1a"
	 },
         new Post
	 {
	    Id = Guid.NewGuid(),
            Content = "Item 2a"
         }
      }
   };

   Feed b = new Feed
   {
      Id = Guid.NewGuid(),
      Address = "http:/b/rss",
      Title = "rss b",
      Posts = new List<Post>
      {
         new Post
         {
            Id = Guid.NewGuid(),
            Content = "Item 1b"
	 },
         new Post
	 {
	    Id = Guid.NewGuid(),
            Content = "Item 2b"
         }
      }
   };
				
   context.Feeds.Add(a);
   context.Feeds.Add(b);

   context.SaveChanges();
}

Note: There’s an oddity, possibly in the way I’ve created my data or specific to SQL Server CE, I’m not sure which yet whereby the above will fail against SQL Server CE but work against SQL Server. If we add the following it works.

public class Post
{
   // other properties
   public Guid FeedId { get; set; }
}

Now if you retrieve the data like the following

foreach(Feed f in context.Feeds)
{
   Console.WriteLine(f.Address);
   foreach(Post p in f.Posts)
   {
      Console.WriteLine("\t\t" + p.Content);
   }
}

All should be as expected.

My Memory

Outsourcing my memory and thoughts (and other ramblings) to the web

Entity Framework – SQL Server CE