Got it, let's work through this together. When you're dealing with 5 related tables in LINQ to SQL and need to map the query results to their respective strongly-typed C# objects, the key is to structure your query to explicitly project each table's data into its corresponding class. Let's break this down with practical examples and fix the common pitfalls that might have messed up your first attempt.

We'll assume you have LINQ to SQL entity classes generated for your tables (e.g., Order, Customer, Product, OrderItem, Supplier—swap these for your actual entity names).

1. Using Navigation Properties (Cleanest Approach) #

If your DataContext already has navigation properties set up (which it should, if you generated the model from your database), you can skip explicit Join calls and leverage the built-in relationships directly. This keeps your code concise and readable.

Here's a Fluent/Lambda example that pulls data from 5 linked tables and maps each to its object:

using (var db = new YourDataContext())
{
    // Query with eager loading to avoid lazy-load nulls
    var query = db.Orders
        .Include(o => o.Customer) // Eager load Customer related to Order
        .Include(o => o.OrderItems)
            .ThenInclude(oi => oi.Product) // Eager load Product related to OrderItem
                .ThenInclude(p => p.Supplier) // Eager load Supplier related to Product
        .Where(o => o.OrderDate >= new DateTime(2024, 1, 1)) // Add your filters here
        .Select(o => new 
        {
            Order = o,
            Customer = o.Customer,
            OrderItemsWithDetails = o.OrderItems.Select(oi => new 
            {
                OrderItem = oi,
                Product = oi.Product,
                Supplier = oi.Product.Supplier
            })
        });

    // Extract each object into separate variables
    foreach (var result in query)
    {
        // Assign to strongly-typed variables
        Order orderObj = result.Order;
        Customer customerObj = result.Customer;

        foreach (var itemDetail in result.OrderItemsWithDetails)
        {
            OrderItem orderItemObj = itemDetail.OrderItem;
            Product productObj = itemDetail.Product;
            Supplier supplierObj = itemDetail.Supplier;

            // Now you can work with each typed object individually
            Console.WriteLine($"Order {orderObj.Id}: {customerObj.Name} ordered {productObj.Name} (from {supplierObj.CompanyName})");
        }
    }
}

2. Explicit Joins (For Custom Relationships) #

If you need to manually define joins (e.g., for non-standard relationships not detected by LINQ to SQL), use the Join method in Fluent syntax:

using (var db = new YourDataContext())
{
    var query = db.Orders
        // Join Order to Customer
        .Join(db.Customers, 
              order => order.CustomerId, 
              customer => customer.Id, 
              (order, customer) => new { order, customer })
        // Join result to OrderItem
        .Join(db.OrderItems, 
              oc => oc.order.Id, 
              item => item.OrderId, 
              (oc, item) => new { oc.order, oc.customer, item })
        // Join result to Product
        .Join(db.Products, 
              oci => oci.item.ProductId, 
              product => product.Id, 
              (oci, product) => new { oci.order, oci.customer, oci.item, product })
        // Join result to Supplier
        .Join(db.Suppliers, 
              ocip => ocip.product.SupplierId, 
              supplier => supplier.Id, 
              (ocip, supplier) => new 
              {
                  Order = ocip.order,
                  Customer = ocip.customer,
                  OrderItem = ocip.item,
                  Product = ocip.product,
                  Supplier = supplier
              })
        .Where(result => result.Order.OrderDate >= new DateTime(2024, 1, 1));

    // Extract each object into variables
    foreach (var result in query)
    {
        Order orderObj = result.Order;
        Customer customerObj = result.Customer;
        OrderItem orderItemObj = result.OrderItem;
        Product productObj = result.Product;
        Supplier supplierObj = result.Supplier;

        // Use the typed variables as needed for your system
        // ...
    }
}

Key Tips to Fix Your Previous Issues #

• Eager Load Related Entities: If you were getting nulls or incomplete data, you probably forgot to use Include() to eager-load navigation properties. Lazy loading might not fetch related data until you access it, leading to unexpected behavior.
• Stick to Strong Typing: Always project results into either your existing entity classes or well-defined anonymous types—never rely on untyped results, which cause casting headaches.
• Validate Join Conditions: Double-check that your join keys match (e.g., order.CustomerId maps to customer.Id). Mismatched keys are a common source of empty or incorrect results.

内容的提问来源于stack exchange，提问作者Pedro Lopes