LINQ to SQLite Tutorial

LinqConnect (formerly known as LINQ to SQLite) is a fast and lightweight ORM solution compatible with Microsoft LINQ to SQL. It contains advanced features, such as complex type support, advanced data fetching options, configurable compiled query caching, and others.

LinqConnect is available as a separate product or as a part of Devart dotConnect providers for the corresponding databases. dotConnect for SQLite is one of such providers, and its Professional edition includes LinqConnect support for SQLite.

This tutorial guides you through the process of creating a simple application powered by LinqConnect.

What is LINQ?

LINQ stands for Language-Integrated Query, which means 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. Your business logic automatically forms the data you receive as objects ready to use.

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 are translated into SQL and sent to the SQLite database for execution. It makes your data access layer safer, faster, and more convenient to design.

Requirements

What you need for this tutorial:

• 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 SQLite: A feature-rich ADO.NET provider with Entity Framework, NHibernate, and LinqConnect support.
• 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 in Visual Studio. It could be any project type, but we'll use the console project throughout the tutorial for simplicity.

We have created the test project named LinqSqlite. If your project has a different name, substitute LinqSqlite with the actual name in Solution Explorer when you follow this tutorial.

Build a Database First LINQ model

The Professional edition of dotConnect for SQLite includes Entity Developer - an ORM model designer and code generator supporting Entity Framework and LinqConnect ORM. It allows you to generate a model from a database (or a database from a 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 Entity Developer's 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 database objects to use in the model. These are 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 this time.

Input SakilaContext as the namespace and SakilaDataContext 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. Let's use the default settings for this tutorial. 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 the Devart.Data.Linq.DataContext 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 SQLite operations are executed through the DataContext descendant named CrmDemoDataContext in this tutorial.

To retrieve data, you must first create an instance of the context, then prepare a query with LinqConnect, and then access the object returned by the query, which may be a collection of objects or a single object.

We need to read all data from the Company table, sort it by CompanyID, and output several columns.

Add the following code block to the main method:

using (var context = new SakilaDataContext())
{
// Check if the connection can be opened
context.Connection.Open();
Console.WriteLine("Connection to the database established successfully.");

// Query the first 10 records from the Actors table
var actors = context.Actors
     .Take(10)
     .ToList();

// Display the records
if (actors.Any())
{
foreach (var actor in actors)
{
Console.WriteLine($"ActorID: {actor.ActorId}, FirstName: {actor.FirstName}, LastName: {actor.LastName}");
}
}
else
{
Console.WriteLine("No records found in the Actors table.");
}
}

We prepare a query and then iterate through it as with a usual object collection. LinqConnect performs the database interaction in the background. Now, let's examine 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 which are designed to be lightweight and not expensive to create. We recommend creating a new DataContext instance for any 'unit of work'. After completing this unit, we can dispose it.

• Query are arbitrary variable names in the LINQ to SQL statement. The former collects data objects, and the latter references single entities in a collection that exist inside the statement only.
• context.Actor refers to a public property of the SakilaDataContext class. It represents the collection of all companies in the context.
• Actor (in the foreach statement) is the name of an autogenerated class that maps to the Actor table and is named after that table.

Here is the project's output in the console:

Let's query data from two tables united by a foreign key. Let's assume you have two tables: Actors and FilmActor, where FilmActor has a foreign key referencing Actors.

• Actors: Contains actor details. Columns: ActorId, FirstName, LastName.
• FilmActor: Contains the relationship between actors and films. Columns: ActorId (foreign key), FilmId.

Replace the old code with the following block:

using (var context = new SakilaDataContext())
{
	// Check if the connection can be opened
	context.Connection.Open();
	Console.WriteLine("Connection to the database established successfully.");

	// Query actors and their associated films
	var actorFilms = from actor in context.Actors
         join filmActor in context.FilmActors on actor.ActorId equals filmActor.ActorId
         select new
            {
            ActorId = actor.ActorId,
            FirstName = actor.FirstName,
            LastName = actor.LastName,
            FilmId = filmActor.FilmId
            };

	// Display the records
	foreach (var actorFilm in actorFilms)
	{
    	Console.WriteLine($"ActorID: {actorFilm.ActorId}, Name: {actorFilm.FirstName} {actorFilm.LastName}, FilmID: {actorFilm.FilmId}");
	}
}

The output is:

Insert new data

Adding rows to tables is just adding new objects to context collections. When you are ready to send changes to the database, call the SubmitChanges() method of the context.

Before doing this, you must 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.

To insert a new record into the Actors table and display its details, replace the old code with the following block:

using (var context = new SakilaDataContext())
{
	// Check if the connection can be opened
	context.Connection.Open();
	Console.WriteLine("Connection to the database established successfully.");

	// Create a new Actor object
	var newActor = new Actor
	{
    	FirstName = "John",
    	LastName = "Doe"
	};

	// Insert the new actor into the Actors table
	context.Actors.InsertOnSubmit(newActor);

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

	Console.WriteLine("New actor inserted successfully.");

	// Query the newly inserted actor's details
	var insertedActor = context.Actors
             .Where(a => a.ActorId == newActor.ActorId)
             .Select(a => new
             	{
             	a.ActorId,
             	a.FirstName,
             	a.LastName
             	})
             .FirstOrDefault();

	// Display the details of the newly inserted actor
	if (insertedActor != null)
	{
    	Console.WriteLine($"Inserted Actor Details - ActorID: {insertedActor.ActorId}, Name: {insertedActor.FirstName} {insertedActor.LastName}");
	}
	else
	{
    	Console.WriteLine("Failed to retrieve the newly inserted actor's details.");
	}
}

The InsertOnSubmit() method is created for every collection in the context. It stores information about all linked objects in the database. As shown in the example, you 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 SakilaDataContext())
{
// Check if the connection can be opened
context.Connection.Open();
Console.WriteLine("Connection to the database established successfully.");

// Create a new Actor object
var newActor = new Actor
{
   	FirstName = "John",
   	LastName = "Doe"
};

// Insert the new actor into the Actors table
context.Actors.InsertOnSubmit(newActor);
context.SubmitChanges();

Console.WriteLine("New actor inserted successfully.");

// Update the actor's details
newActor.FirstName = "Jane";
newActor.LastName = "Smith";

// Submit the changes to update the actor's details in the database
context.SubmitChanges();

Console.WriteLine("Actor's details updated successfully.");

// Query the updated actor's details
var updatedActor = context.Actors
     .Where(a => a.ActorId == newActor.ActorId)
     .Select(a => new
     {
     a.ActorId,
     a.FirstName,
     a.LastName
     })
     .FirstOrDefault();

// Display the updated details of the actor
if (updatedActor != null)
{
    Console.WriteLine($"Updated Actor Details - ActorID: {updatedActor.ActorId}, Name: {updatedActor.FirstName} {updatedActor.LastName}");
}
else
{
    Console.WriteLine("Failed to retrieve the updated actor's details.");
}
}

You can see that the data is successfully updated.

Delete data

To extract an instance from a context, use the DeleteOnSubmit method of the corresponding collection. The object is removed from the collection of its type but not destroyed.

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

using (var context = new SakilaDataContext())
{
	// Check if the connection can be opened
	context.Connection.Open();
	Console.WriteLine("Connection to the database established successfully.");

	// Define the ActorID of the record to be deleted
	int actorIdToDelete = 201;

	// Retrieve the actor with the specified ActorID
	var actorToDelete = context.Actors
                          	.FirstOrDefault(a => a.ActorId == actorIdToDelete);

	if (actorToDelete != null)
	{
    	// Delete the actor's record
    	context.Actors.DeleteOnSubmit(actorToDelete);
    	context.SubmitChanges();

    	Console.WriteLine($"Actor with ActorID {actorIdToDelete} has been successfully deleted.");
	}
	else
	{
    	Console.WriteLine($"No actor found with ActorID {actorIdToDelete}.");
	}

	// Verify the deletion by attempting to retrieve the actor's details
	var deletedActor = context.Actors
                          	.FirstOrDefault(a => a.ActorId == actorIdToDelete);

	// Display the result of the deletion
	if (deletedActor == null)
	{
    	Console.WriteLine("Actor's record has been successfully deleted.");
	}
	else
	{
    	Console.WriteLine("Failed to delete the actor's record.");
	}
}

You can see that the record has been deleted:

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 essential.

Additional information

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

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

Conclusion

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