In the past I have written about dealing with data on Windows Phone 7, including WCF Services, WCF Data Services and Isolated Storage. One thing that was missing in the initial release is native RDBMS, such as SQL CE. Luckily, Mango update that should be released later this year finally exposes SQL CE to developers.
Even I admit that Isolated Storage DB was a useful tool for me in absence if SQL CE. That is why I wrote my project on CodePlex. Having said that, I would likely not take on this task if SQL CE was available in Windows Phone 7. As a good little developer, I wanted to spend some time and educate myself on how to use SQL CE in Mango. This post describes my adventures on the subject.
First of all, you have download and install Mango (7.1) update. Current update version is beta 2. You can download it here.
Now, let’s create new project and select 7.1 as target framework. Now, let’s get acquainted with Linq to SQL API for Windows Phone 7. Yes, interestingly enough ORM framework that is available on the phone is L2S not Entity Framework. I will not be commenting on this subject, let’s just look at what is available.
We are using code only approach with L2S, so we are going to define some tables in code. I want to document some relationships, so I will use the following logical structure: Person with a Title and a number of Interests. Let’s look at the simplest table – PersonTitle.
using System.Data.Linq.Mapping;
namespace MangoDataApp
{
[Table]
public class PersonTitle
{
[Column(DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true,
IsPrimaryKey = true, AutoSync = AutoSync.OnInsert)]
public int PersonTitleID { get; set; }
[Column(DbType = "NVarChar(30) NOT NULL")]
public string Title { get; set; }
}
}
Let’s look at the code in details. You will notice that I use a couple of attributes to designate this class as Table and define columns. I find Table attribute pretty easy. Column attribute is a lot more annoying because I have to remember the column definition and type it as a magic string. I do not like this, but what can one do? I designate my primary key as Identity column and force to sync up the newly generated ID after the insert is committed. So far so good. Now I am going to take a closer look at defining an one-to-many relationship. I will use Person with a Title for this. Here is what my Person class looks like:
[Table]
public class Person
{
[Column(DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true,
IsPrimaryKey = true, AutoSync = AutoSync.OnInsert)]
public int PersonID { get; set; }
[Column(DbType = "NVarChar(30) NOT NULL")]
public string FirstName { get; set; }
[Column(DbType = "NVarChar(50) NOT NULL")]
public string LastName { get; set; }
[Column()]
public int PersonTitleID { get; set; }
private EntityRef<PersonTitle> personTitle;
[Association(ThisKey = "PersonTitleID", OtherKey = "PersonTitleID",
Storage = "personTitle")]
public PersonTitle PersonTitle
{
get { return personTitle.Entity; }
set { personTitle.Entity = value; }
}
As you can see above, unlike simple API of Entity Framework Code First I have to jump through a few hoops. One thing is that I have to create an explicit backing field of type EntityRef. I am hinting L2S to use this backing field by using Storage parameter of the column attribute. I also specify keys in both Person and PersonTitle tables. All-in-all not so bad. Now let’s look at many-to-many relationship between Person and Person interests. In this case I have to actually create a class corresponding to my junction table (unlike Entity Framework where I do not need to do this). Here is my junction table:
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace MangoDataApp
{
[Table]
public class PersonInterest
{
[Column(IsPrimaryKey = true)]
public int PersonID { get; set; }
[Column(IsPrimaryKey = true)]
public int InterestID { get; set; }
private EntityRef<Interest> interest;
[Association(ThisKey = "InterestID", OtherKey = "InterestID", Storage = "interest")]
public Interest Interest { get { return interest.Entity; } set { interest.Entity = value; } }
private EntityRef<Person> person;
[Association(ThisKey = "PersonID", OtherKey = "PersonID", Storage = "person")]
public Person Person { get { return person.Entity; } set { person.Entity = value; } }
}
}
Again, I am using EntityRef class just like in example above. Now, how does my Person table take use of that?
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace MangoDataApp
{
[Table]
public class Person
{
[Column(DbType = "INT NOT NULL IDENTITY", IsDbGenerated = true,
IsPrimaryKey = true, AutoSync = AutoSync.OnInsert)]
public int PersonID { get; set; }
[Column(DbType = "NVarChar(30) NOT NULL")]
public string FirstName { get; set; }
[Column(DbType = "NVarChar(50) NOT NULL")]
public string LastName { get; set; }
[Association(ThisKey = "PersonID", OtherKey = "PersonID")]
public EntitySet<PersonInterest> PersonInterests { get; set; }
[Column()]
public int PersonTitleID { get; set; }
private EntityRef<PersonTitle> personTitle;
[Association(ThisKey = "PersonTitleID", OtherKey = "PersonTitleID",
Storage = "personTitle")]
public PersonTitle PersonTitle
{
get { return personTitle.Entity; }
set { personTitle.Entity = value; }
}
public Person()
{
PersonInterests = new EntitySet<PersonInterest>();
}
}
}
Pretty easy again – I define Person Interest property as EntitySet this time, not EntityRef.
Let’s take a look at our context. This class is very simple. Please notice that I use fields, not properties for tables. If you use properties you will get an ugly non-descriptive exception.
using System.Data.Linq;
namespace MangoDataApp
{
public class PersonContext : DataContext
{
public PersonContext()
: base("isostore:/PersonDB.sdf")
{
}
public Table<Person> People;
public Table<Interest> Interests;
public Table<PersonInterest> PersonInterests;
public Table<PersonTitle> PersonTitles;
}
}
And that is all there is to it. Now, let me see how I can pump some data into my database. Frist of, I am going to expose my database through property of my application class:
public partial class App : Application
{
private static App app;
public static App CurrentApp
{
get { return app; }
}
public PersonContext DB { get; private set; }
/// <summary>
/// Constructor for the Application object.
/// </summary>
public App()
{
app = this;
DB = new PersonContext();
I have a static property to make the code that uses DB easier:
if (!App.CurrentApp.DB.DatabaseExists())
{
App.CurrentApp.DB.CreateDatabase();
Cool. Now, let’s pump some data into my four tables:
var int1 = new Interest() { InterestName = "Phone" };
App.CurrentApp.DB.Interests.InsertOnSubmit(int1);
var int2 = new Interest() { InterestName = "Desktop" };
App.CurrentApp.DB.Interests.InsertOnSubmit(int2);
var title = new PersonTitle() { Title = "Mr." };
App.CurrentApp.DB.PersonTitles
.InsertAllOnSubmit(new[] { title, new PersonTitle() { Title = "Mrs." } });
App.CurrentApp.DB.SubmitChanges();
var person = new Person() { FirstName = "Sergey", LastName = "Barskiy" };
person.PersonTitleID = title.PersonTitleID;
App.CurrentApp.DB.People.InsertOnSubmit(person);
App.CurrentApp.DB.SubmitChanges();
PersonInterest pi1 = new PersonInterest()
{ InterestID = int1.InterestID, PersonID = person.PersonID };
App.CurrentApp.DB.PersonInterests.InsertOnSubmit(pi1);
PersonInterest pi2 = new PersonInterest()
{ InterestID = int2.InterestID, PersonID = person.PersonID };
App.CurrentApp.DB.PersonInterests.InsertOnSubmit(pi2);
person.PersonInterests.Add(pi1);
person.PersonInterests.Add(pi2);
App.CurrentApp.DB.SubmitChanges();
The last thing I would like to talk about is lazy / eager loading. By default L2S will use lazy loading, meaning that your related properties are not retrieved until they are accessed. Most of that time this is good, but what if I always want to load the title when I get the data? I just use data load options object to achieve that.
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Person>(c => c.PersonTitle);
App.CurrentApp.DB.LoadOptions = options;
var people = App.CurrentApp.DB.People.ToList();
You can download sample project here.
Here are some useful links on MSDN:
Column attribute
Linq to SQL Overview
SQL CE Overview for Windows Phone 7.1
SQL CE Connection Strings for the Phone
), I updated my CodePlex project for calendar control for Windows Phone 7 – 
. 
