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

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

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

Linq-To-SQL ORM

Implementation

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");
    }
}