This article is part 9 of a 12-part article series on Windows Phone 7 for iPhone and Android Developers. While many of the previous articles in this series work perfectly fine on Windows Phone 7 and Windows Phone 7.1 (formerly codenamed “Mango”), this article focuses specifically on a feature unique to the 7.1 “Mango” release – LINQ to SQL and on-device SQL database files.
One of the features of the iOS SDK that is often lauded as one of the biggest benefits of the SDK is its support for the Core Data framework. As Apple developers (both Mac and iOS) know, this framework is an object-relational mapping framework that allows for robust queries, relationships between entities, and even storage of binary (BLOB) data as entity attributes. Further, Core Data also allows application developers to read to and write from files such as SQLite files (XML and binary formats are supported on the Mac but only SQLite is supported in iOS).
Xcode 4 lets you visually design your entities, their relationships, and even pre-defined queries (called fetch requests) right within the editor, as shown in this screenshot below:
The similarities between this entity model designer and model designers like those that come with LINQ to SQL and the Entity Framework within Microsoft Visual Studio 2010 are not accidental – all of these frameworks have a single goal in mind: providing a persistence store for an entity model. In the screenshot above, two entities (Scene and Character) are depicted. These entities have a many-to-many relationship to each other.
Note that LINQ to SQL doesn’t natively support many-to-many relationships without the use of an interim or middle-man linking table (e.g. a table called CharacterScenes to support the above example). Core Data models support many-to-many relationships natively and, when using a SQLite database, will actually create the linking table behind the scenes without bothering the developer with those details. Developers coming from Core Data to LINQ to SQL who plan on migrating data models with many-to-many relationships need to be aware of this limitation.
Developers who attempted to build local data-driven (as opposed to data in the cloud) applications for the first release of Windows Phone 7 were limited to using tools like the WCF binary contract serializer to read and write binary files from Isolated Storage. Such limitations are gone and with WP7.1 as developers now have access to virtually all of the robust, powerful features of LINQ to SQL with an underlying SQL database file.
Next, we’ll take a look at how to create data models for Windows Phone 7.1 using LINQ to SQL. Unfortunately, we don’t currently have a visual design surface for phone-based models like we do for enterprise LINQ to SQL models, but I’m hoping that’s just a limitation of the beta. For now, we have to build our models by writing POCO (Plain-Old C# Object) classes and decorating them with enough attributes to allow LINQ to SQL to infer the metadata for the underlying database.
To continue with the sample above of Character entities, let’s create a Character entity that belongs to another entity called World. First, let’s see the child class, Character:
1: [Table]
2: public class Character
3: {
4: private EntityRef<World> world;
5:
6: public Character()
7: {
8: this.world = default(EntityRef<World>);
9: }
10:
11: public void Detach()
12: {
13: this.world = default(EntityRef<World>);
14: }
15:
16: [Column(IsPrimaryKey = true, IsDbGenerated = false,
17: CanBeNull = false,
18: AutoSync = AutoSync.OnInsert)]
19: public Guid Id { get; set; }
20:
21: [Column]
22: public Guid WorldId { get; set; }
23:
24: [Column(IsVersion=true)]
25: public byte[] Version { get; set; }
26:
27: [Association(Name="World_Character", Storage = "world", ThisKey="WorldId",
28: OtherKey="Id", IsForeignKey=true)]
29: public World World
30: {
31: get
32: {
33: return this.world.Entity;
34: }
35: set
36: {
37: this.world.Entity = value;
38: }
39: }
40:
41: [Column]
42: public string Name { get; set; }
43: }
The Character class is decorated with the Table attribute. This means that when the SQL database is created, it will create a table named Character. Next we see that we’ve got an object of type EntityRef<World>. This is, as its name implies, a reference to another entity. In the constructor, we set this value to the default value for an entity ref of that type – this keeps us from throwing exceptions when we access properties on newly created Character objects.
The Detach() method gets called by our code when we are about to update character entities. The reason for this is fairly complicated, but basically we need this method if we are going to save changes to a Character object that belongs to a different data context than the World object to which it belongs. Thankfully you don’t need to worry about those kinds of scenarios just yet.
Now let’s see the parent class, World. In our model, we don’t actually need to grab a list of characters for a world object, since we can filter characters by their WorldId column and so we don’t need a reference to an EntitySet of Character objects. This is where LINQ to SQL starts to show how “close to the metal” it is. Core Data developers may be very put off by the fact that you have to pick and choose when you surface a relationship. Additionally, Core Data developers don’t need to explicitly define identity variables for their entities – the relationships are made available to developers automatically. If I had to make a comparison, I’d say that Core Data provides a model very close to what I would consider a business model whereas LINQ to SQL, despite its “Entity” nomenclature, is very much a data model.
1: [Table]
2: public class World
3: {
4: [Column(IsPrimaryKey = true, IsDbGenerated = false,
5: CanBeNull = false,
6: AutoSync = AutoSync.OnInsert)]
7: public Guid Id { get; set; }
8:
9: [Column]
10: public string Name { get; set; }
11: }
This is only half of the work that needs to be done. Once you have your LINQ to SQL objects defined and you have the mapping attributes configured the way you’d like, you need to create a wrapper class called a data context class. Here’s a data context class that exposes properties that can be used to query for Character or World objects:
1: public class MyDataContext : DataContext
2: {
3: public MyDataContext()
4: : base(ModelConstants.ConnectionString)
5: {
6:
7: }
8:
9: public Table<Character> Characters;
10: public Table<World> Worlds;
11: }
The MyDataContext class inherits from System.Data.Linq.DataContext (remember to add a reference to System.Data.Linq for your project). The constructor passes in a default connection string. In my case, this is a constant which evaluates to "Data Source=isostore:/WorldsAndChars.sdf". This will tell LINQ to SQL to back our data objects with a SQL file called WorldsAndChars.sdf stored in the Isolated Storage area for our application.
We’re almost there – the next thing we need to do is use our data context to create an empty database the first time the application starts:
1: using (var dc = new MyDataContext())
2: {
3: if (!dc.DatabaseExists())
4: {
5: dc.CreateDatabase();
6: }
7: }
Now that we’ve created our LINQ to SQL objects, we’ve created a wrapper context for those objects, and we’ve created the database programmatically, we’re finally able to start working with those objects. First, we can query the data context:
1: public static Character[] GetCharacters(Guid worldId)
2: {
3: using (var dc = new MyDataContext())
4: {
5: var q = from Character c in dc.Characters
6: orderby c.Name
7: where c.WorldId == worldId
8: select c;
9: return q.ToArray();
10: }
11: }
Or we can create new characters (also using the data context):
1: public static void SaveCharacter(Character c)
2: {
3: using (MyDataContext dc = new MyDataContext())
4: {
5: c.Detach();
6: if ((c.Id == null) || (c.Id == Guid.Empty))
7: {
8: c.Id = Guid.NewGuid();
9: dc.Characters.InsertOnSubmit(c);
10: }
11: else
12: {
13: dc.Characters.Attach(c, true);
14: }
15: dc.SubmitChanges();
16: }
17: }
The above method will either create a new character or save an existing one, depending on whether the character exists (its ID has been set). Note the call to c.Detach() – remember that bit earlier where I mentioned that LINQ to SQL doesn’t always like it when you try and save one entity with a data context that is different than the one that loaded it (or its related entities)? Detaching it like this prevents such conflicts. Finally, calling SubmitChanges() on the data context commits all changes made since the last time that data context saved its changes. As a rule, I try and make methods like this that will allow a transient data context to go out of scope as soon as their work is done – this avoids potential multi-threading and multi-context conflicts that might arise from leaving contexts floating around everywhere in my application.
For more information on LINQ to SQL, the best source of information is probably Microsoft’s own documentation for the System.Data.Linq namespace, which contains all of the data mapping attributes that you use to control data storage, metadata, relationships, keys, and column and table properties.
Summary
Core Data is a fantastic framework that allows iOS developers to rapidly build data-driven applications. It gives developers the ability to define data entities, their attributes, and their relationships and persist those entities in a SQLite database. Windows Phone 7.1 gives developers LINQ to SQL and the ability to persist their entities, relationships, and attributes in a SQL database in an Isolated Storage folder.
Quick, easy, robust access to simple object-relational mapping tools gives developers yet another tool in their toolbox for building amazing mobile applications for Windows Phone 7.