Linq query question (one to many, child object property filter)

Question

I'm quite new to linq and struggling to create a query. I have a 'Customers' table and 'Orders' table, one-to-many relationship. How do I select those customers, who have oldest order with word 'special' in order description?

If oldest order does not contain this word, customer should not be in the result. If customer does not have orders, he should not be in the result. In other words, only those customers who have orders and whose oldest order has word 'special' in it should be there.

I hope I'm clear and thank you.

Answer 1

This is a quick guess extrapolating what I can from your question. It may or may not be accurate:

var dc = new MyDataContext();    
var qry = from customer in dc.Customers 
          where customer.Orders.Description.Contains("special")
             && customer.Orders.OrderDate > myMinDate 
             && customer.Orders.OrderDate < myMaxDate 
          order by customer.Orders.OrderDate ascending 
          select customer;

It should return Customer objects ordered by their order dates, filtered by a min and max date. Those two lines can be removed if you don't want to filter by date.

Expect the customers to repeat. If you need non-repeating, add a group by clause.

Good luck with it, report back how well it works. :)

Edit:

Thanks, Randolpho, the problem is that I don't have myMinDate and myMaxDate. I need to be able to inspect the description of the oldest order. I feel that I should use something like where c.Orders.Where(x=>x.OrderDate.Max()) but can't compose the whole query correctly. Thanks. – Valentin Vasiliev

Hmm... I can't tell if you want the customer with the oldest order and from that you wish to inspect the description or if you want the customer with the oldest order that has the word "special" in the order's description.

If you want the customer with the oldest order and then want to check the description, you should do something like this:

var qry = from customer in dc.Customers
          order by customer.Orders.OrderDate ascending
          select new 
          { 
             CustomerData = customer, 
             OrderDescription = customer.Orders.Description
          };
var oldest = qry.First();
if(oldest.OrderDescription.Contains("special"))
{
    // do something
}

In this scenario, oldest is a new anonymous type that has two fields, CustomerData, which contains the customer with the oldest order, and OrderDescription, which contains the description field of the oldest order.

On the other hand, you might want the oldest order that contains the word "special". In that case, you should do something like this:

var qry = from customer in dc.Customers
          where customer.Orders.Description.Contains("special")
          order by customer.Orders.OrderDate ascending
          select customer;
var oldestCustomerWithSpecial = qry.First();

In this scenario, the customer with the oldest record that contains the word "special" in the description is in the variable named oldestCustomerWithSpecial.

I don't think using Max on OrderDate will help you. You're almost always better off using an order by date TOP 1 query. Calling First() on the query will do that.

Answer 2

public static List<Client> SampleSelect(List<Client> clients)
{
    return clients.Where(c => c.Orders.Count > 0 &&
                                      c.Orders.OrderBy(o => o.Date)
                                              .FirstOrDefault().Description.Contains("Special")).ToList();
}

//Fixed ordering, thnx to Ryan Versaw