Querying Data with LINQ to SQL

LINQ to SQL provides a powerful and intuitive way to query your relational databases using Language Integrated Query (LINQ). This topic covers the fundamental aspects of querying data, from simple selects to more complex operations.

Basic Querying

The core of LINQ to SQL querying involves creating a DataContext instance and using it to access your mapped database tables. You then write LINQ queries against these table objects.

Selecting All Records

To retrieve all records from a table, you can use a simple query that projects the entire object.

using System.Linq;

// Assume 'db' is an instance of your DataContext
var allCustomers = from c in db.Customers
                   select c;

foreach (var customer in allCustomers)
{
    Console.WriteLine($"ID: {customer.CustomerID}, Name: {customer.CompanyName}");
}
            

Filtering Data (Where Clause)

The Where clause is used to filter the results based on specified conditions. This translates directly into SQL WHERE clauses.

var londonCustomers = from c in db.Customers
                      where c.City == "London"
                      select c;

foreach (var customer in londonCustomers)
{
    Console.WriteLine($"ID: {customer.CustomerID}, Name: {customer.CompanyName}, City: {customer.City}");
}
            

Selecting Specific Columns (Projection)

You can select specific columns by creating an anonymous type or a custom type in your select clause.

var customerInfo = from c in db.Customers
                   where c.Country == "USA"
                   select new { c.CompanyName, c.City };

foreach (var info in customerInfo)
{
    Console.WriteLine($"Company: {info.CompanyName}, City: {info.City}");
}
            

Ordering Results

Use the OrderBy and OrderByDescending clauses to sort your query results.

Sorting Ascending

var sortedCustomers = from c in db.Customers
                      orderby c.CompanyName ascending
                      select c;

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

Sorting Descending

var recentOrders = from o in db.Orders
                   orderby o.OrderDate descending
                   select o;

foreach (var order in recentOrders)
{
    Console.WriteLine($"Order ID: {order.OrderID}, Date: {order.OrderDate}");
}
            

Joining Tables

LINQ to SQL supports joining multiple tables using the join clause, which generates SQL JOIN statements.

Inner Join

Join two tables based on a common key.

var customerOrders = from c in db.Customers
                     join o in db.Orders on c.CustomerID equals o.CustomerID
                     select new { c.CompanyName, OrderID = o.OrderID };

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

Group Join (Left Outer Join Equivalent)

Use join ... into ... group for scenarios similar to SQL's LEFT JOIN, allowing you to retrieve customers even if they have no orders.

var customerOrdersGroup = from c in db.Customers
                          join o in db.Orders on c.CustomerID equals o.CustomerID into customerOrderGroup
                          from order in customerOrderGroup.DefaultIfEmpty()
                          select new { c.CompanyName, OrderID = (int?)order.OrderID };

foreach (var item in customerOrdersGroup)
{
    if (item.OrderID.HasValue)
    {
        Console.WriteLine($"Customer: {item.CompanyName}, Order ID: {item.OrderID.Value}");
    }
    else
    {
        Console.WriteLine($"Customer: {item.CompanyName}, No Orders");
    }
}
            

Aggregation Functions

LINQ provides several built-in aggregation functions like Count, Sum, Average, Min, and Max.

Counting Records

int customerCount = db.Customers.Count();
Console.WriteLine($"Total Customers: {customerCount}");

int usaCustomerCount = db.Customers.Count(c => c.Country == "USA");
Console.WriteLine($"USA Customers: {usaCustomerCount}");
            

Summing Values

decimal? totalFreight = db.Orders.Sum(o => o.Freight);
Console.WriteLine($"Total Freight: {totalFreight:C}");
            

Other Useful Querying Features

Paging (Skip and Take)

Implement paging by using Skip and Take methods.

int pageSize = 10;
int pageNumber = 2;
var productsPage = db.Products.OrderBy(p => p.ProductID)
                            .Skip((pageNumber - 1) * pageSize)
                            .Take(pageSize);

foreach (var product in productsPage)
{
    Console.WriteLine($"Product: {product.ProductName}");
}
            

Distinct Values

Retrieve unique values using the Distinct method.

var uniqueCountries = db.Customers.Select(c => c.Country).Distinct();

foreach (var country in uniqueCountries)
{
    Console.WriteLine(country);
}
            

Deferred Execution

LINQ to SQL queries are typically executed only when the results are iterated over (e.g., in a foreach loop) or when a method like ToList(), ToArray(), or First() is called. This is known as deferred execution.

Conclusion

LINQ to SQL simplifies data access by allowing you to write database queries in C# or VB.NET. By mastering these querying techniques, you can efficiently retrieve, filter, sort, and aggregate data from your SQL Server databases.