Walkthrough: Creating LINQ to SQL Classes (O/R Designer)

The Object Relational Designer (O/R Designer) provides a visual design surface for creating and editing LINQ to SQL classes (entity classes) that are based on objects in a database. By using LINQ to SQL [LINQ to SQL], you can access SQL databases with LINQ technology. For more information, see LINQ (Language-Integrated Query).

This walkthrough provides the steps that you must follow to create LINQ to SQL entity classes mapped to the Customers and Orders tables in the Northwind database and display the data on a Windows Form. In addition to the steps for displaying the data from the table, the steps for binding data to a LINQ query are also provided. Finally, the steps for using stored procedures to replace the default LINQ to SQL logic for sending updates from the entity classes to the database are provided.

During this walkthrough, you will learn how to perform the following tasks:

• Add a LINQ to SQL file to a project.

• Create new entity classes that are mapped to related tables in the database.

• Create an object data source that references the entity classes.

• Create a Windows Form containing controls that are bound to entity classes.

• Add code to load and save the data between the entity classes and the database.

• Construct a simple LINQ query and display the results on the form.

• Add stored procedures to the O/R Designer.

• Configure an entity class to use stored procedures to perform Inserts, Updates, and Deletes.

Prerequisites

To complete this walkthrough, you need the following:

• Access to the SQL Server version of the Northwind sample database. For more information, see How to: Install Sample Databases.

• The UpdateCustomer stored procedure for the Northwind database. For more information, see Walkthrough: Creating Update Stored Procedures for the Northwind Customers Table.

Creating the Windows-Based Application

Because you will be working with LINQ to SQL classes and displaying the data on a Windows Form, the first step in this walkthrough is to create a new Windows Forms application.

To create the new Windows Application project

1. From the File menu, create a new project.

2. Name the project ORDesignerWalkthrough.

   Note

   The O/R Designer is supported in Visual Basic and C# projects, so create the new project in one of these languages.

3. Click the Windows Forms Application template and click OK. For more information, see Developing Client Applications.

   The ORDesignerWalkthrough project is created and added to Solution Explorer.

Adding a LINQ to SQL Classes File to the Project (Opening the O/R Designer)

Entity classes are created and stored in LINQ to SQL Classes files (.dbml files). The O/R Designer opens when you open a .dbml file. Add .dbml files to projects by selecting the LINQ to SQL Classes template in the Add New Item dialog box.

To add a .dbml file to a project

1. On the Project menu, click Add New Item.

2. Click the LINQ to SQL Classes template and type Northwind.dbml in the Name box.

3. Click Add.

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

After you add the new LINQ to SQL file to the project, the empty design surface opens, displaying two separate panes. The pane on the left is the entities pane, where entity classes are displayed and configured. The pane on the right is the methods pane that displays the DataContext methods added to the designer. If the methods pane is not visible right click an empty area in the entities pane and click Show Methods Pane. The entire empty surface represents a DataContext ready to be configured. The DataContext name corresponds to the name that you provided for the .dbml file. For this walkthrough, because you named the LINQ to SQL file Northwind.dbml, the DataContext is named NorthwindDataContext. You can verify this by clicking any empty area on the designer and inspecting the Properties window.

Creating Customer and Order Entity Classes

Create LINQ to SQL classes that are mapped to database tables by dragging tables from Server Explorer/Database Explorer onto the O/R Designer. The result is a LINQ to SQL entity class that maps to the table in the database.

To add a Customer entity class to the O/R Designer

1. In Server Explorer/Database Explorer, locate the tables in the SQL Server version of the Northwind sample database. For more information, see How to: Connect to the Northwind Database.

2. Drag the Customers node from Server Explorer/Database Explorer onto the O/R Designer surface.

   An entity class named Customer is created. It has properties that correspond to the columns in the Customers table. The entity class is named Customer (not Customers) because it represents a single customer from the Customers table.

   Note

   This renaming behavior is called pluralization. It can be turned on or off in the Options Dialog Box (Visual Studio). For more information, see How to: Turn Pluralization On and Off (O/R Designer).

3. Drag the Orders node from Server Explorer/Database Explorer onto the O/R Designer surface.

   An entity class named Order is created, along with a Customer_Order association (relationship) to the Customer entity class. It has properties that correspond to the columns in the Orders table.

   Note

   The entity class is named Order because it represents a single order. The parent class (Customer) has an Orders property that represents the collection of orders for that specific customer. For more information about LINQ to SQL associations, see How to: Create an Association (Relationship) Between LINQ to SQL Classes (O/R Designer).

Creating an Object Data Source with the Customer Entity Class

Entity classes, just like other classes that have public properties, can be used as object data sources. They can be added to the Data Sources window and dragged onto forms to create data-bound controls (controls that are bound to the values in the public properties of the object). Add entity classes to the Data Sources window by running the Data Source Configuration Wizard and clicking Object for the data source in the wizard.

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

1. On the Build menu, click Build ORDesignerWalkthrough to build the project.

2. On the Data menu, click Show Data Sources.

3. In the Data Sources window, click Add New Data Source.

4. Click Object on the Choose a Data Source Type page and then click Next.

5. Expand the ORDesignerWalkthrough node (the node with the name of your project) and locate and select the Customer class.

   Note

   If the Customer class is not available, cancel out of the wizard, build the project, and run the wizard again.

6. Click Finish to create the data source and add the Customer entity class to the Data Sources window.

Creating Data-Bound Controls to Display the Data on a Windows Form

Create controls that are bound to entity classes by dragging LINQ to SQL data source items from the Data Sources window onto a Windows Form.

To add controls bound to the entity classes

1. Open Form1 in Design view.

2. From the Data Sources window, drag the Customer node onto Form1.

   Note

   To display the Data Sources window, click Show Data Sources on the Data menu.

3. Drag the Orders node from the Data Sources window onto Form1. Place it under CustomerDataGridView.

4. Open Form1 in code view.

5. Add the following code to the form, global to the form, outside any specific method, but inside the Form1 class:

   Private NorthwindDataContext1 As New NorthwindDataContext
   

   private NorthwindDataContext northwindDataContext1
       = new NorthwindDataContext();
   

6. Create an event handler for the Form_Load event and add the following code to the handler:

   CustomerBindingSource.DataSource = NorthwindDataContext1.Customers
   

   customerBindingSource.DataSource
       = northwindDataContext1.Customers;
   

Testing the Application

Run the application. At this point the form contains one DataGridView displaying the data from the Customers table and a second DataGridView displaying the data from the selected customer's orders.

To test the application

1. Press F5.

2. Verify that data appears in the grids.

3. Select a customer.

4. Verify that the orders displayed are for the selected customer.

5. Close the form. (On the Debug menu, click Stop Debugging.)

Implementing Save Functionality

As noted earlier, by default the save button is not enabled, and save functionality is not implemented. Also, code is not automatically added to save changed data to the form when data-bound controls are created for object data sources. This section explains how to enable the save button and implement save functionality for LINQ to SQL objects.

To implement save functionality

1. Open Form1 in Design view.

2. Select the save button on the CustomerBindingNavigator. (The button labeled with a floppy disk icon.)

3. In the Properties window, set the Enabled property to True.

4. Double-click the save button to create an event handler and switch to the Code Editor.

5. Add the following code into the save button event handler:

   Try
       NorthwindDataContext1.SubmitChanges()
   Catch ex As Exception
       MessageBox.Show(ex.Message)
   End Try
   

   try
   {
       northwindDataContext1.SubmitChanges();
   }
   catch (Exception ex)
   {
       MessageBox.Show(ex.Message);
   }
   

Testing the Application

Run the application. The save button should be enabled, and the ability to save data is available.

To test the application

1. Press F5.

2. Modify some data in either grid. (Navigate off the edited row in the grid to commit in-process changes.)

3. Click the save button to save changes back to the database.

4. Close the form.

5. Press F5 and verify that the changes were persisted (or locate the table in the database to verify that the changes were saved).

Binding to LINQ Queries

In addition to binding the CustomerBindingSource to the DataContext, you can also bind directly to LINQ queries. For more information about how to create LINQ queries, see Introduction to LINQ Queries (C#).

Adding a Button and TextBox to the Form

To learn how to bind controls to LINQ queries, add controls to the form that enable you to enter a query parameter and then run the query.

To add controls to the form

1. Open Form1 in Design view.

2. Add a TextBox to the form and set its Name property to CityTextBox.

3. Add a Button to the form and set the following properties:

   • Name = RunQueryButton

   • Text = Run Query

Data Binding to the LINQ Query

Add code to run a LINQ query. The query uses the value typed into CityTextBox as a query parameter.

To bind to a LINQ query

• Double-click the RunQueryButton and add the following code to the RunQueryButton_click event handler:

  Dim CustomersQuery = From customers in NorthwindDataContext1.Customers _
      Where customers.City = CityTextBox.Text _
      Select customers
  
  CustomerBindingSource.DataSource = CustomersQuery
  

  var CustomersQuery = from customers in northwindDataContext1.Customers
                        where customers.City == CityTextBox.Text
                        select customers;
  
  customerBindingSource.DataSource = CustomersQuery;
  

Testing the Application

Run the application. You can now query for customers in a specific city.

To test the application

1. Press F5.

2. Type London in the text box.

3. Click the Run Query button.

4. Verify that only customers who have a value of London in their City property are displayed.

Overriding the Default Behavior for Performing Updates (Inserts, Updates, and Deletes)

By default, the logic to perform updates is provided by the LINQ to SQL runtime. The runtime creates default Insert, Update, and Delete statements based on the Select statement that is used to populate your entity class with data. When you do not want to use the default behavior, you can configure the update behavior and designate specific stored procedures for performing the necessary Inserts, Updates, and Deletes required to manipulate the data in your database. You can also do this when the default behavior is not generated, for example, when your entity classes map to joined tables. Additionally, you can override the default update behavior when the database requires table access through stored procedures.

To override the default update behavior

1. Open the LINQ to SQL file in the O/R Designer. (Double-click the Northwind.dbml file in Solution Explorer.)

2. In Server Explorer/Database Explorer, expand the Northwind databases Stored Procedures node and locate the UpdateCustomers stored procedure.

3. Drag the UpdateCustomers stored procedure onto the O/R Designer.

   The UpdateCustomers stored procedure is added to the methods pane as a DataContext method. For more information, see DataContext Methods (O/R Designer).

4. Select the Customer entity class in the O/R Designer.

5. In the Properties window, select the command to override. (Insert, Update, or Delete). For this example, select the Update property.

6. Click the ellipsis next to Use Runtime to open the Configure Behavior dialog box.

7. Select Customize.

8. Select the UpdateCustomers method in the Customize list.

9. Inspect the list of Method Arguments and Class Properties and notice that there are two Method Arguments and two Class Properties for some columns in the table. This facilitates tracking changes and creating statements that check for concurrency violations.

10. Map the original method arguments (Original_ArgumentName) to the original properties (PropertyName (Original)). For this walkthrough, you have to map the Original_CustomerID argument to the CustomerID (Original) property.

    Note

    By default, method arguments will map to class properties when the names match. If property names were changed and no longer match between the table and the entity class, you might have to select the equivalent class property to map to if the designer cannot determine the correct mapping. Additionally, if method arguments do not have valid class properties to map to, you can set the Class Properties value to (None).

11. Click OK.

Testing the Application

Run the application again to verify that the UpdateCustomers stored procedure correctly updates the customer record on the database.

To test the application

1. Press F5.

2. Locate the ContactName column in the grid for ALFKI.

3. Change the name from Maria Anders to Anders.

4. Navigate off the row to commit the change.

5. Click the save button.

6. Close the form.

7. Press F5 to run the application again and verify that only Anders appears in the ContactName column for ALFKI.

Next Steps

Depending on your application requirements, there are several steps that you may want to perform after you create LINQ to SQL entity classes. Some enhancements you could make to this application include the following:

• Adding more stored procedures to use for the Insert and Delete commands. For more information, see How to: Assign Stored Procedures to Perform Updates, Inserts, and Deletes (O/R Designer).

• Construct various LINQ queries to return filtered data. For more information, see How to: Query for Information.

See Also

Concepts

O/R Designer Overview

PAVE What's New for Data Application Development in Visual Studio 2012

Other Resources

Object Relational Designer (O/R Designer)

LINQ to SQL [LINQ to SQL]

LINQ General Programming Guide

LINQ to ADO.NET

LINQ Documentation Roadmap

Accessing Data in Visual Studio