LINQ to PostgreSQL Tutorial

LinqConnect (formerly known as LINQ to PostgreSQL) is a fast, lightweight ORM solution, closely compatible to Microsoft LINQ to SQL, with advanced features like complex type support, advanced data fetching options, configurable compiled query caching, and more. This functionality allows the users to create applications much faster and with ease.

LinqConnect is available as a separate product or as a part of dotConnect providers. Dedicated providers are available for all popular data sources, including PostgreSQL. The Professional and Developer editions of dotConnect for PostgreSQL come with the LinqConnect support.

This tutorial will guide you through the process of creating a simple application powered by LinqConnect with the help of dotConnect for PostgreSQL.

What is LINQ?

LINQ stands for Language-Integrated Query. The LINQ engine allows .NET applications to connect to databases without worries about columns and rows, as the business logic automatically forms the received data as objects ready to use immediately.

LINQ to Relational Data is an object-relational mapping (ORM) tool. The type-safe LINQ queries get compiled into MSIL on the fly, and the query clauses get translated into SQL and sent to the PostgreSQL server for execution. As a result, you have a safer data access layer safer, and can design it faster and in a more convenient manner.

Requirements

The following prerequisites are necessary to follow this guide:

• Visual Studio 2022: The IDE we use while working on our tutorial. If you do not have this IDE on your machine, download it from the official website and install it. We will use the free Community Edition.
• dotConnect for PostgreSQL: A feature-rich ADO.NET provider for PostgreSQL with enhanced ORM support and database connectivity features.
• LinqConnect: A fast and lightweight LINQ to SQL ORM designer.
• DvdRental database: The sample database for learning and testing from the official PostgreSQL documentation.

Prepare the project

To start, we need a demo project. Create a new console application in Visual Studio (it could be any other project type, but we'll use the console project throughout the tutorial for simplicity). We name our demo project LinqPgSql. If you prefer a different name for your project, make sure to substitute LinqPgSql with the actual project's name in Solution Explorer.

Build a Database First LINQ model

Add Devart LinqConnect Model to the project. To do this, right-click the project in Solution Explorer, point to Add > New Item.

In the Add New Item dialog, select Data, then Devart LinqConnect Model, and click Add.

The Create Model Wizard opens automatically. It creates a new empty model or generates it from the database. Choose the Database First approach and click Next on the welcome screen.

Fill in the details of your database connection and click Next.

Select the database objects to use in the model. These are all objects from our DvdRental database, including auxiliary tables. Click Next.

Define the naming convention for the property names in the database object and click Next. We keep the default settings this time.

Input DvdrentalContext as the namespace and DvdrentalDataContext as the name of the DataContext descendant. It will be the name of the main data access class. Click Next.

Choose the code generation templates for your objects. You can define different parameters you want the object to follow. Here we keep the default settings. Click Next.

Click Finish. The model will be generated and opened in Entity Developer.

Entity Developer creates classes for all selected tables that represent entities. It also creates a descendant of DvdrentalDataContext class that controls the connection to the database and the whole data flow. This class includes properties and methods named after your database objects. You will use these members to retrieve and modify data in the context.

The generated code is in the file DataContext1.Designer.cs (DataContext1.Designer.vb). You may write your partial classes and methods in the file DataContext1.cs (DataContext1.vb).

Query data

All LINQ to PostgreSQL operations are executed through the DataContext descendant. It this tutorial, it is DvdrentalDataContext.

To retrieve data, you need to create an instance of the context, then prepare a query with LinqConnect, and access the object returned by the query. It may be a collection of objects or a single object. Our test case illustrates reading all data from the Customer table. We also need to output some columns.

Add the following block of code to the main method:

using (var context = new DvdrentalDataContext())
{
	try
	{
    	// Attempt to connect to the database
    	context.Connection.Open();
    	Console.WriteLine("Connection successful.");

    	// Query the Customer table and take only 10 records
    	var customers = (from customer in context.Customers
                     	select customer).Take(10);

    	// Display the records
    	foreach (var customer in customers)
    	{
        	Console.WriteLine($"Customer ID: {customer.CustomerId}, Name: {customer.FirstName} {customer.LastName}");
    	}
	}
	catch (Exception ex)
	{
    	// Handle connection or query errors
    	Console.WriteLine($"An error occurred: {ex.Message}");
	}
}

This way, you prepare a query and then iterate through it as you would with a usual collection of objects. LinqConnect performs the database interaction in the background. CrmDemoDataContext is the name of the class that knows all about your model and does everything to retrieve and modify related data in the database.

The properties and methods of this class perform all LinqConnect operations. This class is designed to be lightweight and not expensive to create, therefore, you can create a new DataContext instance for any 'unit of work' and dispose it after this unit is completed.

Note the below aspects:

• query, it are arbitrary variable names in the LINQ to SQL statement. query is used as the collection of data objects, while it is used to reference single entities in a collection, existing inside the statement only.
• Customer refers to a public property of the DvdrentalDataContext class. This property represents the collection of all companies in the context.
• Customer (in the foreach statement) is the name of the autogenerated class. This class maps to the Customer table in the database. Also, it is named after it.

Have a look at the output in the console:

Let us query data from two tables united with a foreign key. Replace the old code with the below block:

using (var context = new DvdrentalDataContext())
{
	try
	{
    // Attempt to connect to the database
    context.Connection.Open();
    Console.WriteLine("Connection successful.");

    // Query the Country and City tables with a join on CountryId
    var countryCityData = from city in context.Cities
         join country in context.Countries
         on city.CountryId equals country.CountryId
         select new
            {
				CityName = city.City1, // Adjust to the correct property name
                CountryName = country.Country1 // Adjust to the correct property name
                };

    // Display the records
    foreach (var item in countryCityData)
    {
       	Console.WriteLine($"City: {item.CityName}, Country: {item.CountryName}");
    }
	}
	catch (Exception ex)
	{
    	// Handle connection or query errors
    	Console.WriteLine($"An error occurred: {ex.Message}");
	}
}

As you can see, no changes took place in the LINQ query statement. It retrieved the data about the contact persons from the database automatically when we accessed the corresponding property of the object. LINQ eliminates any concerns about dependencies during the queries construction.

Insert new data

Earlier, we demonstrated adding rows to tables as adding new objects to context collections. This action did not impact the actual database. To send the changes to the database, we need to call the SubmitChanges() method of the context.

Before calling the necessary method, set all properties that do not support null (Nothing) values. The SubmitChanges() method generates and executes commands that perform the equivalent INSERT, UPDATE, or DELETE statements against the data source.

In our test case, we need to add a new record to the Staff table. Replace the old code with the following code block:

using (var context = new DvdrentalDataContext())
{
	try
	{
    	// Attempt to connect to the database
    	context.Connection.Open();
    	Console.WriteLine("Connection successful.");

    	// Create a new Staff object
    	var newStaff = new Staff
    	{
        	FirstName = "Jane",
        	LastName = "Smith",
        	AddressId = 1, // Ensure this is a valid AddressId
        	Email = "jane.smith@example.com",
        	StoreId = 1,   // Ensure this is a valid StoreId
        	Active = true,
        	Username = "jsmith",
        	Password = "securepassword", // Consider hashing the password
        	Picture = null, // Assuming Picture is nullable
        	LastUpdate = DateTime.Now
    	};

    	// Add the new staff to the context
    	context.Staffs.InsertOnSubmit(newStaff);

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

    	// Display the inserted record details
    	Console.WriteLine($"New staff record inserted successfully:\nFirst Name: {newStaff.FirstName}\nLast Name: {newStaff.LastName}\nEmail: {newStaff.Email}");
	}
	catch (Exception ex)
	{
    	// Handle connection or insertion errors
    	Console.WriteLine($"An error occurred: {ex.Message}");
    	if (ex.InnerException != null)
    	{
        	Console.WriteLine($"Inner exception: {ex.InnerException.Message}");
    	}
	}
}

The InsertOnSubmit() method is created for every collection in the context. This method stores the information about all linked objects in the database. As shown in the example, we only need to call InsertOnSubmit() once to submit both product and category objects.

Update data

Entity instances are modified as usual. The only thing to remember is that you have to invoke the SubmitChanges() method to send the data to the database.

Append the following block to the existing code and launch the project:

using (var context = new DvdrentalDataContext())
{
	try
	{
    	// Attempt to connect to the database
    	context.Connection.Open();
    	Console.WriteLine("Connection successful.");

    	// Retrieve the staff record to update
    	// Assuming you know the StaffId of the record you want to update
    	int staffIdToUpdate = 1; // Replace with the actual StaffId
    	var staffToUpdate = context.Staffs.SingleOrDefault(s => s.StaffId == staffIdToUpdate);

    	if (staffToUpdate != null)
    	{
        	// Update the FirstName and LastName
        	staffToUpdate.FirstName = "NewFirstName";
        	staffToUpdate.LastName = "NewLastName";

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

        	// Display the updated record details
        	Console.WriteLine($"Staff record updated successfully:\nFirst Name: {staffToUpdate.FirstName}\nLast Name: {staffToUpdate.LastName}\nEmail: {staffToUpdate.Email}");
    	}
    	else
    	{
        	Console.WriteLine("Staff record not found.");
    	}
	}
	catch (Exception ex)
	{
    	// Handle connection or update errors
    	Console.WriteLine($"An error occurred: {ex.Message}");
    	if (ex.InnerException != null)
    	{
        	Console.WriteLine($"Inner exception: {ex.InnerException.Message}");
    	}
	}
}

See the results:

Delete data

To remove an instance from a context, we use the DeleteOnSubmit method of the corresponding collection. It removes the object from the collection of its type, but it does not destroy it. To delete the object's data from the database, invoke the SubmitChanges() method.

Use the below block of code:

using (var context = new DvdrentalDataContext())
{
	try
	{
    // Find a payment record to delete
    var paymentToDelete = context.Payments
        .OrderBy(p => p.PaymentId)
        .FirstOrDefault();

    if (paymentToDelete != null)
    {
    int paymentId = paymentToDelete.PaymentId;
    Console.WriteLine($"Found payment with ID {paymentId} to delete");

    // Delete the payment using LINQ
    context.Payments.DeleteOnSubmit(paymentToDelete);
    context.SubmitChanges();

    Console.WriteLine($"Successfully deleted payment with ID {paymentId}");
 }
   else
   {
   Console.WriteLine("No payment records found to delete");

   // Alternative: Try to delete from film_category junction table
   var filmCategoryToDelete = context.FilmCategories
   .FirstOrDefault();

   if (filmCategoryToDelete != null)
   {
   int filmId = filmCategoryToDelete.FilmId;
   int categoryId = filmCategoryToDelete.CategoryId;

   Console.WriteLine($"Found film_category entry: Film ID {filmId}, Category ID {categoryId}");

   context.FilmCategories.DeleteOnSubmit(filmCategoryToDelete);
   context.SubmitChanges();

   Console.WriteLine($"Successfully deleted film_category entry for Film ID {filmId}, Category ID {categoryId}");
   }
   else
   {
   Console.WriteLine("No records found to delete");
  }
  }
}
catch (Exception ex)
{
    Console.WriteLine($"An error occurred: {ex.Message}");
    if (ex.InnerException != null)
    {
    Console.WriteLine($"Inner exception: {ex.InnerException.Message}");
    }
  }
}

Have a look at the results:

Additional information

Now that you can perform the basic data manipulation with LinqConnect, you can move on to some advanced topics. dotConnect for PostgreSQL includes a help section dedicated to the LinqConnect technology. You can access it online at LinqConnect Documentation.

To understand deeper the works of LinqConnect engine you can watch the generated SQL statements in dbMonitor or using the DataContext.Log property.

Conclusion

This tutorial demonstrated how to easily create a data access layer for a PostgreSQL database and work with its data via LINQ, using either dotConnect for PostgreSQL or LinqConnect products. LinqConnect is a lightweight, LINQ to SQL-compatible ORM solution with support for PostgreSQL, MySQL, Oracle, and SQLite. dotConnect for PostgreSQL is an ADO.NET provider from Devart with support for such ORM solutions as Entity Framework v1-v6, Entity Framework Core, NHibernate, and LinqConnect.