none
How do I include top 5 of a related table RRS feed

  • Question

  • Hi,

    Is it possible to include top 5 rows of a related entity. In this sample I would like to include top 5 orders sorted by OrderDate.

    Customers.Include("Orders");

    And is it by the way possible to include orders without having to write it as a string? I would like to make sure that it is being checked by the compiler.

    Thanks

    Henrik Skak Pedersen

    Thursday, June 28, 2012 5:56 PM

Answers

  • Hi Henrik;

    Here is sample code showing how to query the Customers table and filter the Customers table on all Customers in the UK and then returning each Customer record in a variable called cust and the top 5 Orders, first 5 Orders when sorted by date with most resent date first in a variable called Top5Orders. The Microsoft sample database Northwind was used for this sample query.

    var results = from cust in Customers
                  where cust.Country == "UK"
                  select new {
                        cust,
                        Top5Orders = cust.Orders.OrderByDescending(o => o.OrderDate).Take(5)
                  };

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, June 29, 2012 1:18 PM

All replies

  • Hi Henrik Skak Pedersen;

    If I understand you correctly you want to query the Customers table and for each customer selected return the 5 top Orders which are sorted by OrderDate. If that is correct you can do that but not using the Include method call because it will include ALL Order records. What you need to do is query the Customers table and filter as needed then in the select clause make a sub query to get the 5 top Order records and then sort and return the results of the sub query.

    Hope that helps.

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, June 28, 2012 7:10 PM
  • Hi Fernando,

    Ok, thanks, I just thought that there would be a more smooth solution.

    Do you know to include child entities without including the property as a string?

    Thanks

    Henrik

    Thursday, June 28, 2012 7:45 PM
  • Hi Henrik;

    In order to use the Include method with a lambda expression you need to be using EF 4.1 or higher and you need to be using DbContext. If you have that add the using statement at the top of your code System.Data.Entity then you can use the Include with lambda expressions.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, June 28, 2012 8:18 PM
  • Hi Fernando,

    Do you maybe have an example of how to do that?

    Thanks Henrik

    Friday, June 29, 2012 9:50 AM
  • Hi Henrik;

    Here is sample code showing how to query the Customers table and filter the Customers table on all Customers in the UK and then returning each Customer record in a variable called cust and the top 5 Orders, first 5 Orders when sorted by date with most resent date first in a variable called Top5Orders. The Microsoft sample database Northwind was used for this sample query.

    var results = from cust in Customers
                  where cust.Country == "UK"
                  select new {
                        cust,
                        Top5Orders = cust.Orders.OrderByDescending(o => o.OrderDate).Take(5)
                  };

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, June 29, 2012 1:18 PM
  • Thank you
    Friday, June 29, 2012 2:37 PM
  •  

    Not a problem Henrik, glad I was able to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, June 29, 2012 2:52 PM