CODEWITHSUNDEEP
linqentity-framework
Kagawa
Kagawa

Reputation: 1359

Entity Framework Take N items of child collection

Say I have a Customer entity, and a Sales entity, of 1-to-many relationship. How could I get all Customers with N number of most recent sales?

var result = Customers.Where(c => c.Sales.Any());

This would return all customers with ALL their sales. What if I want just 2 sales record from each customer?

P/S: I can do that with query syntax, i'm looking for method syntax solution. I just can't figure out how to chain them together in method syntax form

var result = from cust in context.Customers
             select new
             {
                 Customers = cust,
                 Sales = cust.Sales.OrderBy(s => s.Date).Take(2)
             };

This works, but i'm not sure if this is the best way to do it.

EDIT: OK, it turns out the query syntax that i included here is not working too. Only the Sales in the anonymous type is effectively reduced to 2 records.

var filtered = result.AsEnumerable().Select(r => r.Customers);

doing this will still result in a list of customers with ALL their sales

Upvotes: 0

Views: 959

Answers (1)

Eranga
Eranga

Reputation: 32447

You can do a project as described in here

var dbquery = Customers.Select( c => new { 
                        Customer = c,
                        Sales = c.Sales.OrderBy(s => s.Date)
                             .Take(2).Select( s => new { s, s.SalesDetails})
                  });

var customers = dbquery 
   .AsEnumerable() 
   .Select(c => c.Customer);

Upvotes: 1

Related Questions