Mobile Corner

Understanding LINQ to SQL on Windows Phone 7.5

The "Mango" update to Windows Phone 7 includes LINQ to SQL abilities. Use these via building a sample movie review app.

• By Nick Randolph
• 10/13/2011

One of the most frequently asked questions from new Windows Phone developers is how to store data. Prior to Windows Phone 7.5 (also known as "Mango"), the answer was that any data had to be manually serialized to a file located within Isolated Storage, an area of the file system to which the application could write.

Windows Phone 7.5 includes support for LINQ to SQL, allowing developers to work with relational data. In this article you'll learn how to define, create, upgrade and work with LINQ to SQL within your application.

Setting up the Class
Let's start by creating a basic database that can track movies you've watched. Initially, the database will only contain a single table, Movie, which will track the Name, the Year the movie was made and the Synopsis. We'll start by creating a corresponding class, also named Movie, which will be used to represent each movie in memory (see Listing 1). This class will be annotated with both class and property-level attributes to specify mappings to the corresponding table and columns. You may have to add a reference to System.Data.Linq.dll for these attributes to be resolved.

You can think of the MoviesDataContext class as being your connection to the LINQ to SQL database within your application. An instance of this class is required in order to create, read from or write to a LINQ to SQL database. The following code checks to see if the database exists; if it doesn't, the database is created, then a Movie is created and inserted into the database:

using (var dc = new MoviesDataContext())
{
  if (!dc.DatabaseExists())
  {
    dc.CreateDatabase();
  }

  var movie = new Movie() {Name = "Hall Pass", 
                           Year = 2011, 
                           Synopsis = "Very funny movie"};
  dc.Movies.InsertOnSubmit(movie);
  dc.SubmitChanges();
}

The INotifyPropertyChanging Method
There are a couple of points worth noting about the classes used to interact with LINQ to SQL. First, the Movies class implements INotifyPropertyChanging (not to be confused with INotifyPropertyChanged, which is typically implemented by classes used in data binding so that property changes can propagate through to the UI). Implementing this method allows for LINQ to SQL to significantly optimize how it tracks changed objects. If your table class doesn't implement INotifyPropertyChanging, LINQ to SQL has to create duplicate objects for each item read from the database; it compares the duplicates to determine if there have been changes. If you do implement INotifyPropertyChanging,

LINQ to SQL will only create a duplicate at the point when the original is being modified. If your application works with a large number of objects, or objects that are large in size, this can significantly reduce the memory footprint of your application.

The MovieId property doesn't raise the NotifyPropertyChanging event, as it shouldn't change after the initial insert into the database. In the Column attribute you'll notice that this property has been set as the primary key and that it's an Identity field, meaning that the database will auto-assign the next integer value to this field when the Movie's inserted into the database. The AutoSync property value of OnInsert means that the new MovieId value will be immediately read from the database and the Movie object updated with the new value upon record insertion.

The last point to note is the structure of the connection string specified by the constant DBConnectionString, which currently only specifies the Data Source attribute. The Data Source attribute is the path to the database; in this case it's to a file called Movies.sdf, located within Isolated Storage (specified by the isostore: prefix).

Using an Existing Database
It's possible to ship an existing database with your application by adding it to your project and setting the Build Action to Content. This will deploy the database file with your application onto the phone, but it will be placed in the same folder as all other static content for your application. Your application can only read from this folder, which means you either have to open the database in read-only mode, or copy the database across into Isolated Storage before accessing it. The following is an example connection string for a database that's been packaged with the application:

const string ReadOnlyConnection = 
  "Data Source = 'appdata:/readonly.sdf'; File Mode = read only;";

It's also possible to encrypt a LINQ to SQL database. This has to be done when the database is first created, and simply requires a password to be specified in the connection string, like so:

const string Encrypted = "Data Source = 
   'isostore:/securedb.sdf'; Password='mypassword';";

To read data out of the database, you can either iterate through the Movies collection on the MoviesDataContext, or use LINQ to return a sub-set of the Movies:

var movies = from m in dc.Movies
                       where m.Year > 2000
                       orderby m.Name
                       select m;

Over time, your data needs might change; for example, you might want to add a LengthInMinutes property to the Movie class. Start by adding the appropriate property to the class itself, again with the Column attribute:

private int? lengthInMinutes;
[Column]
public int? LengthInMinutes
{
  get { return lengthInMinutes; }
  set
  {
    if (LengthInMinutes == value) return;
    lengthInMinutes = value;
    NotifyPropertyChanging("LengthInMinutes");
  }
}

At startup, you now have to not only detect whether the database exists, but also what version of the database it is. If the version is less than the version expected by the application, you can make appropriate adjustments by adding tables, columns, indexes and associations, as shown in the following code (it adds the LengthInMinutes column to the Movie table):

if (!dc.DatabaseExists()) {
    dc.CreateDatabase();
}
else {
  DatabaseSchemaUpdater dbUpdater = dc.CreateDatabaseSchemaUpdater();
  if (dbUpdater.DatabaseSchemaVersion < 2) {
    dbUpdater.AddColumn<Movie>("LengthInMinutes");
    dbUpdater.DatabaseSchemaVersion = 2;
    dbUpdater.Execute();
  }
}

Adding Tables
So far, the database has only contained a single table. Let's extend this example to include a second, related table called Review. The Review table includes ReviewId, Comment and MovieId, which links back to the movie being reviewed. In Listing 2, you'll notice that in addition to the MovieId property, there's also a Movie property annotated with the Association attribute; this establishes the database relationship between the Review and Movie tables.

Establishing Relationships
In addition to adding the Review class, you'll also want to add a Reviews property to the Movie class. This will allow you to navigate from a movie to its corresponding reviews. Again, the Reviews property, shown in Listing 3, is annotated with the Association attribute. You'll also notice that as part of the Movie constructor, the reviews field is instantiated as a new EntitySet, passing in references to the attachReview and detachReview methods. These methods ensure that the relationship between the Movie and Review is correctly established, or removed, as a Review is added to -- or removed from -- the Movie.

The only remaining thing to do is to add the Review table to the MovieDataContext:

public class MoviesDataContext : DataContext {
  ...
  public Table<Review> Reviews;
}
If you're adding this table and relationship to an existing database, you'll need to call the AddTable method on the database updater. Because the association to link the table is defined on the Review table you're adding, you don't need to also call AddAssociation:
if (dbUpdater.DatabaseSchemaVersion < 3) {
    dbUpdater.AddTable<Review>();
    dbUpdater.DatabaseSchemaVersion = 3;
    dbUpdater.Execute();
}

Now here's a simple example that illustrates creating a movie and a corresponding review:

var movie = new Movie() {Name = "Hall Pass", 
                         Year = 2011, 
                         Synopsis = "Very funny movie"};
dc.Movies.InsertOnSubmit(movie);

var review = new Review() { Comment = "This is one of the funniest movies ever" };
movie.Reviews.Add(review);
dc.Reviews.InsertOnSubmit(review);
dc.SubmitChanges();

Alternative Techniques and Limitations
In this article you've seen how you can create a LINQ to SQL database by starting with a set of annotated classes. An alternative would be to use either Visual Studio or SQL Server Management Studio to create a SQL Server Compact database and use the command-line tool, SqlMetal, to generate the corresponding classes for use within your Windows Phone application.

You also saw how to progressively update your LINQ to SQL database to accommodate new data columns and tables. However, the updater is limited in that it can't be used to modify or remove existing columns or tables. If you need to modify a column, you'll need to add a replacement column and migrate the data from the existing column to the new one. Although you're unable to remove columns or tables, you should remove any data no longer required to reduce the size of the LINQ to SQL database.