Understanding the Core Mechanics of LINQ to SQL

LINQ to SQL basically means that you write LINQ queries and the LINQ technology converts that query to SQL queries. The core elements are the DataContext and the TableAttribute.

Define a class that inherits from DataContext. This class represents your database connection. And, for each table element you’d like to access define a class that is decorated with the TableAttribute, specify the underlying table element, and decorate properties that map to columns with the ColumnAttribute. Listing 1 shows an elided example demonstrated these two elements hand coded by me.

Listing 1: Defining a DataContext and mapped entity class.

  public class Northwind : DataContext
  {
    private static readonly string connectionString = 
      "Data Source=.\\SQLEXPRESS;AttachDbFilename=c:\\temp\\northwnd.mdf;" + 
      "Integrated Security=True;Connect Timeout=30;User Instance=True";
    public Northwind()
      : base(connectionString)
    {
      this.Log = Console.Out;
    }
    public Table<Invoice> Invoices
    {
      get { return this.GetTable<Invoice>(); }
    }
  }

  [Table(Name = "dbo.Invoices")]
  public partial class Invoice
  {
    private string shipName;
    
    private string shipAddress;
    private string shipCity;
//...
    public Invoice()
    {
    }
    [Column(Storage = "shipName", DbType = "NVarChar(40)")]
    public string ShipName
    {
      get
      {
        return this.shipName;
      }
      set
      {
        if ((this.shipName != value))
        {
          this.shipName = value;
        }
      }
    }
    [Column(Storage = "shipAddress", DbType = "NVarChar(60)")]
    public string ShipAddress
    {
      get
      {
        return this.shipAddress;
      }
      set
      {
        if ((this.shipAddress != value))
        {
          this.shipAddress = value;
        }
      }
    }
    [Column(Storage = "shipCity", DbType = "NVarChar(15)")]
    public string ShipCity
    {
      get
      {
        return this.shipCity;
      }
      set
      {
        if ((this.shipCity != value))
        {
          this.shipCity = value;
        }
      }
    }
//...
  }

The Northwind class simply inherits from DataContext and contains the connection string. When you create an instance of Northwind you essentially have a ready-to-go database connection.

The Invoice class maps fields and properties to columns. The ColumnAttribute uses named arguments to map each property to the relevant column in the database entity. For example, the ShipName property is mapped to the underlying field shipName by the Storage named argument and the DbType is an NVarChar(40). What this means is that when Invoice objects are created a ShipName field value is stored in the shipName field and accessed through the ShipName property.