LINQ to SQL was the first object-relational mapping technology released by Microsoft. It works well in basic scenarios and continues to be supported in Visual Studio, but it is no longer under active development. Use LINQ to SQL when maintaining a legacy application that is already using it, or in simple applications that use SQL Server and do not require multi-table mapping. In general, new applications should use the Entity Framework when an object-relational mapper layer is required.
In Visual Studio, you create LINQ to SQL classes that represent SQL tables by using the O/R Designer.
The O/R Designer has two distinct areas on its design surface: the entities pane on the left, and the methods pane on the right. The entities pane is the main design surface that displays the entity classes, associations, and inheritance hierarchies. The methods pane is the design surface that displays the DataContext methods that are mapped to stored procedures and functions.
The Object Relational Designer (O/R Designer) provides a visual design surface for creating LINQ to SQL entity classes and associations (relationships) that are based on objects in a database. In other words, the O/R Designer is used to create an object model in an application that maps to objects in a database. It also generates a strongly-typed DataContext that is used to send and receive data between the entity classes and the database. The O/R Designer also provides functionality to map stored procedures and functions to DataContext methods for returning data and populating entity classes. Finally, the O/R Designer provides the ability to design inheritance relationships between entity classes.
Opening the O/R Designer
To add a LINQ to SQL entity model to your project, choose Project | Add New Item and then choose LINQ to SQL Classes from the list of project items:
Visual Studio creates a .dbml file and adds it to your solution. This is the XML mapping file and its related code files.
When you select the .dbml file, Visual Studio shows the O/R designer surface that enables you to visually create the model. The following illustration shows the designer after the Northwind Customers and Orders tables have been dragged from Server Explorer. Note the relationship between the tables.
The O/R Designer is a simple object relational mapper because it supports only 1:1 mapping relationships. In other words, an entity class can have only a 1:1 mapping relationship with a database table or view. Complex mapping, such as mapping an entity class to a joined table, is not supported; use the Entity Framework for complex mapping. Additionally, the designer is a one-way code generator. This means that only changes that you make to the designer surface are reflected in the code file. Manual changes to the code file are not reflected in the O/R Designer. Any changes that you make manually in the code file are overwritten when the designer is saved and code is regenerated. For information about how to add user code and extend the classes generated by the O/R Designer, see How to: Extend Code Generated by the O/R Designer.
Creating and Configuring the DataContext
After you add a LINQ to SQL Classes item to a project and open the O/R Designer, the empty design surface represents an empty DataContext ready to be configured. the DataContext is configured with connection information provided by the first item that is dragged onto the design surface.. Therefore, the DataContext is configured by using connection information from the first item dropped onto the design surface. For more information about the DataContext class see, DataContext Methods (O/R Designer).
Creating Entity Classes That Map to Database Tables and Views
You can create entity classes mapped to tables and views by dragging database tables and views from Server Explorer/Database Explorer onto the O/R Designer. As indicated in the previous section the DataContext is configured with connection information provided by the first item that is dragged onto the design surface. If a subsequent item that uses a different connection is added to the O/R Designer, you can change the connection for the DataContext. For more information, see How to: Create LINQ to SQL classes mapped to tables and views (O/R Designer).
Creating DataContext Methods That Call Stored Procedures and Functions
You can create DataContext methods that call (are mapped to) stored procedures and functions by dragging them from Server Explorer/Database Explorer onto the O/R Designer. Stored procedures and functions are added to the O/R Designer as methods of the DataContext.
When you drag stored procedures and functions from Server Explorer/Database Explorer onto the O/R Designer, the return type of the generated DataContext method differs depending on where you drop the item. For more information, see DataContext Methods (O/R Designer).
Configuring a DataContext to Use Stored Procedures to Save Data Between Entity Classes and a Database
As stated earlier, you can create DataContext methods that call stored procedures and functions. Additionally, you can also assign stored procedures that can be used for the default LINQ to SQL runtime behavior that performs Inserts, Updates, and Deletes. For more information, see How to: Assign stored procedures to perform updates, inserts, and deletes (O/R Designer).
Inheritance and the O/R Designer
Like other objects, LINQ to SQL classes can use inheritance and be derived from other classes. In a database, inheritance relationships are created in several ways. The O/R Designer supports the concept of single-table inheritance as it is often implemented in relational systems. For more information, see How to: Configure inheritance by using the O/R Designer.
LINQ to SQL Queries
Separating the Generated DataContext and Entity Class Code into Different Namespaces
The O/R Designer provides the Context Namespace and Entity Namespace properties on the DataContext. These properties determine what namespace the DataContext and entity class code is generated into. By default, these properties are empty and the DataContext and entity classes are generated into the application's namespace. To generate the code into a namespace other than the application's namespace, enter a value into the Context Namespace and/or Entity Namespace properties.
In this section
Data class inheritance (O/R Designer)
Describes the concept of single-table inheritance and how it is implemented in the O/R Designer.
How to: Create LINQ to SQL classes mapped to tables and views (O/R Designer)
Describes how to create entity classes that are mapped to tables and views in a database.
How to: Create an association (relationship) between LINQ to SQL classes (O/R Designer)
Describes how to create a relationship between LINQ to SQL entity classes.
How to: Create DataContext methods mapped to stored procedures and functions (O/R Designer)
Describes how to create DataContext methods that run stored procedures or functions when they are called.
How to: Assign stored procedures to perform updates, inserts, and deletes (O/R Designer)
Describes how to configure a DataContext to use stored procedures when saving data from entity classes back to a database.
How to: Change the return type of a DataContext method (O/R Designer)
Describes how to set the return type of a DataContext method to be the type of an entity class or an auto-generated type created by the O/R Designer.
How to: Add validation to entity classes
Describes how to generate partial methods that enable the addition of code during property changes and entity class updates.
How to: Turn pluralization on and off (O/R Designer)
Describes how to turn on and off the automatic renaming of classes that are added to the O/R Designer.
How to: Configure inheritance by using the O/R Designer
Describes how to configure entity classes using single-table inheritance with the O/R Designer.
How to: Extend Code Generated by the O/R Designer
Describes how and where to add code that will not be overwritten when changes to objects on the O/R Designer regenerate code.
Walkthrough: Creating LINQ to SQL Classes by Using Single-Table Inheritance (O/R Designer)
Provides step-by-step instructions for configuring entity classes using single-table inheritance with the O/R Designer.
Walkthrough: Customizing the insert, update, and delete behavior of entity classes
Provides step-by-step instructions for configuring a DataContext to use stored procedures when saving data from entity classes back to a database.