LINQ to SQLite Tutorial

This tutorial guides you through the process of creating a simple application powered by LINQ to SQL technology. In less than 5 minutes you will have a ready-to-use data access layer for your business objects.

In this walkthrough:

• Introducing the LINQ to SQL Technology
• Requirements
• Preparing the Project
• Generating Model from Database
• Querying Data
• Inserting New Data
• Updating Data
• Deleting Data
• Additional Information

Introducing the LINQ to SQL Technology

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 bothering much about columns and rows. The data you receive is automatically formed as objects ready to use by your business logic.

LINQ to Relational Data may be thought of as 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 SQLite database for execution. This makes your data access layer safer, faster, and greatly more convenient to design.

Requirements

In this tutorial it is assumed that you already have the database objects created. You have to execute a script from the following file installed by default to
\Program Files\Devart\dotConnect\SQLite\Samples\crm_demo.sql

Preparing the Project

Create a new console application in Visual Studio. It could be any other project type as well, but for simplicity's sake we'll use console project throughout the tutorial. The rest of the tutorial assumes that the name of the project is ConsoleApplication1. If you project is named otherwise, you will have to substitute this name with the actual one in Solution Explorer.

Generating Model from Database

1. Add Devart LINQ to SQL Model to the project. To do this, right-click on the project node in Solution Explorer, point to Add, click New Item.... In the Add New Item dialog select Data category, choose Devart LINQ to SQL Model template, and click Add. This automatically launches Entity Developer, which edits and saves the model in the project.
   
2. In the main menu of Entity Developer, choose File | Create From Database if you use Enity Developer as a stand-alone application. This launches Database Reverse Engineering Wizard. To start it in Visual Studio add-in mode use Create from database button on the Entity Developer Project toolbar.
3. Click Next on the welcome screen. As this instance of Entity Developer is invoked from Visual Studio for a LINQ to SQL model, the framework selector is disabled in this screen.
4. Fill in connection settings and click Next.
   
5. Choose database objects that will be used in the model. These are all objects from the crm_demo script, including auxiliary tables. Click Next.
   
6. On the next screen you can adjust naming rules for entities and their members. For the CRM Demo database no rules are required, so just click Next.
   
7. Input CrmDemoData as namespace, and CrmDemoDataContext as the name of DataContext descendant. This will be the name of the main data access class. Click Next.
   
8. Press Finish. The model will be generated and opened in EDM Designer.
9. In the main menu, click File | Save. This updates the DataContext1 model in Visual Studio.

The model you've just generated is ready to use. You can inspect it visually in Entity Developer with diagram and toolwindows, edit various aspects of classes and relationships, add new model elements. Remember that you have save the project to make the changes in Entity Developer reflected in Visual Studio.

The wizard creates classes for all selected tables that represent entities. It also creates a descendant of System.Data.Linq.DataContext class, which 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 code is contained in the file DataContext.cs (DataContext.vb).

Querying Data

All LINQ to SQLite operations are executed through the DataContext descendant, which is named CrmDemoDataContext in this tutorial. To retrieve data you have to first create an instance of the context, then prepare a query with LINQ to SQL, and then access the object returned by the query, which may be a collection of objects or a single object.

Let's read all the data from the table Company, sort it by CompanyID, and output some columns. Add the following block of code to the method Main:

CrmDemoDataContext context = new CrmDemoDataContext();
var query = from it in context.Companies
            orderby it.CompanyID
            select it;

foreach (Company comp in query)
  Console.WriteLine("{0} | {1} | {2}", comp.CompanyID, comp.CompanyName, comp.Country);

Console.ReadLine();

Dim context As CrmDemoDataContext = New CrmDemoDataContext
Dim query = From it In context.companies _
    Order By it.CompanyID _
    Select it

Dim comp As company
For Each comp In query
    Console.WriteLine("{0} | {1} | {2}", comp.CompanyID, comp.CompanyName, comp.Country)
Next

Console.ReadLine()

As simple as that. You prepare a query and then iterate through it as you would do with a usual collection of objects. The database interaction is performed by LINQ to SQLite in the background. Now let's see who is who in this code sample.

• CrmDemoDataContext is the name of the class that knows all about your model and does everything to handle it. All LINQ to SQLite operations are performed within this class's properties and methods. It is recommended that you keep a single instance of the class throughout your application because it consumes lots of resources, and entities are not allowed to be shared through different data contexts.
• query, it are arbitrary variable names in the LINQ to SQL statement. The former is used as the collection of data objects, the latter is not used outside the statement.
• context.Companies refers to a public property of CrmDemoDataContext class. This property represents the collection of all companies in the context.
• Company (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:

Note that the LINQ to SQL query code just describes the query. It does not execute it. This approach is known as deferred execution.

Now let's query data from two tables united with a foreign key. Replace the old code with this:

CrmDemoDataContext context = new CrmDemoDataContext();
var query = from it in context.Companies
            orderby it.CompanyID
            select it;

foreach (Company comp in query) {
  if (comp.PersonContacts.Count > 0) {
    Console.WriteLine("{0} | {1} | {2}",
      comp.CompanyName, comp.PersonContacts[0].FirstName,
      comp.PersonContacts[0].LastName);
  }
}

Console.ReadLine();

Dim context As CrmDemoDataContext = New CrmDemoDataContext
Dim query = From it In context.companies _
    Order By it.CompanyID _
    Select it

Dim comp As company
For Each comp In query
    If comp.personcontacts.Count > 0 Then
        Console.WriteLine("{0} | {1} | {2}", _
              comp.CompanyName, comp.personcontacts(0).FirstName, _
              comp.personcontacts(0).LastName)
    End If
Next

Console.ReadLine()

As you can see, the LINQ to SQL query statement was not changed at all. The data about the contact persons was retrieved from the database automatically when you accessed the corresponding property of the company object. This is one of the great things about LINQ to SQL: you do not have to worry about dependencies when writing queries.

Inserting New Data

What earlier was adding rows to tables, now is just adding new objects to context collections. When you are ready to send the changes to the database, call SubmitChanges() method of the context. Before doing this, you must first 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.

Let's add a new product and a new category to the database. Replace the old code with this:

CrmDemoDataContext context = new CrmDemoDataContext();

// Create a new category
ProductCategory newCategory = new ProductCategory();
newCategory.CategoryID = 1000;
newCategory.CategoryName = "New category";

// Create a new product
Product newProduct = new Product();
newProduct.ProductID = 2000;
newProduct.ProductName = "New product";
newProduct.Price = 20;

// Associate the new product with the new category
newProduct.ProductCategory = newCategory;
context.Products.InsertOnSubmit(newProduct);

// Send the changes to the database.
// Until you do it, the changes are cached on the client side.
context.SubmitChanges();

// Request the new product from the database
var query = from it in context.Products
            where it.ProductID == 2000
            select it;

// Since we query for a single object instead of a collection, we can use the method First()
Product product = query.First();
Console.WriteLine("{0} | {1} | {2}",
  product.ProductCategory.CategoryName, product.ProductName, product.Price);
Console.ReadLine();

Dim context As CrmDemoDataContext = New CrmDemoDataContext
' Create a new category
Dim newCategory As productcategory = New productcategory()
newCategory.CategoryID = 1000
newCategory.CategoryName = "New category"

' Create a new product
Dim newProduct As product = New product()
newProduct.ProductID = 2000
newProduct.ProductName = "New product"
newProduct.Price = 20
' Associate the new product with the new category
newProduct.productcategory = newCategory
context.products.InsertOnSubmit(newProduct)

' Send the changes to the database.
' Until you do it, the changes are cached on the client side.
context.SubmitChanges()

' Request the new product from the database
Dim query = From it In context.products _
            Where it.ProductID = 2000 _
            Select it

' Since we query for a single object instead of a collection, we can use the method First()
Dim product As product = query.First()
Console.WriteLine("{0} | {1} | {2}", _
  product.productcategory.CategoryName, product.ProductName, product.Price)
Console.ReadLine()

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, it is only necessary to call InsertOnSubmit() once to submit both product and category objects.

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

Updating 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:

product.ProductName = "Edited product";
product.Price = 15;
context.SubmitChanges();

product.ProductName = "Edited product"
product.Price = 15
context.SubmitChanges()

Deleting 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 a block of code like the following:

context.products.DeleteOnSubmit(newProduct);
context.productcategories.DeleteOnSubmit(newCategory);
context.SubmitChanges();

context.products.DeleteOnSubmit(newProduct)
context.productcategories.DeleteOnSubmit(newCategory)
context.SubmitChanges()

Deletion of objects is affected by attributes in the model. When 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.

Additional Information

Now that you can perform the basic data manipulation with LINQ to SQLite, you can move on to some advanced topics. dotConnect for SQLite includes a help section dedicated to the LINQ to SQL technology. You can access it online at http://www.devart.com/dotconnect/linq/docs/ or locally in Visual Studio help collections.

LINQ to SQLite is developed closely to the Microsoft's implementation of LINQ to SQL, so you might find some useful information in MSDN:

• LINQ to SQL
• LINQ to SQL: .NET Language-Integrated Query for Relational Data

For hands-on experience use samples shipped with dotConnect for SQLite. You can access the samples from the Start menu.

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