C# – Query that returns Entity and child collection.. child collection sorted

clinq-to-entities

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!

Best Answer

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();
          }
        }
    }

Related Solutions

C# – How to return many Child Collections Count() in a single entity framework query

This should work:

IQueryable parentQuery = context.Parent.Select(p => new { Parent = p, ChildCount = p.Childs.Count() });

EDIT

If you define:

public class ParentModel
{
    public Task Parent { get; set; }
    public int ChildCount { get; set; }
}

you can use

IQueryable parentQuery = context.Parent.Select(p => new ParentModel { Parent = p, ChildCount = p.Childs.Count() });

EDIT

You can also do:

var parentQuery = context.Parent.Select(p => new { Parent = p, ChildCount = p.Childs.Count() }).ToList();
parentQuery.ForEach(p => p.Parent.ChildCount = p.ChildCount);
var result = return parentQuery.Select(p => p.Parent);

Short and you have your property populated.

Entity-framework – Entity Framework: Querying Child Entities

The only way to get a collection of parents with a filtered children collection in a single database roundtrip is using a projection. It is not possible to use eager loading (Include) because it doesn't support filtering, Include always loads the whole collection. The explicite loading way shown by @Daz requires one roundtrip per parent entity.

Example:

var result = db.Parents
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(c => c.Age >= 5)
    })
    .ToList();

You can directly work with this collection of anonymous type objects. (You can also project into your own named type instead of an anonymous projection (but not into an entity like Parent).)

EF's context will also populate the Children collection of the Parent automatically if you don't disable change tracking (using AsNoTracking() for example). In this case you can then project the parent out of the anonymous result type (happens in memory, no DB query):

var parents = result.Select(a => a.Parent).ToList();

parents[i].Children will contain your filtered children for each Parent.

Edit to your last Edit in the question:

I am after a) A list of parents who have a child older than 5 (and include only those children).

The code above would return all parents and include only the children with Age >= 5, so potentially also parents with an empty children collection if there are only children with Age < 5. You can filter these out using an additional Where clause for the parents to get only the parents which have at least one (Any) child with Age >= 5:

var result = db.Parents
    .Where(p => p.Children.Any(c => c.Age >= 5))
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(c => c.Age >= 5)
    })
    .ToList();

Best Answer

Related Solutions

C# – How to return many Child Collections Count() in a single entity framework query

Entity-framework – Entity Framework: Querying Child Entities

Related Topic