How to: Create, Read, Update, and Delete Data using LINQ

Article
09/22/2010

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

This topic provides examples of how to programmatically create, read, update, and delete data in the SQL Server Modeling Services database by using Language-Integrated Query (LINQ). For more information about LINQ, see Introduction to LINQ.

Note

This topic uses a HumanResources model. For more information about how to create this model and install it in the Modeling Services database, see How to: Install the HumanResources Sample Model using Visual Studio.

In this tutorial, you will perform the following tasks.

Use the Object Relational Designer (O/R) to add LINQ classes and methods for target objects in the Modeling Services database.
Create a new Folder named HumanResourcesExampleFolder that contains employee instances.
Add employee instances to the [HumanResources].[Employees] table.
Query a list of employees from the database.
Update an employee instance.
Remove the HumanResourcesExampleFolder Folder and its contents.

To create a connection to the Modeling Services database

Open Visual Studio 2010.
On the View menu, point to Other Windows, and then click Server Explorer.
In the Server Explorer window, right-click the Data Connections folder, and then select Add Connection.
In the Add Connection dialog, enter the server name and select the database name of the Modeling Services database.

Tip

Note that if the Modeling Services database is on the same machine, you can enter (local) for the server name. The default name for the database is "Repository".
Click the OK button to create the connection.

To create a LINQ console application

Create a new console application project. In the New Project dialog, select Visual Basic or Visual C#. Select the Console Application template, and name it RepAccess_LINQ. Use the default Location.
On the Project menu, click Add New Item.
In the Installed Templates pane, select Data.
In the list of data templates, select LINQ to SQL Classes.
In the Name edit box, type Repository.dbml
Click the Add button. The two-paned Object Relational (O/R) Designer should open.
In the Server Explorer window, expand the Modeling Services database connection, and then expand Tables.
Drag the "Employees (Human Resources)" table to the left pane of the O/R Designer.
Collapse the list of tables in Server Explorer.
Expand Views.
Drag and "Folders (Repository.Item)" view to the left pane of the O/R Designer.
Collapse the list of views in Server Explorer.
Expand Functions.
Drag the "PathsFolder (Repository.Item)" function to the right pane of the O/R Designer.

To modify the generated Employee and Folder data classes

In the O/R Designer for Repository.dbxml, select the Id column of the Folders data class.
In the Properties window, change the Primary Key property to True for the Id column.
Note

The steps in this section are necessary, because the Repository.Item schema uses updatable views. A view does not have a primary key, but the view targets a table with a primary key. The O/R Designer cannot make this assumption, so the change must be made manually.
In the O/R Designer, select the last field of the Folder data class. It might be named Folder1 or just Folder.
Change the Name property for this column to ParentFolder.
On the Build menu, click Build Solution.

To override the insert behavior in LINQ

In the O/R Designer for Repository.dbxml, right-click a blank area of the designer, and then select View Code.

Include two namespaces to the top of this source file: System.Linq and System.Collections.Generic.

Imports System.Linq
Imports System.Collections.Generic

using System.Linq;
using System.Collections.Generic;

Add definitions for two partial methods to the RepositoryDataContext class to override the insertion routines for the generated Folder data class.

Note

This is a necessary step, because the normal insertion routines call the SQL Server function, SCOPE_IDENTITY, to get the identifier for the inserted row. However, because the view inserts rows into the underlying tables using triggers, SCOPE_IDENTITY returns a null value. To prevent errors, the insertion is performed manually and then the identifiers are retrieved afterward.
```
Private Sub InsertFolder(ByVal instance As Folder)
   ExecuteCommand("insert into [Repository.Item].[Folders] (Name, Folder) " & _
      "values ({0}, {1})", instance.Name, instance.ParentFolder)
End Sub
```
```
partial void InsertFolder(Folder instance)
{
   ExecuteCommand("insert into [Repository.Item].[Folders] (Name, Folder) " +
      "values ({0}, {1})", instance.Name, instance.ParentFolder);
}
```

To use the generated LINQ classes

Open the main source file (Program.cs in Visual C# or Module1.vb in Visual Basic).
Include three namespaces to the top of the source file: System, System.Linq, and System.Data.Linq. In a Visual C# project, replace any existing using statements with the ones below.
```
Imports System
Imports System.Linq
Imports System.Data.Linq
```
```
using System;
using System.Linq;
using System.Data.Linq;
```

Add exception handling in the Main method using the following code.

Sub Main()
   Try

   Catch exception As Exception
      Console.WriteLine("An unexpected error occurred: {0}" & Environment.NewLine, exception.Message)
   Finally
      Console.ReadKey()
   End Try
End Sub

static void Main(string[] args)
{
   try
   {

   }
   catch (Exception exception)
   {
      Console.WriteLine("An unexpected error occurred: {0}" + Environment.NewLine, exception.Message);
   }
   finally
   {
      Console.ReadKey();
   }
}

Inside the try-block, add code to create a local variable, db, for the LINQ-generated RepositoryDataContext class.

' Create the DataContext object for the Repository. 
Dim db As RepositoryDataContext = New RepositoryDataContext()

// Create the DataContext object for the Repository. 
RepositoryDataContext db = new RepositoryDataContext();

Add local variables to use in later steps.

Note

Note that the Long? and long? type names specifies a long type that is nullable.

' Reference strongly typed tables for the [Repository.Item].[Folders]
' view and the [HumanResources].[Employees] table.
Dim foldersView As Table(Of Folder) = db.Folders
Dim employeesTable As Table(Of Employee) = db.Employees

Dim humanResourcesFolderId As Long?

// Reference strongly typed tables for the [Repository.Item].[Folders]
// view and the [HumanResources].[Employees] table.
Table<Folder> foldersView = db.Folders;
Table<Employee> employeesTable = db.Employees;

long? humanResourcesFolderId;

To create a new Modeling Services Folder

Immediately following the code in the previous section, add code that calls the LINQ wrapper for the [Repository.Item].[PathsFolder] function to discover if the target HumanResourcesExampleFolder Folder already exists.

' Look to see if the target Modeling Services Folder already 
' exists by using the [Repository.Item].[PathsFolder] function.
Console.WriteLine("Looking for the Repository/HumanResourcesFolder...")
humanResourcesFolderId = db.PathsFolder("Repository/HumanResourcesFolder")

// Look to see if the target Modeling Services Folder already 
// exists by using the [Repository.Item].[PathsFolder] function.
Console.WriteLine("Looking for the Repository/HumanResourcesFolder...");
humanResourcesFolderId = db.PathsFolder("Repository/HumanResourcesFolder");

If the target Folder does not exist, insert a new Folder into the [Repository.Item].[Folders] view. Call the [Repository.Item].[PathsFolder] function again to retrieve the identity of the new Folder.

' If the target Folder does not exist, create it.
If (humanResourcesFolderId.HasValue = False) Then
   ' First obtain the Folder for the system-defined "Repository" Folder.
   Dim folderRepository As Integer?
   folderRepository = _
      (From folder In foldersView _
      Where folder.Name = "Repository" _
      Select folder.Id).SingleOrDefault()

   If (folderRepository.HasValue) Then
      ' Create the new Folder and submit the changes.
      ' Refer to the identifier for the "Repository" Folder to make
      ' this new Folder a subFolder under Repository.
      Dim folderNew As Folder = New Folder()
      folderNew.Name = "HumanResourcesFolder"
      folderNew.ParentFolder = folderRepository

      foldersView.InsertOnSubmit(folderNew)

      db.SubmitChanges()

      ' Obtain the value of the inserted Folder
      humanResourcesFolderId = db.PathsFolder("Repository/HumanResourcesFolder")
   Else
      humanResourcesFolderId = 1
   End If
End If
Console.WriteLine("HumanResourcesExampleFolder: Id = {0}" + Environment.NewLine, humanResourcesFolderId.ToString())

// If the target Folder does not exist, create it.
if (humanResourcesFolderId.HasValue == false)
{
   // First obtain the Folder for the system-defined "Repository" Folder.
   int? folderRepository =
      (from folder in foldersView
       where folder.Name == "Repository"
       select folder.Id).SingleOrDefault();

   if (folderRepository.HasValue)
   {
      // Create the new Folder and submit the changes.
      // Refer to the identifier for the "Repository" Folder to make
      // this new Folder a subFolder under Repository.
      Folder folderNew = new Folder();
      folderNew.Name = "HumanResourcesFolder";
      folderNew.ParentFolder = (int)folderRepository;

      foldersView.InsertOnSubmit(folderNew);

      db.SubmitChanges();

      // Obtain the value of the inserted Folder
      humanResourcesFolderId = db.PathsFolder("Repository/HumanResourcesFolder");
   }
   else
      humanResourcesFolderId = 1;
}
Console.WriteLine("HumanResourcesExampleFolder: Id = {0}" + Environment.NewLine, humanResourcesFolderId.ToString());

To create a new employee in the [HumanResources].[Employees] table

Immediately following the code in the previous section, add the following code that creates a new employee row in the [HumanResources].[Employees] table.

Note

Note that this new employee instance is associated with the identifier of the previously created HumanResourcesFolder Folder.

' Create the new employee and submit the changes. Use the HumanResourcesFolder
' identifier for the Folder column of the Employee item.
Console.WriteLine("Creating new employee: Name={0}", "John")
Dim employeeNew As Employee = New Employee()
employeeNew.Name = "John"
employeeNew.Folder = humanResourcesFolderId

employeesTable.InsertOnSubmit(employeeNew)

db.SubmitChanges()

Console.WriteLine("New employee: Id = {0}", employeeNew.Id)

// Create the new employee and submit the changes. Use the HumanResourcesFolder
// identifier for the Folder column of the Employee item.
Console.WriteLine("Creating new employee: Name={0}", "John");
Employee employeeNew = new Employee();
employeeNew.Name = "John";
employeeNew.Folder = (int)humanResourcesFolderId;

employeesTable.InsertOnSubmit(employeeNew);

db.SubmitChanges();

Console.WriteLine("New employee: Id = {0}", employeeNew.Id);

To query the contents of the [HumanResources].[Employees] table

Add the following code that uses a LINQ query to list the contents of the [HumanResources].[Employees] table. You should see the new employee, "John", created in the previous step.

' Display the current list of Employees.
Console.WriteLine("Employees:")
Dim employeeQuery = _
   From employee In employeesTable _
   Select employee

For Each employee In employeeQuery
   Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder)
Next

// Display the current list of Employees.
Console.WriteLine("Employees:");
var employeeQuery =
    from employee in employeesTable
    select employee;

foreach (var employee in employeeQuery)
   Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder);

To update the [HumanResources].[Employees] table

Add the following code to change the name of the employee from "John" to "Jonathan".

' Update the [HumanResources].[Employees] table.
Console.WriteLine(Environment.NewLine + "Updating the employees name to Jonathan...")
employeeNew.Name = "Jonathan"
db.SubmitChanges()

// Update the [HumanResources].[Employees] table.
Console.WriteLine(Environment.NewLine + "Updating the employees name to Jonathan...");
employeeNew.Name = "Jonathan";
db.SubmitChanges();

Next, add code to display the contents of the table again to see the change.

' Display the current list of Employees.
Console.WriteLine("Employees:")
For Each employee In employeeQuery
   Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder)
Next

// Display the current list of Employees.
Console.WriteLine("Employees:");
foreach (var employee in employeeQuery)
   Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder);

To delete a Modeling Services Folder and its contents

Add the following code to remove the HumanResourcesExampleFolder Folder. This action also deletes the contents of the Folder, which in this case is the row in the [HumanResources].[Employees] table.

' Delete the HumanResourcesFolder Folder.
Console.WriteLine(Environment.NewLine + "Deleting HumanResourcesFolder Folder...")
Dim humanResourcesFolder As Folder
humanResourcesFolder = _
       (From folder In foldersView _
        Where folder.Id = humanResourcesFolderId _
        Select folder).Single()
foldersView.DeleteOnSubmit(humanResourcesFolder)
db.SubmitChanges()

// Delete the HumanResourcesFolder Folder.
Console.WriteLine(Environment.NewLine + "Deleting HumanResourcesFolder Folder...");
Folder humanResourcesFolder =
    (from folder in foldersView
     where folder.Id == (int)humanResourcesFolderId
     select folder).Single();
foldersView.DeleteOnSubmit(humanResourcesFolder);
db.SubmitChanges();

Next, add code to display the contents of the table again to see the change. You should not see the employee row that was previously assigned to the HumanResourcesExampleFolder Folder.

' Display the current list of Employees.
Console.WriteLine("Employees:")
For Each employee In employeeQuery
   Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder)
Next

Console.WriteLine(Environment.NewLine + "Finished. Press any key.")

// Display the current list of Employees.
Console.WriteLine("Employees:");
foreach (var employee in employeeQuery)
   Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder);

Console.WriteLine(Environment.NewLine + "Finished. Press any key.");

To test the LINQ sample

On the Build menu, click Build Solution.
On the Debug menu, click Start Without Debugging.
Observe the output in the console window.

Example

The following is the complete source code for the Main method of the RepAccess_LINQ project.

Imports System.Linq
Imports System.Data.Linq

Module Module1
   Sub Main()
      Try
         ' Create the DataContext object for the Repository. 
         Dim db As RepositoryDataContext = New RepositoryDataContext()

         ' Reference strongly typed tables for the [Repository.Item].[Folders]
         ' view and the [HumanResources].[Employees] table.
         Dim foldersView As Table(Of Folder) = db.Folders
         Dim employeesTable As Table(Of Employee) = db.Employees

         Dim humanResourcesFolderId As Long?

         ' Look to see if the target Modeling Services Folder already 
         ' exists by using the [Repository.Item].[PathsFolder] function.
         Console.WriteLine("Looking for the Repository/HumanResourcesFolder...")
         humanResourcesFolderId = db.PathsFolder("Repository/HumanResourcesFolder")

         ' If the target Folder does not exist, create it.
         If (humanResourcesFolderId.HasValue = False) Then
            ' First obtain the Folder for the system-defined "Repository" Folder.
            Dim folderRepository As Integer?
            folderRepository = _
               (From folder In foldersView _
               Where folder.Name = "Repository" _
               Select folder.Id).SingleOrDefault()

            If (folderRepository.HasValue) Then
               ' Create the new Folder and submit the changes.
               ' Refer to the identifier for the "Repository" Folder to make
               ' this new Folder a subFolder under Repository.
               Dim folderNew As Folder = New Folder()
               folderNew.Name = "HumanResourcesFolder"
               folderNew.ParentFolder = folderRepository

               foldersView.InsertOnSubmit(folderNew)

               db.SubmitChanges()

               ' Obtain the value of the inserted Folder
               humanResourcesFolderId = db.PathsFolder("Repository/HumanResourcesFolder")
            Else
               humanResourcesFolderId = 1
            End If
         End If

         Console.WriteLine("HumanResourcesExampleFolder: Id = {0}" + Environment.NewLine, humanResourcesFolderId.ToString())

         ' Create the new employee and submit the changes. Use the HumanResourcesFolder
         ' identifier for the Folder column of the Employee item.
         Console.WriteLine("Creating new employee: Name={0}", "John")
         Dim employeeNew As Employee = New Employee()
         employeeNew.Name = "John"
         employeeNew.Folder = humanResourcesFolderId

         employeesTable.InsertOnSubmit(employeeNew)

         db.SubmitChanges()

         Console.WriteLine("New employee: Id = {0}", employeeNew.Id)

         ' Display the current list of Employees.
         Console.WriteLine("Employees:")
         Dim employeeQuery = _
            From employee In employeesTable _
            Select employee

         For Each employee In employeeQuery
            Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder)
         Next

         ' Update the [HumanResources].[Employees] table.
         Console.WriteLine(Environment.NewLine + "Updating the employees name to Jonathan...")
         employeeNew.Name = "Jonathan"
         db.SubmitChanges()

         ' Display the current list of Employees.
         Console.WriteLine("Employees:")
         For Each employee In employeeQuery
            Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder)
         Next

         ' Delete the HumanResourcesFolder Folder.
         Console.WriteLine(Environment.NewLine + "Deleting HumanResourcesFolder Folder...")
         Dim humanResourcesFolder As Folder
         humanResourcesFolder = _
                (From folder In foldersView _
                 Where folder.Id = humanResourcesFolderId _
                 Select folder).Single()
         foldersView.DeleteOnSubmit(humanResourcesFolder)
         db.SubmitChanges()

         ' Display the current list of Employees.
         Console.WriteLine("Employees:")
         For Each employee In employeeQuery
            Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder)
         Next

         Console.WriteLine(Environment.NewLine + "Finished. Press any key.")

      Catch exception As Exception
         Console.WriteLine("An unexpected error occurred: {0}" & Environment.NewLine, exception.Message)
      Finally
         Console.ReadKey()
      End Try
   End Sub
End Module

using System;
using System.Linq;
using System.Data.Linq;

namespace RepAccess_LINQ
{
   class Program
   {
      static void Main(string[] args)
      {
         try
         {
            // Create the DataContext object for the Repository. 
            RepositoryDataContext db = new RepositoryDataContext();

            // Reference strongly typed tables for the [Repository.Item].[Folders]
            // view and the [HumanResources].[Employees] table.
            Table<Folder> foldersView = db.Folders;
            Table<Employee> employeesTable = db.Employees;

            long? humanResourcesFolderId;

            // Look to see if the target Modeling Services Folder already 
            // exists by using the [Repository.Item].[PathsFolder] function.
            Console.WriteLine("Looking for the Repository/HumanResourcesFolder...");
            humanResourcesFolderId = db.PathsFolder("Repository/HumanResourcesFolder");

            // If the target Folder does not exist, create it.
            if (humanResourcesFolderId.HasValue == false)
            {
               // First obtain the Folder for the system-defined "Repository" Folder.
               int? folderRepository =
                  (from folder in foldersView
                   where folder.Name == "Repository"
                   select folder.Id).SingleOrDefault();

               if (folderRepository.HasValue)
               {
                  // Create the new Folder and submit the changes.
                  // Refer to the identifier for the "Repository" Folder to make
                  // this new Folder a subFolder under Repository.
                  Folder folderNew = new Folder();
                  folderNew.Name = "HumanResourcesFolder";
                  folderNew.ParentFolder = (int)folderRepository;

                  foldersView.InsertOnSubmit(folderNew);

                  db.SubmitChanges();

                  // Obtain the value of the inserted Folder
                  humanResourcesFolderId = db.PathsFolder("Repository/HumanResourcesFolder");
               }
               else
                  humanResourcesFolderId = 1;
            }
            Console.WriteLine("HumanResourcesExampleFolder: Id = {0}" + Environment.NewLine, humanResourcesFolderId.ToString());

            // Create the new employee and submit the changes. Use the HumanResourcesFolder
            // identifier for the Folder column of the Employee item.
            Console.WriteLine("Creating new employee: Name={0}", "John");
            Employee employeeNew = new Employee();
            employeeNew.Name = "John";
            employeeNew.Folder = (int)humanResourcesFolderId;

            employeesTable.InsertOnSubmit(employeeNew);

            db.SubmitChanges();

            Console.WriteLine("New employee: Id = {0}", employeeNew.Id);

            // Display the current list of Employees.
            Console.WriteLine("Employees:");
            var employeeQuery =
                from employee in employeesTable
                select employee;

            foreach (var employee in employeeQuery)
               Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder);

            // Update the [HumanResources].[Employees] table.
            Console.WriteLine(Environment.NewLine + "Updating the employees name to Jonathan...");
            employeeNew.Name = "Jonathan";
            db.SubmitChanges();

            // Display the current list of Employees.
            Console.WriteLine("Employees:");
            foreach (var employee in employeeQuery)
               Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder);

            // Delete the HumanResourcesFolder Folder.
            Console.WriteLine(Environment.NewLine + "Deleting HumanResourcesFolder Folder...");
            Folder humanResourcesFolder =
                (from folder in foldersView
                 where folder.Id == (int)humanResourcesFolderId
                 select folder).Single();
            foldersView.DeleteOnSubmit(humanResourcesFolder);
            db.SubmitChanges();

            // Display the current list of Employees.
            Console.WriteLine("Employees:");
            foreach (var employee in employeeQuery)
               Console.WriteLine("Id = {0}, Name = {1}, Folder = {2}", employee.Id, employee.Name, employee.Folder);

            Console.WriteLine(Environment.NewLine + "Finished. Press any key.");
         }
         catch (Exception exception)
         {
            Console.WriteLine("An unexpected error occurred: {0}" + Environment.NewLine, exception.Message);
         }
         finally
         {
            Console.ReadKey();
         }
      }
   }
}

How to: Create, Read, Update, and Delete Data using LINQ

To create a connection to the Modeling Services database

To create a LINQ console application

To modify the generated Employee and Folder data classes

To override the insert behavior in LINQ

To use the generated LINQ classes

To create a new Modeling Services Folder

To create a new employee in the [HumanResources].[Employees] table

To query the contents of the [HumanResources].[Employees] table

To update the [HumanResources].[Employees] table

To delete a Modeling Services Folder and its contents

To test the LINQ sample

Example

See Also

Other Resources

Additional resources