Query that returns Entity and child collection.. child collection sorted?

Question

Hi All,

How can i create a query that will return a single entity along with a collection, and have the collection sorted? i don't care if it's two calls to the DB.. i just need the end result to be an Entity type and not an anonymous type:

var

category = context.Categories.Where(c => c.categoryID == categoryID).Select(c => new { c, products = c.Products.OrderByDescending(p => p.priceDate) }).First();

But i'd like to have the above either return or cast to a Category with the Products collections.. as apposed to anonymous type. thanks!

Answer 1

If you have the correct foreign key relationships in your database then doing a simple select for your category should suffice and then you can order the products so

Category category = context.Categories.Where(c => c.categoryID == categoryID).First();

List<Product> sortedProducts= context.Categories.products.OrderBy(...).ToList()

When your FK relationships are set up when you get the top level object it should retrieve its children too (i havent worked with linq-to-entities but with linq-to-sql i cant imagine it being diffrent in this aspect tho) I think the above should work...

Answer 2

Could you just put that into the category class? Something like this:

public parital class Category 
{
        public Ilist<Product> ProductsByPriceDate
        {
          get
          {
              return this.Products.OrderbyDescending(p= > p.priceDate).ToList();
          }
        }
    }