LINQ to MySQL Tutorial

LinqConnect (formerly known as LINQ to MySQL) is a fast, lightweight ORM solution, closely compatible to Microsoft LINQ to SQL. It contains such advanced features as complex type support, advanced data fetching options, configurable compiled query caching, and more, allowing the users to create applications easily and much faster.

LinqConnect is available as a separate product or as a part of dotConnect providers. There are dedicated providers for all popular data sources, and dotConnect for MySQL is one of these providers. The Professional and Developer editions of dotConnect for MySQL come with the LinqConnect support.

This tutorial will guide you through the process of creating a simple application powered by the LinqConnect technology.

What is LINQ?

LINQ stands for Language-Integrated Query, meaning that data retrieval is no longer a separate language. The LINQ engine allows .NET applications to connect to databases without worries about columns and rows. The data is automatically formed as objects ready to be used by your business logic.

LINQ to Relational Data becomes a kind of an object-relational mapping (ORM) tool. The type-safe LINQ queries get compiled into MSIL on the fly. The query clauses are translated into SQL and sent to the MySQL server for execution. The data access layer is safer, faster, and more convenient to design.

Requirements

Before we proceed, make sure the following prerequisites are in place:

• Visual Studio 2022: The IDE we use to illustrate 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 MySQL: A feature-rich ADO.NET provider for MySQL with enhanced ORM support and database connectivity features.
• LinqConnect: A fast and lightweight LINQ to SQL ORM designer.
• Sakila database: A popular sample database for learning and testing.

Prepare the project

Create a new console application project in Visual Studio. It could be any other project type as well, but we'll use console project throughout the tutorial for simplicity.

The name of our demo project is LinqMySql. If your project name is different, substitute LinqMySql with the actual one in Solution Explorer.

Build a Database First LINQ model

The Professional and Developer editions of dotConnect for MySQL include Entity Developer - an ORM model designer and code generator with Entity Framework and LinqConnect ORM support. With it, we can generate a model from a database (or a database from a model) easily.

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

In the Add New Item dialog, select Data > Devart LinqConnect Model. Click Add.

This automatically launches the Create Model Wizard. Choose Database First and click Next.

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

In the Database First mode, we can choose model contents: either generate the model from a database or create an empty model as a starting point for designing visually. In our tutorial, we choose to generate a model from a database.

Select database objects for the model. We get all objects from the Sakila 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.

Enter SakilaContext as namespace, and SakilaDataContext as the name of DataContext descendant. This will be the name of the main data access class. Click Next.

Choose code generation templates for your objects. You can define different parameters you want the object to follow. Let's use the default settings for this tutorial. Click Next.

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

The model is ready to use.

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

The generated code is saved 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 MySQL operations are executed through the DataContext descendant, which we named SakilaDataContext in this tutorial. To retrieve data, first create an instance of the context, then prepare a query with LinqConnect, and access the object returned by the query. It can be a collection of objects or a single object.

We want to read all data from the Actors table and output some columns. Add the following block of code to the main method:

using (SakilaDataContext db = new SakilaDataContext())
{
	Console.WriteLine("Connected to MySQL database!");

	// LINQ Query: Fetch first 10 actors
	var actors = db.Actors.Take(10).ToList();

	// Display data
	Console.WriteLine("Actors List:");
	foreach (var actor in actors)
	{
    	Console.WriteLine($"{actor.ActorId}: {actor.FirstName} {actor.LastName}");
	}
}

LinqConnect performs the database interaction in the background.

In this code sample, SakilaDataContext is the name of the class that knows all about the model and does everything to retrieve and modify related data in the database.

All LinqConnect operations are performed within this class's properties and methods. This class is lightweight and not expensive to create, threrfore, we recommend creating a new DataContext instance for any 'unit of work' and dispose it after this unit is completed.

• query, it are arbitrary variable names in the LINQ to SQL statement. The former is for the collection of data objects, the latter references single entities in a collection. It exists inside the statement only.
• context.Actors refers to a public property of the SakilaDataContext class. This property represents the collection of all actors in the context.
• Actor (in the foreach statement) is the name of an autogenerated class. This class maps to the Company table in the database and is named after it.

Here is the project's output in the console:

Now let's query data from two tables united with a foreign key.

using (SakilaDataContext db = new SakilaDataContext())
{
	Console.WriteLine("Connected to MySQL (Sakila) database!");

	// LINQ Query: Fetch the last 5 rentals for each customer
	var customerRentals = from c in db.Customers
                      	from r in db.Rentals
                      	where c.CustomerId == r.CustomerId
                      	orderby r.RentalDate descending
                      	select new
                      	{
                          	CustomerName = c.FirstName + " " + c.LastName,
                          	RentalDate = r.RentalDate
                      	};

	// Display results (limit to 10 for testing)
	Console.WriteLine("Customers and Their Last Rentals:");
	foreach (var item in customerRentals.Take(10))
	{
    	Console.WriteLine($"{item.CustomerName} rented on {item.RentalDate}");
	}
}

As you can see, the LINQ query statement was not changed at all. We retrieved the data about the customers from the database automatically when we accessed the corresponding property of the Company object. LINQ eliminates worries about dependencies when writing queries.

See the results below:

Insert new data

When we added rows to tables, we did not update the data. We only added new objects to context collections. To send the changes to the database, we need to call the SubmitChanges() method of the context.

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

Let's add a new actor entity to the database. To do it, replace the old code with the below block:

using (SakilaDataContext db = new SakilaDataContext())
{
	Console.WriteLine("Connected to MySQL (Sakila) database!");

	// Create a new Actor record
	Actor newActor = new Actor
	{
    	FirstName = "Tom",
    	LastName = "Holland",
    	LastUpdate = DateTime.Now // Required field in Sakila
	};

	// Insert the new actor into the database
	db.Actors.InsertOnSubmit(newActor);
	db.SubmitChanges(); // Commit the transaction

	Console.WriteLine($"✅ New actor added: {newActor.FirstName} {newActor.LastName}, ID: {newActor.ActorId}");
}

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

It is essential to note that after adding a new product and category by submitting changes, you cannot execute this solution again as is. To execute the solution, change the IDs of the objects to be added.

Update data

We modify entity instances as usual. The only thing to remember is that we 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 (SakilaDataContext db = new SakilaDataContext())
{
	Console.WriteLine("Connected to MySQL (Sakila) database!");

	// Find the actor "Tom Holland"
	var actorToUpdate = db.Actors.FirstOrDefault(a => a.FirstName == "Tom" && a.LastName == "Holland");

	if (actorToUpdate != null)
	{
    	// Update the actor's details
    	actorToUpdate.FirstName = "Mike";
    	actorToUpdate.LastName = "Johnson";
    	actorToUpdate.LastUpdate = DateTime.Now; // Update the timestamp

    	// Save changes
    	db.SubmitChanges();

    	Console.WriteLine($"✅ Actor updated: {actorToUpdate.ActorId} -> {actorToUpdate.FirstName} {actorToUpdate.LastName}");
	}
	else
	{
    	Console.WriteLine("❌ Actor 'Tom Holland' not found.");
	}
}

See the result:

Delete data

To extract an instance from a context, use the DeleteOnSubmit method of the corresponding collection. We remove the object from the collection of its type, but we will not destroy it.

To delete the object's data from the database, invoke the SubmitChanges() method. You can do this with a block of code like the following:

using (SakilaDataContext db = new SakilaDataContext())
{
	Console.WriteLine("Connected to MySQL (Sakila) database!");

	// Find the actor by ID (actor_id = 202)
	var actorToDelete = db.Actors.FirstOrDefault(a => a.ActorId == 202);

	if (actorToDelete != null)
	{
    	// Delete the actor
    	db.Actors.DeleteOnSubmit(actorToDelete);
    	db.SubmitChanges(); // Commit the deletion

    	Console.WriteLine($"✅ Actor with ID {actorToDelete.ActorId} deleted successfully.");
	}
	else
	{
    	Console.WriteLine("❌ Actor with ID 202 not found.");
	}
}

Deletion of objects is affected by attributes in the model:

• When the DeleteRule parameter is Cascade, dependent objects are deleted automatically.
• When this parameter is SetNull, dependent objects are not deleted, but the relation is nullified.

When no rule is specified, the order of deletion sequence is important.

See the result below:

Additional information

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

To better understand how the LinqConnect engine works, you can watch the generated SQL statements in dbMonitor or use the DataContext.Log property.

Conclusion

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