LINQ to SQL

Introduction

LINQ to SQL (Language Integrated Query to SQL) is a component of the .NET Framework that allows developers to work with relational data using the same syntax and tools they use for querying objects. It provides a way to query, insert, update, and delete data in a database using C# or Visual Basic.

In this tutorial, we will explore how to use LINQ to SQL to interact with databases. We'll cover basic querying, as well as more advanced operations like filtering, sorting, and joining data.

Concept

LINQ to SQL is built on top of the .NET Framework's LINQ (Language Integrated Query) technology. It allows developers to write queries in a strongly-typed manner using C# or Visual Basic. The queries are translated into SQL commands that are executed against the database.

The main components of LINQ to SQL include:

• DataContext: Represents a session with the database and is used to query, insert, update, and delete objects.
• Table<T>: Represents a table in the database and provides methods for querying data.
• Entity Classes: Represent rows in the database tables as strongly-typed objects.

Examples

Setting Up LINQ to SQL

Before we start querying data, we need to set up our project. Here are the steps:

1. Create a New Project: Open Visual Studio and create a new Console Application.
2. Add LINQ to SQL Classes: Right-click on the project in Solution Explorer, select "Add" -> "New Item", and choose "LINQ to SQL Classes". Name it DataClasses.dbml.

Designing the Database Model

1. Open the .dbml File: This will open the Object Relational Designer.
2. Drag Tables from Server Explorer: Connect to your database in Server Explorer, drag the tables you want to work with into the designer.

Querying Data

Let's start by querying data from a table. Assume we have a Customers table with columns like CustomerID, CompanyName, and ContactName.

using System;
using System.Linq;
using DataClasses; // Namespace generated by LINQ to SQL

class Program
{
    static void Main()
    {
        // Create a new DataContext instance
        using (var db = new NorthwindDataContext())
        {
            // Query all customers
            var customers = from customer in db.Customers
                           select customer;

            // Display the results
            foreach (var customer in customers)
            {
                Console.WriteLine($"{customer.CustomerID}: {customer.CompanyName}");
            }
        }
    }
}

Filtering Data

You can filter data using LINQ's Where method. For example, to find all customers from a specific country:

using System;
using System.Linq;
using DataClasses;

class Program
{
    static void Main()
    {
        using (var db = new NorthwindDataContext())
        {
            // Query customers from Germany
            var germanCustomers = from customer in db.Customers
                                where customer.Country == "Germany"
                                select customer;

            foreach (var customer in germanCustomers)
            {
                Console.WriteLine($"{customer.CustomerID}: {customer.CompanyName}");
            }
        }
    }
}

Sorting Data

You can sort data using LINQ's OrderBy or OrderByDescending methods. For example, to sort customers by company name:

using System;
using System.Linq;
using DataClasses;

class Program
{
    static void Main()
    {
        using (var db = new NorthwindDataContext())
        {
            // Query and sort customers by CompanyName
            var sortedCustomers = from customer in db.Customers
                                orderby customer.CompanyName
                                select customer;

            foreach (var customer in sortedCustomers)
            {
                Console.WriteLine($"{customer.CustomerID}: {customer.CompanyName}");
            }
        }
    }
}

Joining Data

You can join tables using LINQ's Join method. For example, to join the Customers table with the Orders table:

using System;
using System.Linq;
using DataClasses;

class Program
{
    static void Main()
    {
        using (var db = new NorthwindDataContext())
        {
            // Join Customers and Orders tables
            var customerOrders = from customer in db.Customers
                                join order in db.Orders on customer.CustomerID equals order.CustomerID
                                select new { CustomerName = customer.CompanyName, OrderID = order.OrderID };

            foreach (var item in customerOrders)
            {
                Console.WriteLine($"{item.CustomerName}: Order ID {item.OrderID}");
            }
        }
    }
}

Inserting Data

To insert data into the database, you can create a new entity object and add it to the Table<T> collection:

using System;
using DataClasses;

class Program
{
    static void Main()
    {
        using (var db = new NorthwindDataContext())
        {
            // Create a new customer
            var newCustomer = new Customer
            {
                CustomerID = "CUST101",
                CompanyName = "New Customer Co.",
                ContactName = "John Doe"
            };

            // Add the new customer to the Customers table
            db.Customers.InsertOnSubmit(newCustomer);

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

            Console.WriteLine("Customer added successfully.");
        }
    }
}

Updating Data

To update data, you can modify the properties of an entity object and then call SubmitChanges:

using System;
using DataClasses;

class Program
{
    static void Main()
    {
        using (var db = new NorthwindDataContext())
        {
            // Find a customer by CustomerID
            var customer = (from c in db.Customers
                           where c.CustomerID == "CUST101"
                           select c).FirstOrDefault();

            if (customer != null)
            {
                // Update the contact name
                customer.ContactName = "Jane Doe";

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

                Console.WriteLine("Customer updated successfully.");
            }
        }
    }
}

Deleting Data

To delete data, you can remove an entity object from the Table<T> collection and then call SubmitChanges:

using System;
using DataClasses;

class Program
{
    static void Main()
    {
        using (var db = new NorthwindDataContext())
        {
            // Find a customer by CustomerID
            var customer = (from c in db.Customers
                           where c.CustomerID == "CUST101"
                           select c).FirstOrDefault();

            if (customer != null)
            {
                // Remove the customer from the Customers table
                db.Customers.DeleteOnSubmit(customer);

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

                Console.WriteLine("Customer deleted successfully.");
            }
        }
    }
}

What's Next?

In this tutorial, we covered the basics of querying and manipulating data with LINQ to SQL. In the next section, we will explore Async Programming in C#, which allows you to write asynchronous code that can improve the performance of your applications by not blocking the main thread while waiting for I/O operations to complete.

Stay tuned for more advanced topics and practical examples!