How do I properly filter child objects?

Question

I apologize for syntax errors, this is my simple explanation of the problem.

I've setup my dbml file with a relationship between Customers and Orders on CustomerId. I'm trying to return all orders for a customer that are less than $10.

Customer customer = context.Customers.FirstOrDefault(c => c.Id == 123);
IEnumerable<Order> orders = customer.Orders.Where(o => o.Total < 10);

This takes for ever because when orders gets enumerated, the sql generated ignores the where clause, pulls 1000s of records, and then in memory, filters out the orders based on the where clause.

How do I set this up so it will generate a query that filters orders at the server?

Answer 1

This will be translated into the SQL you expect:

var qry = from o in context.Orders
          where o.CustomerId == 123 
                && o.Total < 10
          select o;

(edit)

After viewing my Log, I can see that the generated SQL for your code is what I expected as well and the filtering is done at the Database with SQL. So, the orders aren't all loaded. Did you change any options in the data context like DeferredLoadingEnabled or provided any LoadOptions?