LINQ syntax across Many-to-Many

I've been struggling with LINQ a bit, and was after help. I could do it in SQL, but can't seem to work out how to in LINQ. I have a SQL Compact 4.0 Database, with a Entity Framework 4.0 EDMX modelling it, in C#.

This is the scenario, these tables:

Customers - OrderDetails - Orders

The OrderDetails table is a non-payload table, just facilitating the many to many join.

If I'm given an CustomerId Number as a parameter, I want to return an 开发者_如何学CIEnumerable<Orders>.

In SQL I would've written this as:

SELECT     Orders.*  
FROM         OrderDetails INNER JOIN  
                  Orders ON OrderDetails.OrderId = Orders.OrderId INNER JOIN  
                  Customers ON OrderDetails.CustomerId = Customers.CustomerId

How can I do this in LINQ?

If you want to "think in SQL", you can do this as a query expression like this:

int customerId = ...;
var query = from detail in OrderDetails
            where detail.CustomerId == customerId
            join order in Orders on detail.OrderId equals order.OrderId
            select order;

Note that this doesn't actually touch the Customers table at all... I'm assuming that there are enough relational constraints to ensure that OrderDetails.CustomerId really does refer to a real customer.

If you have all the relationships set up appropriately, however, you can use something like Sjoerd's answer. Note that that will first fetch the Customer entity, whereas the above query doesn't. It's a more OO way of thinking about things though.

EDIT: As it appears your relationships are set up appropriately, two options:

// This will involve checking the customer data first
var customer = db.Customers.SingleOrDefault(c => c.CustomerId == customerId);
if (customer != null)
{
    var orders = customer.Orders;
}

// This shouldn't... hopefully :) You'll end up with an empty sequence if
// the customer doesn't exist
var orders = db.Customers.Where(c => c.CustomerId == customerId)
                         .SelectMany(c => c.Orders);

Something like this:

Customer customer = DataContext.Customers.Single(c => c.Id == CustomerId);
IEnumberable<Order> orders = customer.OrderDetails.Orders;

I think this could work :

int customerId = ...;
var query = Customers.Include("Orders").Where(cust => cust.CustomerID == customerId)

If you want all orders which have at least one customer with ID customerId you could also try this:

var orders = context.Orders
    .Where(o => o.Customers.Any(c => c.CustomerId == customerId)).ToList();

It looks somewhat weird to me that one order can have many customers but that's how I understand your description: "Customers - OrderDetails - Orders ... The OrderDetails table is a non-payload table, just facilitating the many to many join."

I'm expecting that an Order in your EntityModel has a navigation collection of Customers and OrderDetails is only a internal join table not existing as an entity.