(X) Hide this
    • Login
    • Join
      • Generate New Image
        By clicking 'Register' you accept the terms of use .

WP7 for iPhone and Android Developers : From Core Data to LINQ to SQL

(4 votes)
Kevin Hoffman
Kevin Hoffman
Joined Feb 01, 2011
Articles:   10
Comments:   1
More Articles
2 comments   /   posted on Aug 03, 2011
Tags:   windows-phone-7 , android , iphone , ios , mango , linq , linq-to-sql , kevin-hoffman
Categories:   Data Access , Windows Phone , General

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;
 6:     public Character()
 7:     {
 8:         this.world = default(EntityRef<World>);           
 9:     }
 11:     public void Detach()
 12:     {
 13:         this.world = default(EntityRef<World>);
 14:     }
 16:     [Column(IsPrimaryKey = true, IsDbGenerated = false, 
 17:         CanBeNull = false,
 18:         AutoSync = AutoSync.OnInsert)]
 19:     public Guid Id { get; set; }
 21:     [Column]
 22:     public Guid WorldId { get; set; }
 24:     [Column(IsVersion=true)]
 25:     public byte[] Version { get; set; }
 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:     }
 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; }
 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:      {
 7:      }
 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.


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.



  • TheNetAvenger

    Re: WP7 for iPhone and Android Developers : From Core Data to LINQ to SQL

    posted by TheNetAvenger on Aug 16, 2011 11:55

    >>Note that LINQ to SQL doesn’t natively support many-to-many relationships

    This is presented as a bad thing, yet is a fundamental aspect of 2nd Normal Form, which the user or database designer should be 'aware' of, and not relying on Core to implement, just because the Apple object model makes it 'easy' to create poorly designed databases.

    The Core Model is an abstraction layer to relational database models, via objects, using traditional data modeling it is not dimensional modeling, which is normally where gerund tables would be omitted unless they are only designing for a flat structure that will develop data nightmares later on.

    This is creating an environment of programmers that are moving away from fundamental information technologies, and implies that 'quick and dirty' is the right way to design databases.


  • KotanCode

    Re: WP7 for iPhone and Android Developers : From Core Data to LINQ to SQL

    posted by KotanCode on Oct 20, 2011 16:03

    I'm not presenting the lack of many-to-many relationships as a bad thing. I'm simply presenting it as a difference in architectural decisions. There are pros and cons to all architectures. Just because something is 2nd Normal Form doesn't automatically make it good, it must be good within the context of the problem and the problem constraints. In other words, there are plenty of times where many-to-many with mapping tables are necessary and appropriate and other times where it isn't. There are times when a DB has been "over normalized" and times when it has been "under normalized".

     I certainly don't advocate the ignorance of programmers to the underlying implementation (e.g. devs not knowing that mapping tables exist in Core Data to support their m2ms). What I advocate is not making developers do things that computers can do for them such as automatically generate a mapping table to support a many-to-many relationship.

Add Comment

Login to comment:
  *      *       

From this series