Linq-To-SQL - Windows Phone Development For People In a Hurry

Overview

Linq-To-SQL is an Object-Relational-Mapper (ORM) , i.e. a way of saving and retrieving C# objects to and from a database

Relational Databases

The local database used in Windows Phone is a 'relational database'
A relational database consists of a set of tables
Each table is tailored for a particular object type with each row representing one object and each column representing a property of the object
There is also an additional column containing a unique ID for the object (usually an integer)
N.b. For Windows Phone we also add another column for the version. This is to speed up the code when updating the database and is not essential for relational databases in general

One-To-One and One-to-Many mapping

Often objects feature another object as a property. The property column in this case just feature the ID for the attached object. This is known as a 'foreign key'
The referenced object may be referenced by several objects which is known as a One-To-Many mapping. If it is only reference by one object then it is a One-To-One mapping
An example is a Customer may have many Order objects, but each Order only belongs to one Customer , hence Many-To-One
In this example the foreign key is set in the Order table. Miss Smith has made one order, Mr Jones has made 3 orders

Customer table
Id	Name
1	Mr Jones
2	Miss Smith

Order table
Id	Item	Price	Customer_Id
1	Fun Bus - Memoirs of a Conductor	4.99	1
2	Cooking with Marmite	5.99	1
3	My Great Life In Provence	5.99	2
5	Pocket Guide to Pockets	1.99	1

Many-To-Many mapping

There are some cases where you may want a 'Many-to-Many' relationship. e.g. A Book may be bought by many Customer and a Customer may have bought many Book
This can be represented in relational databases using a separate 'join table' which consists of two columns, a foreign key for the Book Id and a foreign key for the Customer Id and there is a row for every relationship
This is not an easy/efficient way to do this in Linq-To-SQL but here is one workaround,
- Create a new model class which represents a single relationship with a One-To-Many reference to each of the joined classes
- This will be created and stored in the join table
- These can be queried reasonably effectively using Linq subqueries

Linq-To-SQL ORM

An ORM takes the objects and reads/writes them to the database
To avoid too many slow database read/write operations, the ORM also caches the changes until you ask for them to be committed
Linq-To-SQL for Windows Phone also provides access to the database via Linq so abstracting the programmer away from SQL statements
In fact, one of the limitations of Linq-To-SQL for Windows Phone is that SQL cannot be executed directly

Implementation

An object is created which represents the ORM. This inherits from an object called (confusingly) 'DataContext' which has nothing to do with the DataContext to do with Binding
Various C# attributes are assigned to the Model objects that will be stored in the database
Moreover, the object should also implement the INotifyPropertyChanging interface to keep the ORM current with changes on the object
Entity References need to be defined on objects where there is a foreign key relation

Template for a Linq-To-SQL DataContext

namespace VitalStats.Model
{
    public class AppDataContext : DataContext
    {
        public AppDataContext(string connectionString) : base(connectionString) 
        {
        }

        public Table<Profile> Profiles;

        public Table<Stat> Orders;

    }
}

Template for a Model class

[Table]
public class MyModelClass : INotifyPropertyChanged, INotifyPropertyChanging
{

    // ... Constructor, properties not saved to database and methods here ...


    // Example simple string property
    private string _name;
    [Column]
    public string Name
    {
        get {return _name;}
        set
        {
            if (_name != value)
            {
                NotifyPropertyChanging("Name");
                _name = value;
                NotifyPropertyChanged("Name");
            }
        }
    }
    
    // Always include an ID column defined as follows
    private int _id;
    [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", 
        CanBeNull = false, AutoSync = AutoSync.OnInsert)]
    public int Id
    {
        get {return _id;}
        set
        {
            if (_id != value)
            {
                NotifyPropertyChanging("Id");
                _id = value;
                NotifyPropertyChanged("Id");
            }
        }
    }

    // Always include a version column. Improves performance when updating the 
    // database schema
    [Column(IsVersion = true)]
    private Binary _version;

    // Need to implement this for the Linq-To-SQL ORM to work properly
    public event PropertyChangingEventHandler PropertyChanging;
    private void NotifyPropertyChanging(string propertyName)
    {
        if (PropertyChanging != null)
        {
            PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
        }
    }

    // Useful to implement INotifyPropertyChanged so can do effective Binding
    public event PropertyChangedEventHandler PropertyChanged;
    private void NotifyPropertyChanged(string propertyName)
    {
        if (PropertyChanged != null)
        {
            PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }

}

Template for a Many-To-One reference property

Additional code for a Profile that references many Order objects,

In the main body of the Profile class,

private EntitySet<Order> _orders;

[AssociationStorage(Storage = "_orders", OtherKey = "_profileId", ThisKey = "Id")]
public EntitySet<Order> Orders
{
  get { return this._orders; }
  set { this._orders.Assign(value); }
}

In the constructor of the Profile class,

public Profile() {

  // Supply the entityset with code which details how to deal with adding and
  // removing objects respectively
  this._orders = new EntitySet<Order>(
      delegate (Order entity)
      {
          this.NotifyPropertyChanging("Orders");
          entity.Profile = this;
      },
      delegate (Order entity)
      {
          this.NotifyPropertyChanging("Orders");
          entity.Profile = null;
      });

}

In the main body of the Order class,

private EntityRef<Profile> _profile;
[Association(Storage = "_profile", ThisKey = "_profileId", OtherKey = "Id", IsForeignKey = true)]
public Profile profile
{
    get {return _profile.Entity;}
    set
    {
        NotifyPropertyChanging("Profile");
        _profile.Entity = value;
        if (value != null)
        {
            _profileId = value.Id;
        }
        NotifyPropertyChanging("Profile");
    }
}

Linq-To-SQL for People in a Hurry