Entity classes map a native database table or view to a managed class. Intrinsically, this changes access from a data model to an object-oriented model. You can map database columns (fields) to data members and properties of a managed class. Mapping every column of the table is not required. You can map only needed columns to the class instead of the entire table. Columns not mapped are not accessible in LINQ to SQL. Entity classes also can define uniqueness and associations.

The Table attribute maps an entity class to a database table and cannot be applied to a structure. Name is the only property of the Table attribute and names the database table that the class is mapping. If the Name property is omitted, the class maps to the table that shares the name of the class. For example, by default, the class XData would map to a table in the database named XData.

A Column attribute maps a database column to a data member or property of the entity class. The Name property is optional and maps the member to a specific column in the database table. The default mapping is to the column with the same name as the member. The Column attribute has additional properties. Table 11-5 list all the properties of the Column attribute.

An entity class is defined and used in the following example. Employee is the entity class. It is mapped to the HumanResources.Employee table in the SQL database. The Id data member is mapped explicitly to EmployeeID of the target entity. The other members are mapped implicitly to the correct member in the corresponding table. In Main, the connection string is set. The dots represent the path to the AdventureWorks_Data database. This is where the database is installed. You should substitute the correct path. Next, an instance of DataContext is created. DataContext is a bridge to the original data source. DataContext.GetTable binds the entity class to the database. The result is placed in the Table<Employee> type. The subsequent query returns employees that share a specific manager, where the manager ID is 21. The foreach loop displays the results. A reference to System.Data.Linq.dll is required to access the System.Data.Linq and System.Data.Linq.Mapping namespaces:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace CSharpBook {
    class Program {
        static void Main(string[] args) {
            string conn = @"Data Source=DONISBOOK;AttachDbFilename=" +
                @"'...AdventureWorks_Data.mdf';Integrated Security=True";
            DataContext context = new DataContext(conn);
            Table<Employee> employees = context.GetTable<Employee>();
            var query = from e in employees
                        where e.ManagerID == 21
                        select new { e.Id, e.Title};
            foreach (var item in query) {
                Console.WriteLine("{0} {1}",
                   item.Id, item.Title);
            }
        }
    }

    [Table(Name = "HumanResources.Employee")]
    public class Employee{
        [Column(Name="EmployeeID", IsPrimaryKey=true)] public int Id;
        [Column] public string Title;
        [Column] public int ManagerID;
    }
}

LINQ to SQL query expressions are applied to relational databases. The query expression is object-based, which might require mapping database tables and views to entities. Use the DataContext type to connect to the data source. Next, you apply a query expression to the resulting entity type. Here are the steps for applying a query expression to a table:

The following sample code demonstrates these steps. This program displays the names of all salespeople. General salesperson information, sales information, and employee names are stored in separate tables. Entities are created for the Employee, SalesOrderHeader, and Contact tables. In Main, the connection string is defined. DataContext.GetTable then is called to create entity objects for the Employee and Contact tables. A query expression is performed on the table objects. A join is used to create a relationship between the two tables. The results then are enumerated and displayed:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace SimpleQuery {
    class Program {
        static void Main(string[] args) {
            string conn = @"Data Source=DONISBOOK;AttachDbFilename=" +
                @"'...AdventureWorks_Data.mdf';Integrated Security=True";
            DataContext context = new DataContext(conn);
            Table<Employee> employees = context.GetTable<Employee>();
            Table<Contact> contacts = context.GetTable<Contact>();
            var result = from employee in employees
                         join contact in contacts
                         on employee.ContactID equals contact.ContactID
                         where employee.Title == "Sales Representative"
                         select new { employee.ContactID, contact.FirstName, contact.
LastName };
            Console.WriteLine("Sales people are:");
            foreach (var item in result) {
                Console.WriteLine("{0} {1}", item.FirstName, item.LastName);
            }
        }
    }

    [Table(Name = "HumanResources.Employee")]
    public class Employee {
        [Column(IsPrimaryKey = true)] public int EmployeeID;
        [Column] public string Title;
        [Column] public int ContactID;
    }
    [Table(Name = "Person.Contact")]
    public class Contact {
        [Column(IsPrimaryKey = true)] public int ContactID;
        [Column] public string FirstName;
        [Column] public string LastName;
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Linq;

namespace CSharpBook {
    class Program {
        static void Main(string[] args) {
            string conn =
                @"Data Source=DONISBOOK;AttachDbFilename=" +
                @"'...AdventureWorks_Data.mdf';Integrated Security=True";
            string tablePerson= "select * from Person.Contact";
            string tableEmployee= "select * from HumanResources.Employee";

            SqlDataAdapter da1 = new SqlDataAdapter(tablePerson, conn);
            SqlDataAdapter da2 = new SqlDataAdapter(tableEmployee, conn);

            DataSet ds = new DataSet();
            da1.Fill(ds, "Contact");
            da2.Fill(ds, "Employee");
            DataTable employees=ds.Tables["Employee"];
            DataTable contacts=ds.Tables["Contact"];

            var results = from person in employees.AsEnumerable()
                          join contact in contacts.AsEnumerable()
                            on person.Field<int>("ContactID") equals contact.
Field<int>("ContactID")
                          where person.Field<string>("Title") == "Sales Representative"
                          select new { ID = person.Field<int>("ContactID"),
                                       First = contact.Field<string>("FirstName"),
                                       Last = contact.Field<string>("LastName")};

            Console.WriteLine("Sales people are:");
            foreach (var item in results) {
                Console.WriteLine("{0} {1}", item.First, item.Last);
            }
        }
    }
}

Associations are integral to SQL programming. The most common associations are one-to-many and one-to-one associations. For example, an inventory database might consist of purchase order, product, and vendor tables. There would be a one-to-many relationship from the purchase order table to the product table. For any purchase order, there could be many products. A one-to-one relationship exists between the purchase order and vendor tables. This would match the vendor ID in the purchase order with the vendor name found in the vendor table. Associated tables must share a common field. The vendor ID field would be the common field between the purchase order and vendor tables. The common field provides the link between the associated tables.

In LINQ to SQL, tables are represented by entity classes. In an entity class, a relationship is defined with the Association attribute. The ThisKey and OtherKey properties describe the association between entities. ThisKey defines the common field (typically the primary key) in the current entity. OtherKey defines the common field in the other entity. In standard SQL terminology, OtherKey is equivalent to a foreign key.

Associations in LINQ to SQL are similar to joins in other query languages. A join defines the relationship between two tables. An association defines the relationship between objects. The EntitySet type defines a one-to-many relationship, while the EntityRef type defines a one-to-one relationship. Both are exposed as properties within the entity class. The EntityRef and EntitySet types also provide access to the related class or collection from within the current entity. For this reason, both typically are exposed as properties. Here is an example of the Association attribute, EntityRef type, and EntitySet type:

[Table(Name = "HumanResources.Employee")]
public class Employee {
    [Column(IsPrimaryKey = true)] public int EmployeeID;
    [Column] public string Title;
    [Column] public int ContactID;

    private EntitySet<SalesOrderHeader> propSales = null;
    [Association(Storage = "propSales", ThisKey = "EmployeeID",
        OtherKey = "SalesPersonID")]
    public EntitySet<SalesOrderHeader> Sales {
        get { return this.propSales; }
        set { this.propSales.Assign(value); }
    }

    private EntityRef<Contact> propName;
    [Association(Storage = "propName", ThisKey = "ContactID", OtherKey = "ContactID")]
    public Contact Name {
        get { return this.propName.Entity; }
        set { this.propName.Entity = value; }
    }
}

Assuming that the employee is a salesperson, there is a one-to-many relationship between the Employee and Sales tables. For that reason, the relationship is defined with an Association attribute on an EntitySet type. The foreign key in the Sales table is defined by the OtherKey property, which is SalesPersonID. The local key is EmployeeID and is defined with the ThisKey property. In this example, the ThisKey property is self-documenting only. Without the property, the default is the primary key, which is EmployeeID. Properties in the class hide the details of the EntityRef and EntitySet types. You can access the related table (Sales) from this property.

There is a one-to-one relationship between the Employee and Contact tables. For that reason, the EntityRef type is used. The foreign key is ContactID of the Contact table, which is defined with the ThisKey property. The EntityRef is abstracted by a class property.

Here is the entire code. There are three entity classes: Employee, Contact, and SalesOrderHeader. The program generates a sales report, which is saved to a file. Notice that the query expression does not include an explicit join. The join is defined already via the Association attributes. The report is written to the file in the foreach loop. From each Employee entity, references to the other entity classes are available through the EntityDef and EntityRef properties. Sales is an EntityDef type, which represents the one-to-many relationship between the Employee and Sales tables. In this case, the "many" are sales records. Each sales record is retrieved in the nested foreach loop and is written to the sales report:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.IO;

namespace SalesReport {
    class Program {
        static void Main(string[] args) {
            StreamWriter sw = new StreamWriter("report.txt");
            string conn = @"Data Source=DONISBOOK;AttachDbFilename=" +
                @"'...AdventureWorks_Data.mdf';Integrated Security=True";
            DataContext context = new DataContext(conn);
            Table<Employee> employees = context.GetTable<Employee>();

            var sales = from person in employees
                        where person.Title == "Sales Representative"
                        select person;

            foreach (var item in sales) {
                sw.WriteLine(
                    "
{0} {1} {2}

Orders:",
                    item.EmployeeID,
                    item.Name.FirstName,
                    item.Name.LastName);

                foreach (var salesitem in item.Sales) {
                    sw.WriteLine("{0}", salesitem.SalesOrderID);
                }
            }
        }
    }
    [Table(Name = "HumanResources.Employee")]
    public class Employee {
        [Column(IsPrimaryKey = true)] public int EmployeeID;
        [Column] public string Title;
        [Column] public int ContactID;

        private EntitySet<SalesOrderHeader> propSales = null;
        [Association(Storage = "propSales", ThisKey = "EmployeeID",
            OtherKey = "SalesPersonID")]
        public EntitySet<SalesOrderHeader> Sales {
            get { return this.propSales; }
            set { this.propSales.Assign(value); }
        }

        private EntityRef<Contact> propName;
        [Association(Storage = "propName", ThisKey = "ContactID")]
        public Contact Name {
            get { return this.propName.Entity; }
            set { this.propName.Entity = value; }
        }
    }

    [Table(Name = "Sales.SalesOrderHeader")]
    public class SalesOrderHeader {
        [Column(IsPrimaryKey=true)] public int SalesOrderID;
        [Column] public int CustomerID;
        [Column] public int SalesPersonID;

        private EntityRef<Employee> propSalesPerson;
        [Association(Storage = "propSalesPerson", ThisKey = "SalesPersonID")]
        public Employee SalesPerson {
        get { return this.propSalesPerson.Entity; }
        set { this.propSalesPerson.Entity = value; }
        }
    }

    [Table(Name = "Person.Contact")]
    public class Contact {
        [Column(IsPrimaryKey = true)] public int ContactID;
        [Column] public string FirstName;
        [Column] public string LastName;
    }
}

As mentioned, the DataContext type is the bridge between LINQ to SQL and the relational database. DataContext creates an in-memory representation of a data table or view, which is cached in entity classes. This is the disconnected model with optimistic locking. This model is not ideal for highly contentious data sources, where there is likely to be a high number of conflicts in a short period of time. The DataContext is also responsible for updating changes back to the original data source and resolving possible conflicts. You can specify what action to take when a conflict occurs.

In LINQ to SQL, the Identity Management Service tracks changes to entities. The Identity Management Service keeps a single instance of a row in memory. For example, if separate queries return overlapping results, the common results reference the same entities. This keeps the in-memory representation synchronized. Entities must have a primary key defined to be tracked by the Identity Management Service. Entities without a primary key are read-only, and changes are discarded.

Changing an existing record is easy. Change a value of a mapped data member or property in the related entity. This will update the data in memory.

To add a new record, create a new instance or instances of the entity. Call Table<TEntity>.InsertOnSubmit to add a single entity (record). Table<TEntity>.InsertOnAllSubmit adds a collection of entities.

To delete a record, first find the record or records using a query expression. Then call Table<TEntity>.DeleteOnSubmit to delete a single entity (record). Table<TEntity>.DeleteAllOnSubmit deletes a collection of entities.

DataContext.SubmitChanges persists changes (updates, inserts, or deletions) to the data source. Prior to calling this method, only the in-memory representation is changed. You can obtain the pending changes with the DataContext.GetChangeSet method. The return value from this method is a ChangeSet type, which has a collection for each type of change: Inserts, Updates, and Deletes. The ChangeSet.ToString method returns a summary of changes. This is the signature of DataContext.SubmitChanges:

public void SubmitChanges()

public void SubmitChanges(ConflictMode failureMode)

ConflictMode is an enumeration, where FailOnFirstConflict and ContinueOnConflict are the values. FailOnFirstConflict means updates will stop on the first conflict. ContinueOnConflict means all updates are attempted even if a conflict occurs prior to completing.

ChangeTable is a console application that updates the ContactType table of the AdventureWorks_Data database. You enter individual commands from the command line. For example, the following commands add, delete, and modify a record in the ContactType table.

C:>changetable –del 21

C:>changetable –add "Senior Manager"

C:>changetable –update 20 Director

The first command deletes the record that contains the specified ContactID. The second command adds a new record for Senior Manager. Finally, the last command updates the record with the specified ContactID. The name in that record is changed to Director. Running the program with no command-line arguments will list the ContactType records without making any updates.

In the sample code, the switch statement partitions and handles each command: add, delete, and update. After the switch statement, DataContext.SubmitChanges saves the changes to the actual database. The last action is to list the contents of the ContactType table, including any changes. Here is the code for the ChangeTable application. The entity class for the ContactType table is at the end of the sample code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace ChangeTable {
    class Program {
        static void Main(string[] args) {

            string conn = @"Data Source=DONISBOOK;AttachDbFilename=" +
                @"'...AdventureWorks_Data.mdf';Integrated Security=True";
            DataContext context = new DataContext(conn);
            Table<ContactType> contacts = context.GetTable<ContactType>();
            ContactType record = null;
            if (args.Length > 0) {
                switch (args[0].ToLower()) {
                    case "add":
                    case "a":
                        record = new ContactType {
                            Name = args[1],
                            ModifiedDate = DateTime.Now
                        };
                        contacts.InsertOnSubmit(record);
                        break;
                    case "del":
                    case "d":
                        record = contacts.Where(c => c.ContactTypeID == int.Parse(args[1])).
First();
                        contacts.DeleteOnSubmit(record);
                        break;
                    case "update":
                    case "u":
                        record = contacts.Where(c => c.ContactTypeID == int.Parse(args[1])).
First();
                        record.Name = args[2];
                        break;
                }
                Console.WriteLine("{0}
", context.GetChangeSet().ToString());
                context.SubmitChanges();
            }
            Console.WriteLine("Contact type list:
");
            foreach (var contact in contacts) {
                Console.WriteLine("{0} {1} {2}",
                    contact.ContactTypeID,
                    contact.Name,
                    contact.ModifiedDate);
            }
        }
    }

    [Table(Name = "Person.ContactType")]
    public class ContactType {
        [Column(IsPrimaryKey=true, IsDbGenerated=true)] public int ContactTypeID;
        [Column] public string Name;
        [Column] public DateTime ModifiedDate;
    }
}

Exception handling is an essential ingredient in software development and in creating a robust application. Chapter 12, discusses various aspects of exception handling, including protected bodies, exception handlers, and termination handlers. A protected body, also known as a guarded body, is a try block and encapsulates protected code. When an exception is raised in the protected code, execution is transferred to the exception filter. The exception filter is a catch statement that identifies which exceptions are handled at that location on the call stack. Termination handlers are finally blocks. Place cleanup code in a finally block, where code is executed whether an exception is raised or not.

There are system or hard exceptions such as access violations and software exceptions, which are thrown by the CLR. You can throw some system exceptions. However, you also can throw user-defined exceptions.

Unhandled exceptions can crash an application, causing a crash dialog box to be displayed. You can override this default behavior with the appropriate unhandled exception event handler. This and other topics related to exception handling are detailed in the next chapter.