Linq To Sql | Linq | Csharp Tutorial

Introduction to LINQ to SQL

LINQ to SQL is a component of the .NET Framework that provides a run-time infrastructure for managing relational data as objects. With LINQ to SQL, you can use LINQ queries to retrieve and manipulate data from a SQL Server database. This tutorial will guide you through the basics of using LINQ to SQL in C#.

Setting Up the Environment

Before you start using LINQ to SQL, you need to set up your development environment. Ensure you have the following:

Visual Studio or any other C# IDE
SQL Server or SQL Server Express

Once you have these tools installed, you can create a new project in Visual Studio.

Creating the Data Context

The Data Context is the main class that coordinates LINQ to SQL functionality. It is created by mapping a database schema to a Data Context class.

To create a Data Context:

Right-click on your project and select Add > New Item.
Select LINQ to SQL Classes and name it DataClasses.dbml.
Drag and drop tables from the Server Explorer onto the designer surface.

This will generate the necessary code to interact with the database.

Basic LINQ to SQL Query

Now that we have our Data Context, we can start querying the database. Here's an example of a basic query:

using (DataClassesDataContext db = new DataClassesDataContext())
{
    var query = from customer in db.Customers
                select customer;
    
    foreach (var customer in query)
    {
        Console.WriteLine(customer.ContactName);
    }
}

This code snippet queries the Customers table and prints the ContactName of each customer.

Inserting Data

To insert new data into the database, you can create a new object and insert it using the Data Context:

using (DataClassesDataContext db = new DataClassesDataContext())
{
    Customer newCustomer = new Customer
    {
        CustomerID = "ALFKI",
        CompanyName = "Alfreds Futterkiste",
        ContactName = "Maria Anders"
    };
    
    db.Customers.InsertOnSubmit(newCustomer);
    db.SubmitChanges();
}

This code snippet inserts a new customer into the Customers table.

Updating Data

To update existing data, fetch the object, modify it, and submit the changes:

using (DataClassesDataContext db = new DataClassesDataContext())
{
    var customer = db.Customers.Single(c => c.CustomerID == "ALFKI");
    customer.ContactName = "Alfred Schmidt";
    db.SubmitChanges();
}

This code snippet updates the ContactName of the customer with CustomerID "ALFKI".

Deleting Data

To delete data, fetch the object and delete it using the Data Context:

using (DataClassesDataContext db = new DataClassesDataContext())
{
    var customer = db.Customers.Single(c => c.CustomerID == "ALFKI");
    db.Customers.DeleteOnSubmit(customer);
    db.SubmitChanges();
}

This code snippet deletes the customer with CustomerID "ALFKI".

Advanced Queries

LINQ to SQL allows you to perform more complex queries. Here's an example of joining two tables:

using (DataClassesDataContext db = new DataClassesDataContext())
{
    var query = from order in db.Orders
                join customer in db.Customers on order.CustomerID equals customer.CustomerID
                select new { customer.ContactName, order.OrderID };
    
    foreach (var result in query)
    {
        Console.WriteLine("Customer: " + result.ContactName + ", Order ID: " + result.OrderID);
    }
}

This code snippet joins the Orders and Customers tables and prints the ContactName and OrderID of each result.

Conclusion

In this tutorial, we've covered the basics of using LINQ to SQL including setting up the environment, creating a Data Context, and performing basic CRUD (Create, Read, Update, Delete) operations. With these concepts, you can start integrating LINQ to SQL into your C# applications to manage relational data more efficiently.

LINQ to SQL Tutorial