LINQ to Oracle Tutorial

LinqConnect is a lightweight ORM solution, designed to be fully compatible with Microsoft LINQ to SQL. It supports complex types, advanced data fetching options, and configurable compiled query caching, allowing users to develop applications more quickly and easily.

LinqConnect is available both as a standalone product and as part of dotConnect, a suite of dedicated providers for popular data sources. The Professional and Developer editions of dotConnect for Oracle include full support for LinqConnect for Oracle.

This tutorial will walk you through the steps to create a simple application using LinqConnect technology.

What is LINQ?

LINQ (Language-Integrated Query) enables .NET applications to interact with databases without needing to focus on columns and rows. It automatically converts data into objects that are ready for use by your business logic.

LINQ to Relational Data acts as an object-relational mapping (ORM) tool. Type-safe LINQ queries are compiled into MSIL at runtime, with query clauses translated into SQL and executed on the Oracle server. This approach makes your data access layer more secure, faster, and much easier to design.

Prerequisites

• Visual Studio 2022: Our IDE of choice. If you do not have it on your machine, download it from the official website and install it. We will use the free Community edition.
• dotConnect for Oracle: A feature-rich ADO.NET provider with enhanced ORM support and database connectivity features.
• LinqConnect: A fast and lightweight LINQ to SQL ORM designer.

Prepare the project

We need to create a new application in Visual Studio. It could be any other project type, but we'll use a console project in the tutorial. We name it LinqOracle. If your project has a different name, make sure to substitute our demo project name in examples with the actual name from your Solution Explorer.

Build a Database First LINQ model

Professional and Developer editions of dotConnect for Oracle include Entity Developer: a professional ORM model designer and code generator. It supports both Entity Framework and LinqConnect ORM and allows generating a model from a database (or a database from a model).

Add Devart LinqConnect Model to the project: right-click the project node in Solution Explorer > Add > New Item.

Under the Data category, select Devart LinqConnect Model, and click Add.

The Entity Developer's Create Model Wizard opens automatically. This tool will create a new empty model or generate it from the database. Choose Database First and click Next.

Fill in the details to connect to your Oracle database. Click Next.

Select the database objects to use in the model. Click Next.

Specify the naming convention for the property names in the database object and click Next. This time we suggest keeping the default settings.

Now we need to provide the namespace and the DataContext name. Our namespace is OracleContext, and the DataContext name is OracleDataContext (it will be the name of the main data access class). Click Next.

Choose the code generation template for your objects. You can define different parameters for the object to follow. This tutorial uses the default settings. Click Next.

Click Finish and view the generated model.

Entity Developer generates classes for all selected tables, each representing an entity. It also creates a descendant of the OracleDataContext class to manage the database connection and data flow. This class includes properties and methods named after your database objects, which you will use to retrieve and modify data in the context.

The generated code is saved in the DataContext1.Designer.cs (or DataContext1.Designer.vb) file. You can add your own partial classes and methods in the DataContext1.cs (or DataContext1.vb) file.

Now, let us explore how to retrieve and manipulate data from the application.

Query data

The DataContext descendant executes all LINQ-to-Oracle operations. In this tutorial, it is OracleDataContext - the class that knows everything about your model and retrieves and modifies related data in the database.

All LinqConnect operations are performed within the properties and methods of this class.

As the DataContext class is lightweight and not expensive to create, we recommend creating a new DataContext instance for any 'unit of work' and disposing it after completing that unit.

Notice the following parameters:

• query, it - arbitrary variable names in the LINQ to SQL statement. query serves as the collection of data objects, while it references single entities in a collection and exists inside the statement only.
• context.EMPLOYEE - a reference to a public property of the OracleDataContext class. This property represents the collection of all actors in the context.
• EMPLOYEE (in the foreach statement) - the name of an autogenerated class. This class maps to the Company table in the database and is named after it.

Modify the Program.cs file by writing the below code to connect to the Oracle database and retrieve data from the EMPLOYEE table:

static void Main(string[] args) {
  try {
    using(var db = new OracleDataContext()) {
      Console.WriteLine("Connected to Oracle database!");

      // LINQ Query: Fetch all employees
      var employees = db.EMPLOYEEs.ToList();

      // Display data
      Console.WriteLine("Employees List:");
      foreach(var employee in employees) {
        Console.WriteLine($"{employee.ID}: {employee.NAME} ({employee.POSITION})");
      }
    }
  } catch (Exception ex) {
    Console.WriteLine($"An error occurred: {ex.Message}");
  }
}
  

The program checks if the connection to the database is open and opens the connection if it is not. The LINQ query retrieves all records from the EMPLOYEE table and prints them to the console.

Insert new data

To insert a new record into the EMPLOYEE table and display the inserted record only, modify the Program.cs file as follows:

static void Main(string[] args) {
  try {
	using(var db = new OracleDataContext()) {
  	// Create a new EMPLOYEE record
  	var newEmployee = new EMPLOYEE {
    	NAME = "John Doe",
      	POSITION = "Software Engineer"
  	};

  	// Insert the new record into the EMPLOYEE table
  	db.EMPLOYEEs.InsertOnSubmit(newEmployee);
  	db.SubmitChanges();

  	// Display the inserted record
  	Console.WriteLine("Inserted Employee:");
  	Console.WriteLine($"{newEmployee.ID}: {newEmployee.NAME} ({newEmployee.POSITION})");
	}
  } catch (Exception ex) {
	Console.WriteLine($"An error occurred: {ex.Message}");
  }
}

The InsertOnSubmit() method stores the information about all linked objects. As shown in the example, we only need to call InsertOnSubmit() once to submit both product and category objects.

Update data

To rename an employee and display their updated name and position, modify the Program.cs file as follows:

static void Main(string[] args) {
  try {
	using(var db = new OracleDataContext()) {
  	// Find the employee with the name "John Doe"
  	var employee = db.EMPLOYEEs.SingleOrDefault(e => e.NAME == "John Doe");

  	if (employee != null) {
    	// Update the employee's name
    	employee.NAME = "Jane Doe";

    	// Submit the changes to the database
    	db.SubmitChanges();

    	// Display the updated record
    	Console.WriteLine("Updated Employee:");
    	Console.WriteLine($"{employee.ID}: {employee.NAME} ({employee.POSITION})");
  	} else {
    	Console.WriteLine("Employee 'John Doe' not found.");
  	}
	}
  } catch (Exception ex) {
	Console.WriteLine($"An error occurred: {ex.Message}");
  }
}

This code changes the employee's name from 'John Doe' to 'Jane Doe' and displays the updated details:

Delete data

To delete an employee with a specific ID (here it is ID 42) and display the status, modify the Program.cs file as follows:

static void Main(string[] args) {
  try {
	using(var db = new OracleDataContext()) {
  	// Find the employee with the ID 42
  	var employee = db.EMPLOYEEs.SingleOrDefault(e => e.ID == 42);

  	if (employee != null) {
    	// Delete the employee
    	db.EMPLOYEEs.DeleteOnSubmit(employee);
    	db.SubmitChanges();

    	// Display the deletion status
    	Console.WriteLine("Employee with ID 42 has been deleted.");
  	} else {
    	Console.WriteLine("Employee with ID 42 not found.");
  	}
	}
  } catch (Exception ex) {
	Console.WriteLine($"An error occurred: {ex.Message}");
  }
}

The program uses a LINQ query to search for the employee with ID 42. When found, the DeleteOnSubmit method marks the employee for deletion. Then, the SubmitChanges method saves the deletion to the database. Finally, the program prints a message indicating whether the employee was successfully deleted or not found.

Additional information

Now that you can perform the basic data manipulation with LinqConnect, you can move on to more advanced topics. More information is available in the LinqConnect Documentation.

To understand the work of the LinqConnect engine better, you can refer to the generated SQL statements in dbMonitor or use the DataContext.Log.

Conclusion

This tutorial demonstrated how to create a data access layer for an Oracle database and work with its data via LINQ, using either dotConnect for Oracle or LinqConnect products.

Both solutions offer fully functional trials, allowing you to assess their performance with your actual workload and evaluate how they can support your application development.