Querying Relational Databases with LINQ to SQL

LINQ to SQL is a built-in ORM (Object Relational Mapper) tool in .NET that allows us to query relational databases using LINQ (Language Integrated Query) syntax. In this tutorial, we'll look at how to use LINQ to SQL to query a relational database.

Syntax

// Creating DataContext instance
var dataContext = new DataContext(connectionString);

// Querying database using LINQ to SQL syntax
var query = from t in dataContext.TableName
            where t.ColumnName == value
            select t;

Example

Suppose we have a database named "TestDB" with a table named "Employees" containing two columns "EmployeeID" (int) and "Name" (varchar). Now, let's see how to use LINQ to SQL to retrieve all the employees whose Name column values start with "J".

using System.Linq;
using System.Data.Linq;

class Program
{
    static void Main(string[] args)
    {
        // Creating DataContext instance
        var dataContext = new DataContext("Data Source=(local);Initial Catalog=TestDB;Integrated Security=True");

        // Querying database using LINQ to SQL syntax
        var query = from e in dataContext.GetTable<Employee>()
                    where e.Name.StartsWith("J")
                    select e;

        // Outputting the results
        foreach (var employee in query)
        {
            Console.WriteLine(employee.Name);
        }

        Console.ReadLine();
    }
}

// Model Class representing the Employee Table
public class Employee
{
    public int EmployeeID { get; set; }
    public string Name { get; set; }
}

Output

After running the above code, the output will display the names of all employees whose names start with "J":

John
James

Explanation

In the above syntax, a DataContext object is created using a connection string to specify the database to connect to. LINQ to SQL syntax is used to query the Employee table, where the name column value starts with "J". The results are then displayed using a foreach loop.

Use

LINQ to SQL is commonly used to retrieve data from a relational database, using a familiar syntax that's similar to querying in-memory collections with LINQ. It is used to abstract the database and manipulate data using strongly-typed objects, with the help of a Model Class that represents the table structure.

Important Points

• To use LINQ to SQL, a DataContext object must be created using the necessary connection string parameters.
• A Model Class should be created, representing the table structure, to map the results with strong type objects.
• LINQ to SQL queries are executed when the query is enumerated by calling a method like ToList or by using a foreach loop.

Summary

In this tutorial, we learned about querying relational databases using LINQ to SQL in .NET. We saw how to create a DataContext object, query a table using LINQ to SQL syntax, and then use a Model Class to represent the table structure. LINQ to SQL simplifies the querying process and makes it easy to manipulate relational databases, using a familiar syntax.