Linq - Getting Started

Get yourself a copy of Visual Studio 2008. It ships with a LINQ to SQL Designer. The designer will be your best friend throughout the LINQ to SQLing process.

I shall design a simple schema starting with something very common to websites today - a SQL table storing member information. It has 3 fields.

1. ID - auto incrementing integer
2. Username - char(10)
3. Password - char(10)
   

Lets dive into the designer.

How to bring up the designer?
Right-click on App_Code folder under your website and click on Add New Item. Select Linq to SQL Classes. I renamed the file as Member.dbml


Visual Studio creates Member.dbml and brings up the designer interface.

The designer is divided into two panes. The left pane is where one should drag n drop the sql tables. The right pane is where you drop stored procedures which get converted into methods on the classes. We shall explore the left page first.

To drag n drop sql tables, open the server explorer n connect to the database. I dragged n dropped the Members table. You will notice that Visual Studio will convert the table names from plural to singular. Here, Members is converted to Member.


At this moment, if you open up Member.designer.cs file, these are the contents.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;



[System.Data.Linq.Mapping.DatabaseAttribute(Name="LinqDemo")]
public partial class MemberDataContext : System.Data.Linq.DataContext
{
 
 private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
 
  #region Extensibility Method Definitions
  partial void OnCreated();
  partial void InsertMember(Member instance);
  partial void UpdateMember(Member instance);
  partial void DeleteMember(Member instance);
  #endregion
 
 public MemberDataContext() : 
   base(global::System.Configuration.ConfigurationManager.ConnectionStrings["LinqDemoConnectionString"].ConnectionString, mappingSource)
 {
  OnCreated();
 }
 
 public MemberDataContext(string connection) : 
   base(connection, mappingSource)
 {
  OnCreated();
 }
 
 public MemberDataContext(System.Data.IDbConnection connection) : 
   base(connection, mappingSource)
 {
  OnCreated();
 }
 
 public MemberDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
   base(connection, mappingSource)
 {
  OnCreated();
 }
 
 public MemberDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
   base(connection, mappingSource)
 {
  OnCreated();
 }
 
 public System.Data.Linq.Table Members
 {
  get
  {
   return this.GetTable();
  }
 }
}

Basically, Visual Studio creates a DataContext class (in this case MemberDataContext) which will be your conduit to the data stored in the sql tables. The MemberDataContext class has a few constructors. You will mostly need the default constructor.

Once you save the Member.dbml file, the following code is added to Member.designer.cs

[Table(Name="dbo.Members")]
public partial class Member : INotifyPropertyChanging, INotifyPropertyChanged
{
 
   private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
 
   private int _ID;
 
   private string _Username;
 
   private string _Password;
 
   #region Extensibility Method Definitions
   partial void OnLoaded();
   partial void OnValidate(System.Data.Linq.ChangeAction action);
   partial void OnCreated();
   partial void OnIDChanging(int value);
   partial void OnIDChanged();
   partial void OnUsernameChanging(string value);
   partial void OnUsernameChanged();
   partial void OnPasswordChanging(string value);
   partial void OnPasswordChanged();
   #endregion
 
   public Member()
   {
       OnCreated();
   }
 
   [Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
   public int ID
   {
       get
       {
           return this._ID;
       }
       set
       {
           if ((this._ID != value))
           {
               this.OnIDChanging(value);
               this.SendPropertyChanging();
               this._ID = value;
               this.SendPropertyChanged("ID");
               this.OnIDChanged();
           }
       }
   }
 
   [Column(Storage="_Username", DbType="NChar(10) NOT NULL", CanBeNull=false)]
   public string Username
   {
       get
       {
           return this._Username;
       }
       set
       {
           if ((this._Username != value))
           {
               this.OnUsernameChanging(value);
               this.SendPropertyChanging();
               this._Username = value;
               this.SendPropertyChanged("Username");
               this.OnUsernameChanged();
           }
       }
   }
 
   [Column(Storage="_Password", DbType="NChar(10) NOT NULL", CanBeNull=false)]
   public string Password
   {
       get
       {
           return this._Password;
       }
       set
       {
           if ((this._Password != value))
           {
               this.OnPasswordChanging(value);
               this.SendPropertyChanging();
               this._Password = value;
               this.SendPropertyChanged("Password");
               this.OnPasswordChanged();
           }
       }
   }
 
   public event PropertyChangingEventHandler PropertyChanging;
 
   public event PropertyChangedEventHandler PropertyChanged;
 
   protected virtual void SendPropertyChanging()
   {
       if ((this.PropertyChanging != null))
       {
           this.PropertyChanging(this, emptyChangingEventArgs);
       }
   }
 
   protected virtual void SendPropertyChanged(String propertyName)
   {
       if ((this.PropertyChanged != null))
       {
           this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
       }
   }
}

This is the fun part. Visual Studio created a partial class called Member (the name corresponds to name shown in the designer). I hope you know what a partial class is. If not, in simple words, partial classes allow you to define a class across multiple files.

Notice [Table(Name="dbo.Members")] definition above the class definition. It means that this class has been mapped to the Members table.

Now, the Member class has 3 private fields of interest.

1. private int _ID;
2. private string _Username;
3. private string _Password;

These correspond to the fields in the Members table. For each private field from the table, you will find the definition of a public property. Lets investigate the ID property.

[Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
  public int ID
  {
      get
      {
          return this._ID;
      }
      set
      {
          if ((this._ID != value))
          {
              this.OnIDChanging(value);
              this.SendPropertyChanging();
              this._ID = value;
              this.SendPropertyChanged("ID");
              this.OnIDChanged();
          }
      }
  }

The get property is self explanatory. The set property is more interesting.
Lets say you have a page to change the password. A member whose ID is 100 changes his password and you are relying on Linq to generate the sql statements for the update. Linq keeps track of what fields have changed and generates appropriate sql statements.

So, just by simple drag n drop actions, you have created a Object Mapper for your Member Table.

Next up, how do you actually start using it.