LINQ to SQL database row not updating?

Relational databases use rows. Objects have unique identity as each instance is physically different from another. Rows are identified by primary key values.

Objects have references that identify and link instances together. Rows are left intentionally distinct requiring related rows to be tied together loosely using foreign keys. Objects stand alone, existing as long as they are still referenced by another object.

Rows exist as elements of tables, vanishing as soon as they are removed. It is no wonder that applications expected to bridge this gap are difficult to build and maintain. It would certainly simplify the equation to get rid of one side or the other.

Yet relational databases provide critical infrastructure for long-term storage and query processing, and modern programming languages are indispensable for agile development and rich computation. Until now, it has been the job of the application developer to resolve this mismatch in each application separately. The best solutions so far have been elaborate database abstraction layers that ferry the information between the applications domain-specific object models and the tabular representation of the database, reshaping and reformatting the data each way.

Yet by obscuring the true data source, these solutions end up throwing away the most compelling feature of relational databases; the ability for the data to be queried. LINQ to SQL, a component of Visual Studio Code Name "Orcas", provides a run-time infrastructure for managing relational data as objects without losing the ability to query. It does this by translating language-integrated queries into SQL for execution by the database, and then translating the tabular results back into objects you define.

Your application is then free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically. LINQ to SQL is designed to be non-intrusive to your application. It is possible to migrate current ADO.NET solutions to LINQ to SQL in a piecemeal fashion (sharing the same connections and transactions) since LINQ to SQL is simply another component in the ADO.NET family.

LINQ to SQL also has extensive support for stored procedures, allowing reuse of the existing enterprise assets. LINQ to SQL applications are easy to get started. Objects linked to relational data can be defined just like normal objects, only decorated with attributes to identify how properties correspond to columns.

Of course, it is not even necessary to do this by hand. A design-time tool is provided to automate translating pre-existing relational database schemas into object definitions for you. Together, the LINQ to SQL run-time infrastructure and design-time tools significantly reduce the workload for the database application developer.

The following chapters provide an overview of how LINQ to SQL can be used to perform common database-related tasks. It is assumed that the reader is familiar with Language-Integrated Query and the standard query operators. LINQ to SQL is language-agnostic.

Any language built to provide Language-Integrated Query can use it to enable access to information stored in relational databases. The samples in this document are shown in both C# and Visual Basic; LINQ to SQL can be used with the LINQ-enabled version of the Visual Basic compiler as well. The first step in building a LINQ to SQL application is declaring the object classes you will use to represent your application data.

Let's walk through an example. We will start with a simple class Customer and associate it with the customers table in the Northwind sample database. To do this, we need only apply a custom attribute to the top of the class declaration.

LINQ to SQL defines the Table attribute for this purpose. The Table attribute has a Name property that you can use to specify the exact name of the database table. If no Name property is supplied, LINQ to SQL will assume the database table has the same name as the class.

Only instances of classes declared as tables will be stored in the database. Instances of these types of classes are known as entities. The classes themselves are known as entity classes.

In addition to associating classes to tables you will need to denote each field or property you intend to associate with a database column. For this, LINQ to SQL defines the Column attribute. The Column attribute has a variety of properties you can use to customize the exact mapping between your fields and the database columns.

One property of note is the Id property. It tells LINQ to SQL that the database column is part of the primary key in the table. As with the Table attribute, you only need to supply information in the Column attribute if it differs from what can be deduced from your field or property declaration.

In this example, you need to tell LINQ to SQL that the CustomerID field is part of the primary key in the table, yet you don't have to specify the exact name or type. Only fields and properties declared as columns will be persisted to or retrieved from the database. Others will be considered as transient parts of your application logic.

Each database table is represented as a Table collection, accessible via the GetTable() method using its entity class to identify it. It is recommended that you declare a strongly typed DataContext instead of relying on the basic DataContext class and the GetTable() method. A strongly typed DataContext declares all Table collections as members of the context.