LINQ to SQL: Advanced Topics

Custom Mapping and Attributes

LINQ to SQL provides attributes that allow you to customize how your .NET objects map to your database tables and columns. While the default mapping is often sufficient, advanced scenarios may require more granular control.

Common Mapping Attributes:

• [Table]: Specifies the database table that a class maps to. You can specify the name and schema.
• [Column]: Maps a class member to a database column. Properties like Name, IsPrimaryKey, IsDbGenerated, AutoSync, and DbType offer fine-grained control.
• [Association]: Defines relationships between classes, mapping to foreign key constraints in the database.
• [Function]: Maps a class method to a stored procedure or SQL function.
• [Database]: Defines the overall data context for your application.

For example, to map a class to a table with a different name and specify a primary key column:

using System.Data.Linq.Mapping;

namespace MyDataAccess
{
    [Table(Name="dbo.Products")]
    public class Product
    {
        [Column(Name="ProductID", IsPrimaryKey=true, IsDbGenerated=true)]
        public int Id { get; set; }

        [Column]
        public string Name { get; set; }

        [Column]
        public decimal Price { get; set; }
    }
}

Executing Stored Procedures and Functions

LINQ to SQL can easily invoke stored procedures and SQL functions defined in your database. This is particularly useful for complex logic that is best managed at the database level.

Calling Stored Procedures:

You can map stored procedures to methods within your DataContext using the [Function] attribute. The parameters and return types should match the stored procedure's signature.

public partial class MyDatabaseDataContext : DataContext
{
    [Function(Name="dbo.GetProductCountByPriceRange")]
    public int GetProductCountByPriceRange(
        [Parameter(DbType="decimal(19,4)")] decimal minPrice,
        [Parameter(DbType="decimal(19,4)")] decimal maxPrice)
    {
        IExecuteResult result = this.ExecuteMethodCall(this,
            (MethodInfo)MethodInfo.GetCurrentMethod(),
            minPrice, maxPrice);
        return (int)result.ReturnValue;
    }
}

Usage:

var context = new MyDatabaseDataContext(connectionString);
int count = context.GetProductCountByPriceRange(10.0M, 100.0M);

Handling Concurrency Conflicts

Concurrency conflicts occur when multiple users try to modify the same data simultaneously. LINQ to SQL offers mechanisms to detect and handle these conflicts.

Optimistic Concurrency:

LINQ to SQL uses optimistic concurrency by default. When you update an entity, it checks if the row in the database has been modified since it was retrieved. This is typically achieved by including version numbers or timestamp columns in your table.

You can configure how LINQ to SQL handles updates using the AutoSync property of the [Column] attribute:

• AutoSync.Never: Never synchronizes the database with the object.
• AutoSync.OnInsert: Synchronizes only on insert.
• AutoSync.OnUpdate: Synchronizes only on update.
• AutoSync.Always: Synchronizes on both insert and update.
• AutoSync.Drop: Drops the column.

When a conflict is detected during SubmitChanges(), a ChangeConflictException is thrown. You can catch this exception and resolve the conflicts.

Resolving Conflicts:

You can use the RefreshMode enumeration to specify how to resolve conflicts:

• RefreshMode.KeepCurrentValues: Keeps the values from the client object.
• RefreshMode.KeepChanges: Keeps the changes made to the client object, overwriting database values.
• RefreshMode.OverwriteCurrentValues: Overwrites the client object with the current database values.
• RefreshMode.CompareAllValues: Compares all values and attempts a merge.

try
{
    context.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException ex)
{
    ex.SaveChanges();
    // Optionally, re-submit after handling conflicts
    context.SubmitChanges();
}

Performance Tuning and Optimization

Efficient data access is crucial for application performance. LINQ to SQL offers several strategies for optimization.

1. Deferred Loading vs. Immediate Loading:

• Deferred Loading (Lazy Loading): Related entities are loaded only when they are accessed. This can improve initial load times but may lead to the "N+1 query problem" if not managed carefully.
• Immediate Loading (Eager Loading): Related entities are loaded along with the main query. This avoids the N+1 problem but can increase the initial query cost.

You can control loading behavior using the [Association] attribute's IsDeferred property, or by using LINQ's LoadWith and SelectWith extensions.

// Eager Loading using LoadWith
var customers = context.Customers
    .Include(c => c.Orders)
    .ToList();

2. Query Reuse and Compilation:

LINQ to SQL compiles your queries for efficiency. While the DataContext handles caching, for frequently executed complex queries, you might consider using CompiledQuery.

public static readonly CompiledQuery<MyDatabaseDataContext, string> CompiledGetCustomerNames =
    CompiledQuery.Compile<MyDatabaseDataContext, string>(
        db => db.Customers.Select(c => c.Name)
    );

var names = CompiledGetCustomerNames(context).ToList();

3. Selecting Specific Columns:

Avoid selecting all columns from a table if you only need a few. Use Select() to project into an anonymous type or a specific DTO (Data Transfer Object).

var productInfo = context.Products
    .Select(p => new {
        p.Name,
        p.Price
    })
    .ToList();

4. Paging:

Use Skip() and Take() to implement efficient paging, translating to OFFSET and FETCH or equivalent SQL clauses.

int pageNumber = 2;
int pageSize = 10;
var paginatedProducts = context.Products
    .OrderBy(p => p.Name)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();

5. Debugging SQL Queries:

Use the DataContext's Log property to write generated SQL to a stream (e.g., Console.Out) for analysis.

context.Log = Console.Out;
var customers = context.Customers.Where(c => c.Country == "USA").ToList();

Working with Large Objects (LOBs)

LINQ to SQL can handle large object data types like VARBINARY(MAX), IMAGE, and TEXT. When retrieving LOBs, they are often loaded lazily by default.

You can use Stream.Null or manually manage streams for efficient handling of large binary or text data.

Best Practices and Considerations

• Keep your DataContext lightweight: Avoid loading excessive data at once.
• Use .ToList() or .ToArray() strategically: Execute queries when you need the data.
• Understand generated SQL: Use the Log property to see what SQL is being executed.
• Handle exceptions gracefully: Especially ChangeConflictException.
• Consider alternatives for very complex scenarios: For highly complex ORM needs, Entity Framework might be a more robust choice.
• Test thoroughly: Always test your LINQ to SQL implementations with realistic data volumes.