Introduction
One of the more popular variants of LINQ is LINQ to SQL, which takes the concepts of LINQ and extends them to working with collections of data from a SQL Server database. LINQ to SQL adds additional concepts, objects, and methods for not only retrieving sets of data from a database, but also inserting, updating, and deleting data. I'll show you the basics here. This article describes a series of steps for retrieving data from a SQL Server database to be displayed in a console window, and therefore assumes a basic knowledge of LINQ syntax. The intention is to show the basic workflow of how to create a Data Context using the Object Relational Designer, then how to retrieve data and display it on screen.
To develop this application, I used Visual Studio 2008 with the SQL Management Studio 2005, using the 2005 version of the Adventure Works LT database. This database can be installed by downloading it from CodePlex.com. By default, it will install in the \Program Files\Microsoft SQL Server\MSQL.1\MSQL\90\Data directory. If you open the SQL Management Studio, you can right-click the database folder and select “Attach”, where you will locate that directory and add it to the database. Afterwards, fire up Microsoft Visual Studio 2008 (or 2005) and use the Server Explorer to add a new connection to this database. At that point, start a Visual C# console application project and name it LinqToSql. Once created, go to “add new item” and choose “add LINQ to SQL classes. Name the .dbml file AdventureWorks.dbml. The creation of this file opens up a designer surface called the Object Relational Designer (ORD). We are using this Object Relational Designer to create a data context. So we drag and drop the Customer (Sales LT) table onto that designer to this result:
Take note of those properties of the Customer table
Some Explanation
The purpose of the Object Relational Designer is to allow you to model classes that map to and from a database. We dragged and dropped the Customer table and that created a Customer entity class. LINQ to SQL allows you to use any class to represent data, as long as you decorate it with the appropriate attributes. Here is a simple example:
[Table]
public class Customer
{
[Column(IsPrimaryKey=true)]
public int ID;
[Column]
public string Name;
}
The [Table] attribute, in the System.Data.Linq.Mapping namespace, tells LINQ to SQL that an object of this type represents a row in a database table. By default, it assumes the table name matches the class name; if this is not the case, you can specify the table name as follows:
[Table(Name="SalesLT.Customer")]
A class decorated with the [Table] attribute is called an entity in LINQ to SQL. To be useful, its structure must come closely – or exactly – match that of a database table, making it a low-level construct. The [Column] attribute flags a field or property that maps to a column in a table. Instead of defining public fields, you can define public properties in conjunction with private fields. This allows you to write validation logic into the property accessors. Here is the partial class file (that was created by dragging and dropping the Customer table onto the designer surface). It will illustrate some of these operations:
#pragma warning disable 1591
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;
using System.Linq.Expressions;
using System.ComponentModel;
using System;
[System.Data.Linq.Mapping.DatabaseAttribute(Name="AdventureWorksLT")]
public partial class AdventureWorksDataContext : System.Data.Linq.DataContext
{
private static System.Data.Linq.Mapping.MappingSource
mappingSource = new AttributeMappingSource();
#region Extensibility Method Definitions
partial void OnCreated();
partial void InsertCustomer(Customer instance);
partial void UpdateCustomer(Customer instance);
partial void DeleteCustomer(Customer instance);
#endregion
public AdventureWorksDataContext() :
base(global::LinqToSql.Properties.Settings.
Default.AdventureWorksLTConnectionString, mappingSource)
{
OnCreated();
}
public AdventureWorksDataContext(string connection) :
base(connection, mappingSource)
{
OnCreated();
}
public AdventureWorksDataContext(System.Data.IDbConnection connection) :
base(connection, mappingSource)
{
OnCreated();
}
public AdventureWorksDataContext(string connection,
System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}
public AdventureWorksDataContext(System.Data.IDbConnection connection,
System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}
public System.Data.Linq.Table<Customer><customer> Customers
{
get
{
return this.GetTable<customer>();
}
}
}
[Table(Name="SalesLT.Customer")]
public partial class Customer : INotifyPropertyChanging, INotifyPropertyChanged
{
private static PropertyChangingEventArgs emptyChangingEventArgs =
new PropertyChangingEventArgs(String.Empty);
private int _CustomerID;
private bool _NameStyle;
private string _Title;
private string _FirstName;
private string _MiddleName;
private string _LastName;
private string _Suffix;
private string _CompanyName;
private string _SalesPerson;
private string _EmailAddress;
private string _Phone;
private string _PasswordHash;
private string _PasswordSalt;
private System.Guid _rowguid;
private System.DateTime _ModifiedDate;
#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate(System.Data.Linq.ChangeAction action);
partial void OnCreated();
partial void OnCustomerIDChanging(int value);
partial void OnCustomerIDChanged();
partial void OnNameStyleChanging(bool value);
partial void OnNameStyleChanged();
partial void OnTitleChanging(string value);
partial void OnTitleChanged();
partial void OnFirstNameChanging(string value);
partial void OnFirstNameChanged();
partial void OnMiddleNameChanging(string value);
partial void OnMiddleNameChanged();
partial void OnLastNameChanging(string value);
partial void OnLastNameChanged();
partial void OnSuffixChanging(string value);
partial void OnSuffixChanged();
partial void OnCompanyNameChanging(string value);
partial void OnCompanyNameChanged();
partial void OnSalesPersonChanging(string value);
partial void OnSalesPersonChanged();
partial void OnEmailAddressChanging(string value);
partial void OnEmailAddressChanged();
partial void OnPhoneChanging(string value);
partial void OnPhoneChanged();
partial void OnPasswordHashChanging(string value);
partial void OnPasswordHashChanged();
partial void OnPasswordSaltChanging(string value);
partial void OnPasswordSaltChanged();
partial void OnrowguidChanging(System.Guid value);
partial void OnrowguidChanged();
partial void OnModifiedDateChanging(System.DateTime value);
partial void OnModifiedDateChanged();
#endregion
public Customer()
{
OnCreated();
}
[Column(Storage="_CustomerID", AutoSync=AutoSync.OnInsert,
DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int CustomerID
{
get
{
return this._CustomerID;
}
set
{
if ((this._CustomerID != value))
{
this.OnCustomerIDChanging(value);
this.SendPropertyChanging();
this._CustomerID = value;
this.SendPropertyChanged("CustomerID");
this.OnCustomerIDChanged();
}
}
}
[Column(Storage="_NameStyle", DbType="Bit NOT NULL")]
public bool NameStyle
{
get
{
return this._NameStyle;
}
set
{
if ((this._NameStyle != value))
{
this.OnNameStyleChanging(value);
this.SendPropertyChanging();
this._NameStyle = value;
this.SendPropertyChanged("NameStyle");
this.OnNameStyleChanged();
}
}
}
[Column(Storage="_Title", DbType="NVarChar(8)")]
public string Title
{
get
{
return this._Title;
}
set
{
if ((this._Title != value))
{
this.OnTitleChanging(value);
this.SendPropertyChanging();
this._Title = value;
this.SendPropertyChanged("Title");
this.OnTitleChanged();
}
}
}
[Column(Storage="_FirstName",
DbType="NVarChar(50) NOT NULL", CanBeNull=false)]
public string FirstName
{
get
{
return this._FirstName;
}
set
{
if ((this._FirstName != value))
{
this.OnFirstNameChanging(value);
this.SendPropertyChanging();
this._FirstName = value;
this.SendPropertyChanged("FirstName");
this.OnFirstNameChanged();
}
}
}
[Column(Storage="_MiddleName", DbType="NVarChar(50)")]
public string MiddleName
{
get
{
return this._MiddleName;
}
set
{
if ((this._MiddleName != value))
{
this.OnMiddleNameChanging(value);
this.SendPropertyChanging();
this._MiddleName = value;
this.SendPropertyChanged("MiddleName");
this.OnMiddleNameChanged();
}
}
}
[Column(Storage="_LastName",
DbType="NVarChar(50) NOT NULL", CanBeNull=false)]
public string LastName
{
get
{
return this._LastName;
}
set
{
if ((this._LastName != value))
{
this.OnLastNameChanging(value);
this.SendPropertyChanging();
this._LastName = value;
this.SendPropertyChanged("LastName");
this.OnLastNameChanged();
}
}
}
[Column(Storage="_Suffix", DbType="NVarChar(10)")]
public string Suffix
{
get
{
return this._Suffix;
}
set
{
if ((this._Suffix != value))
{
this.OnSuffixChanging(value);
this.SendPropertyChanging();
this._Suffix = value;
this.SendPropertyChanged("Suffix");
this.OnSuffixChanged();
}
}
}
[Column(Storage="_CompanyName", DbType="NVarChar(128)")]
public string CompanyName
{
get
{
return this._CompanyName;
}
set
{
if ((this._CompanyName != value))
{
this.OnCompanyNameChanging(value);
this.SendPropertyChanging();
this._CompanyName = value;
this.SendPropertyChanged("CompanyName");
this.OnCompanyNameChanged();
}
}
}
[Column(Storage="_SalesPerson", DbType="NVarChar(256)")]
public string SalesPerson
{
get
{
return this._SalesPerson;
}
set
{
if ((this._SalesPerson != value))
{
this.OnSalesPersonChanging(value);
this.SendPropertyChanging();
this._SalesPerson = value;
this.SendPropertyChanged("SalesPerson");
this.OnSalesPersonChanged();
}
}
}
[Column(Storage="_EmailAddress", DbType="NVarChar(50)")]
public string EmailAddress
{
get
{
return this._EmailAddress;
}
set
{
if ((this._EmailAddress != value))
{
this.OnEmailAddressChanging(value);
this.SendPropertyChanging();
this._EmailAddress = value;
this.SendPropertyChanged("EmailAddress");
this.OnEmailAddressChanged();
}
}
}
[Column(Storage="_Phone", DbType="NVarChar(25)")]
public string Phone
{
get
{
return this._Phone;
}
set
{
if ((this._Phone != value))
{
this.OnPhoneChanging(value);
this.SendPropertyChanging();
this._Phone = value;
this.SendPropertyChanged("Phone");
this.OnPhoneChanged();
}
}
}
[Column(Storage="_PasswordHash",
DbType="VarChar(128) NOT NULL", CanBeNull=false)]
public string PasswordHash
{
get
{
return this._PasswordHash;
}
set
{
if ((this._PasswordHash != value))
{
this.OnPasswordHashChanging(value);
this.SendPropertyChanging();
this._PasswordHash = value;
this.SendPropertyChanged("PasswordHash");
this.OnPasswordHashChanged();
}
}
}
[Column(Storage="_PasswordSalt",
DbType="VarChar(10) NOT NULL", CanBeNull=false)]
public string PasswordSalt
{
get
{
return this._PasswordSalt;
}
set
{
if ((this._PasswordSalt != value))
{
this.OnPasswordSaltChanging(value);
this.SendPropertyChanging();
this._PasswordSalt = value;
this.SendPropertyChanged("PasswordSalt");
this.OnPasswordSaltChanged();
}
}
}
[Column(Storage="_rowguid",
DbType="UniqueIdentifier NOT NULL")]
public System.Guid rowguid
{
get
{
return this._rowguid;
}
set
{
if ((this._rowguid != value))
{
this.OnrowguidChanging(value);
this.SendPropertyChanging();
this._rowguid = value;
this.SendPropertyChanged("rowguid");
this.OnrowguidChanged();
}
}
}
[Column(Storage="_ModifiedDate", DbType="DateTime NOT NULL")]
public System.DateTime ModifiedDate
{
get
{
return this._ModifiedDate;
}
set
{
if ((this._ModifiedDate != value))
{
this.OnModifiedDateChanging(value);
this.SendPropertyChanging();
this._ModifiedDate = value;
this.SendPropertyChanged("ModifiedDate");
this.OnModifiedDateChanged();
}
}
}
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));
}
}
}
#pragma warning restore 1591
When we dragged and dropped the table onto the designer surface, we created an object model. The output is called a data context, and provides access to that data model. When we dragged and dropped the Customer table onto the ORD, a data context was created, and within that data context, a child class of type Customer was created:
public System.Data.Linq.Table<Customer><customer /> Customers
{
get
{
return this.GetTable<customer>();
}
}
That is, the dragging and dropping created a Customers (plural) class inside of the data context (AdventureWorksDataContext). Again, classes like the Customer class are called entity classes. Instances of entity classes are called entities. Entity classes map to tables within a database. Scroll down the *.dbml.cs file further and notice all of the public and private properties. The Customers class is an entity class for the Customer table; it is creating your data tier. It is making a one to one translation between a database table's data and an entity class created within your application. There is a one to one mapping between database columns and class properties. The data context is a factory object, that when asked to enumerate through a list, will check to see if you already have an instance in memory. If it does, it will supply an instance of the entity class. If it doesn't, it will query the database, create an entity instance for each row that it retrieves, and then supply the one that you asked for. Once you ask for the next entity instance (the next customer, for example), the data context repeats that sequence of events.
Further, entity classes are simply data structures that usually map one to one to database table definitions and represent a single row of data. When we look at this query:
var myQuery = from c in myContext.Customers
select c;
The LINQ to SQL runtime knows how to convert that expression, or that created query, into T-SQL. Further, it knows how to translate the T-SQL into entity classes. Here is the file that contains the query:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
class Program
{
static void Main(string[] args)
{
AdventureWorksDataContext myContext = new AdventureWorksDataContext();
var myQuery = from c in myContext.Customers
select c;
foreach (var c in myQuery)
{
Console.WriteLine(c.LastName);
}
Console.ReadLine();
}
}
As shown in the code above, once you’ve defined entity classes, start querying by instantiating a DataContext object and then calling GetTable on it. Admittedly, this was a very basic look at C# 3.0’s LINQ to SQL technology. This article is meant to start the beginner, and is not a substitute for any professional texts written about this subject. Here is a view of the output after execution:
