LINQ to SQL

Language-Integrated Query (LINQ) to SQL is a component of the .NET Framework that provides a run-time infrastructure for managing data from relational databases like SQL Server. It allows developers to write queries against relational data in a .NET language with the same syntax and basic constructs as querying .NET objects. LINQ to SQL translates these queries into SQL statements that are executed against the database.

Key Features:

Object-Relational Mapping (ORM)
Querying SQL Server databases using LINQ syntax
Automatic translation of LINQ queries to SQL
Support for defining mappings between database tables and .NET classes
Rich IntelliSense support for queries

Getting Started with LINQ to SQL

To use LINQ to SQL, you typically need to:

Define your database schema: This can be done directly in SQL Server.
Create a LINQ to SQL data context: This object represents your database and the tables within it. You can generate this using tools like the SQLMetal command-line utility or the Object Relational Designer in Visual Studio.
Map database tables to C# or VB.NET classes: Each table in your database will correspond to a .NET class, and columns will map to properties of that class.
Write LINQ queries: Use LINQ syntax to query data through your data context.

Using the SQLMetal Tool

SQLMetal is a command-line utility that can generate code for LINQ to SQL. You can use it to generate a data context class and entity classes from an existing database, or to generate a `.dbml` file which can then be used in Visual Studio.

sqlmetal /server:your_server /database:your_database /dbml:your_schema.dbml
sqlmetal /server:your_server /database:your_database /code:your_context.cs /language:csharp

Using the Object Relational Designer (Visual Studio)

In Visual Studio, you can add a new item of type "LINQ to SQL Classes" to your project. This will open the Object Relational Designer where you can drag tables and views from your database to create the data context and entity mappings.

Querying Data with LINQ to SQL

Once your data context and mappings are set up, querying data is straightforward. Here's an example of querying customers from a hypothetical database:

using System;
using System.Linq;

// Assuming 'YourDataContext' is your generated data context class
// and 'Customers' is a Table property within it.
// 'Customer' is a class mapping to your Customer table.

var context = new YourDataContext("YourConnectionString");

// Querying all customers
var allCustomers = from c in context.Customers
                   select c;

// Querying customers in a specific city
var customersInLondon = from c in context.Customers
                        where c.City == "London"
                        select c;

// Iterating through the results
foreach (var customer in customersInLondon)
{
    Console.WriteLine($"Name: {customer.ContactName}, City: {customer.City}");
}

Inserting, Updating, and Deleting Data

LINQ to SQL also simplifies data modification operations.

Inserting a New Record

var newCustomer = new Customer
{
    CustomerID = "ALFKI",
    CompanyName = "Alfreds Futterkiste",
    ContactName = "Maria Anders",
    City = "Berlin"
};
context.Customers.InsertOnSubmit(newCustomer);
context.SubmitChanges();

Updating an Existing Record

var customerToUpdate = context.Customers.FirstOrDefault(c => c.CustomerID == "ALFKI");
if (customerToUpdate != null)
{
    customerToUpdate.City = "Hamburg";
    context.SubmitChanges();
}

Deleting a Record

var customerToDelete = context.Customers.FirstOrDefault(c => c.CustomerID == "ALFKI");
if (customerToDelete != null)
{
    context.Customers.DeleteOnSubmit(customerToDelete);
    context.SubmitChanges();
}

Performance Tip: Use .ToList() or .ToArray() judiciously to materialize query results when you need to iterate multiple times or perform operations that require in-memory collections. Be mindful of fetching large datasets.

MSDN Documentation