The LINQ to SQL Object Model
In LINQ to SQL, an object model expressed in the programming language of the developer is mapped to the data model of a relational database. Operations on the data are then conducted according to the object model.
In this scenario, you do not issue database commands (for example, INSERT) to the database. Instead, you change values and execute methods within your object model. When you want to query the database or send it changes, LINQ to SQL translates your requests into the correct SQL commands and sends those commands to the database.
.png "LINQ to SQL Object Model")
The most fundamental elements in the LINQ to SQL object model and their relationship to elements in the relational data model are summarized in the following table:
LINQ to SQL Object Model |
Relational Data Model |
|---|---|
Entity class |
Table |
Class member |
Column |
Association |
Foreign-key relationship |
Method |
Stored Procedure or Function |
Note
The following descriptions assume that you have a basic knowledge of the relational data model and rules.
LINQ to SQL Entity Classes and Database Tables
In LINQ to SQL, a database table is represented by an entity class. An entity class is like any other class you might create except that you annotate the class by using special information that associates the class with a database table. You make this annotation by adding a custom attribute (TableAttribute) to your class declaration, as in the following example:
Example
<Table(Name:="Customers")> _
Public Class Customer
Public CustomerID As String
' ...
Public City As String
End Class
[Table(Name = "Customers")]
public class Customerzz
{
public string CustomerID;
// ...
public string City;
}
Only instances of classes declared as tables (that is, entity classes) can be saved to the database.
For more information, see the Table Attribute section of Attribute-Based Mapping (LINQ to SQL).
LINQ to SQL Class Members and Database Columns
In addition to associating classes with tables, you designate fields or properties to represent database columns. For this purpose, LINQ to SQL defines the ColumnAttribute attribute, as in the following example:
Example
<Table(Name:="Customers")> _
Public Class Customer
<Column(IsPrimaryKey:=True)> _
Public CustomerID As String
<Column()> _
Public City As String
End Class
[Table(Name = "Customers")]
public class Customer
{
[Column(IsPrimaryKey = true)]
public string CustomerID;
[Column]
public string City;
}
Only fields and properties mapped to columns are persisted to or retrieved from the database. Those not declared as columns are considered as transient parts of your application logic.
The ColumnAttribute attribute has a variety of properties that you can use to customize these members that represent columns (for example, designating a member as representing a primary key column). For more information, see the Column Attribute section of Attribute-Based Mapping (LINQ to SQL).
LINQ to SQL Associations and Database Foreign-key Relationships
In LINQ to SQL, you represent database associations (such as foreign-key to primary-key relationships) by applying the AssociationAttribute attribute. In the following segment of code, the Order class contains a Customer property that has an AssociationAttribute attribute. This property and its attribute provide the Order class with a relationship to the Customer class.
The following code example shows the Customer property from the Order class.
Example
<Association(Name:="FK_Orders_Customers", Storage:="_Customer", ThisKey:="CustomerID", IsForeignKey:=true)> _
Public Property Customer() As Customer
Get
Return Me._Customer.Entity
End Get
Set
Dim previousValue As Customer = Me._Customer.Entity
If (((previousValue Is value) _
= false) _
OrElse (Me._Customer.HasLoadedOrAssignedValue = false)) Then
Me.SendPropertyChanging
If ((previousValue Is Nothing) _
= false) Then
Me._Customer.Entity = Nothing
previousValue.Orders.Remove(Me)
End If
Me._Customer.Entity = value
If ((value Is Nothing) _
= false) Then
value.Orders.Add(Me)
Me._CustomerID = value.CustomerID
Else
Me._CustomerID = CType(Nothing, String)
End If
Me.SendPropertyChanged("Customer")
End If
End Set
End Property
[Association(Name="FK_Orders_Customers", Storage="_Customer", ThisKey="CustomerID", IsForeignKey=true)]
public Customer Customer
{
get
{
return this._Customer.Entity;
}
set
{
Customer previousValue = this._Customer.Entity;
if (((previousValue != value)
|| (this._Customer.HasLoadedOrAssignedValue == false)))
{
this.SendPropertyChanging();
if ((previousValue != null))
{
this._Customer.Entity = null;
previousValue.Orders.Remove(this);
}
this._Customer.Entity = value;
if ((value != null))
{
value.Orders.Add(this);
this._CustomerID = value.CustomerID;
}
else
{
this._CustomerID = default(string);
}
this.SendPropertyChanged("Customer");
}
}
}
For more information, see the Association Attribute section of Attribute-Based Mapping (LINQ to SQL).
LINQ to SQL Methods and Database Stored Procedures
LINQ to SQL supports stored procedures and user-defined functions. In LINQ to SQL, you map these database-defined abstractions to client objects so that you can access them in a strongly typed manner from client code. The method signatures resemble as closely as possible the signatures of the procedures and functions defined in the database. You can use IntelliSense to discover these methods.
A result set that is returned by a call to a mapped procedure is a strongly typed collection.
LINQ to SQL maps stored procedures and functions to methods by using the FunctionAttribute and ParameterAttribute attributes. Methods representing stored procedures are distinguished from those representing user-defined functions by the IsComposable property. If this property is set to false (the default), the method represents a stored procedure. If it is set to true, the method represents a database function.
Note
If you are using Visual Studio, you can use the Object Relational Designer to create methods mapped to stored procedures and user-defined functions. How to: Create DataContext Methods Mapped to Stored Procedures and Functions (O/R Designer)
How to: Create DataContext Methods Mapped to Stored Procedures and Functions (O/R Designer)
Example
' This is an example of a stored procedure in the Northwind
' sample database. The IsComposable property defaults to false.
<FunctionAttribute(Name:="dbo.CustOrderHist")> _
Public Function CustOrderHist(<Parameter(Name:="CustomerID", DbType:="NChar(5)")> ByVal customerID As String) As ISingleResult(Of CustOrderHistResult)
Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), customerID)
Return CType(result.ReturnValue, ISingleResult(Of CustOrderHistResult))
End Function
// This is an example of a stored procedure in the Northwind
// sample database. The IsComposable property defaults to false.
[Function(Name="dbo.CustOrderHist")]
public ISingleResult<CustOrderHistResult> CustOrderHist([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
return ((ISingleResult<CustOrderHistResult>)(result.ReturnValue));
}
For more information, see the Function Attribute, Stored Procedure Attribute, and Parameter Attribute sections of Attribute-Based Mapping (LINQ to SQL) and Stored Procedures (LINQ to SQL).
See Also
Reference
Attribute-Based Mapping (LINQ to SQL)