Creating LINQ to SQL Classes with the O/R Designer

Article
11/16/2012

You can use the Object Relational Designer (O/R Designer) to map LINQ to SQL classes to tables in a database. These mapped classes are also known as entity classes. The properties of an entity class map to the columns of the table, and you can bind the data to controls on a Windows Form. To learn more about LINQ to SQL, see Using LINQ in C#.

Prerequisites

If you did not install SQL Server Express Edition when you installed Visual C# Express Edition, install it before continuing. SQL Server Compact 3.5 databases are not currently supported in LINQ to SQL. For information about how to obtain SQL Server Express Edition, see the Obtaining SQL Server Express section in the How to: Install Sample Databases topic.
You must also have access to the SQL Server version of the Northwind sample database. For information about how to obtain and install this database, see How to: Install Sample Databases.

Opening the O/R Designer

To open the O/R Designer

On the File menu, click New Project.
In the New Project dialog box, click Windows Forms Application, name it LINQToSQLSample, and then click OK.

A new Windows Forms project opens.
On the Project menu, click Add New Item.
Click the LINQ to SQL Classes template and type NorthwindSample.dbml in the Name box.
Click Add.

An empty LINQ to SQL Classes file (NorthwindSample.dbml) is added to the project, and the O/R Designer opens.

The O/R Designer consists of two design surfaces: the entities pane on the left, and the methods pane area on the right. The entities pane is the main design surface that displays the entity classes defined in the file. The methods pane is the design surface that displays the DataContext methods mapped to stored procedures and functions. You can hide the methods pane by right-clicking the O/R Designer and then clicking Hide Methods Pane. You will not use the methods pane in this lesson, but you can learn more about it in DataContext Methods (O/R Designer).

Click the main design surface, and verify its name is NorthwindSampleDataContext in the Properties window. You can create entity classes by dragging database tables from Database Explorer onto the O/R Designer surface. But first, you must create a connection to the database. This process resembles what was described in How to: Connect to a Database (C#), except that here you will use a SQL Server Express database.

To connect to the Northwind sample database

On the View menu, point to Other Windows, and then click Database Explorer.

Database Explorer opens.
Click the Connect to Database button.

The Add Connection dialog box opens.
In the Add Connection dialog box, make sure that the Data source is Microsoft SQL Server Database File.
Click Browse and select the Northwind.mdf database.
Click Test Connection to verify that the connection was successful.
Click OK to close the Add Connection dialog box.

Creating Entity Classes

The tables in the Northwind database are now visible in the Database Explorer. You can drag tables from the Database Explorer onto the O/R Designer. This automatically creates entity classes that have properties that map to the columns in the tables that were selected.

To create entity classes

In Database Explorer, expand Northwind.mdf, and then expand Tables.
Drag Customers from Database Explorer onto the O/R Designer surface. If prompted to copy the data file to your project, click Yes.

An entity class named Customer is created. It has properties that correspond to the columns in the Customers table.

Note

The entity class name is Customer, instead of Customers. This renaming behavior is called pluralization. It can be turned on or off in the Options dialog box. For more information, see Options Dialog Box (Visual Studio) and How to: Turn Pluralization On and Off (O/R Designer).
Drag Orders from Database Explorer to the O/R Designer surface.

An entity class named Order is created. Notice that the Order class has a relationship (known as an association) with the Customer entity class. The properties of the Order class correspond to the columns in the Orders table.

Now that you have created the entity classes, you can add them to the Data Sources window by running the Data Source Configuration Wizard.

To add an entity class as an object data source in the Data Sources window

On the Build menu, click Build Solution.
On the Data menu, click Show Data Sources.
In the Data Sources window, click the Add New Data Source button.
Click Object on the Choose a Data Source Type page and then click Next.
Expand LINQToSQLSample and click the Customer class.

Note

If the Customer class is not available, click Cancel, build the project, and run the wizard again.
Click Finish to create the data source and add the Customer entity class to the Data Sources window.

Binding Controls on a Windows Form to Entity Classes

You can add controls that are data-bound to entity classes to a Windows Form by dragging tables or fields from the Data Sources window to the form.

To add controls bound to the entity classes

Right-click Form1 in Solution Explorer, and then click View Designer.
In the Data Sources window, expand Customer.
Click the drop-down next to CompanyName and click Label.
Drag CompanyName to the form.
Drag Orders from the Data Sources window to Form1. Place it under CompanyName.
Right-click Form1 in Solution Explorer, and then click View Code.

Add the following code to the form, at the top of the Form class, outside any specific method.

private NorthwindSampleDataContext northwindSampleDataContext1
    = new NorthwindSampleDataContext();

Create an event handler for the Form_Load event and add the following code to the event handler:
```
customerBindingSource.DataSource
    = northwindSampleDataContext1.Customers;
```
Press F5 to run the code.
When the form opens, click the bindingNavigatorMoveNextItem button to scroll through the companies and verify that the ordersDataGridView is updated to display the selected customer's orders.

Saving Changes

When you add controls to the O/R Designer, the Save button on the bindingNavigator is not enabled. If you want to enable users to save changes, you must first enable the Save button and then add code to save changed data.

To enable the Save button

Click the Save button on the bindingNavigator. (The button with the floppy disk icon.)
In the Properties window, set the Enabled property to True.
Double-click the save button to create an event handler and switch to the Code Editor.
Add the following code into the Save button event handler:
```
northwindSampleDataContext1.SubmitChanges();
```
Press F5 to run the code.
Change the value in one of the Shipped Date fields and press the TAB key to commit the change.
Click the Save button.
Close the form.
Press F5, and verify the change you made was saved.