CODEWITHSUNDEEP
c#entity-frameworkentity-framework-5
Andrew
Andrew

Reputation: 68

Entity Framework - Getting Count of all Child Records from a List of Entities

I've been struggling to get my head around this for a while so figured I would post up here.

If I was to have a 1->Many relationship between a Parent object and a Child object. Then retrieved my Parent objects from my Context into a List object. I then iterate through said list setting a "virtual" property on each Child record. The "virtual" property is an enumeration that is defined defines a status, ie New, Unchanged, Invalid. Its implemented as a Property on an Abstract Class that the Entities implement.

Once I have done this I want to get a count of all the child objects that have a status of say "New".

Parent.Sum(p => p.Child.Where(c => c.Status == New).Count())

The first problem is that this goes back to the database once for each parent record and gets all linked child records.

Second issue is that because the Status field is not an actual database field, the values are all at the enumerations default value.

Any help with this would be greatly appreciated.

EDIT:

So firstly, I don't want the Status column in the database. I can easily add it if I want to but its not something I want to persist.

This is probably the best sample of full code that I can give. 

List<Parent> parents = myDB.Parents.ToList();

foreach(Parent parent in parents)
{
    foreach(Child child in parent.Child)
    {
        if (condition1)
        {
            child.Status == Statuses.Status1;
        }
        else if (condition2)
        {
            child.Status == Statuses.Status2;
        }
        else if (condition3)
        {
            child.Status == Statuses.Status3;
        }

    }
}

Console.WriteLine(Parent.Sum(p => p.Child.Where(c => c.Status == New).Count()))

Upvotes: 4

Views: 9550

Answers (3)

ozkary
ozkary

Reputation: 2704

We should also look to disable lazy loading. This usually causes that navigation properties to query the database. A quick way to get the child counts into a view model is shown in this article:

http://www.ozkary.com/2015/04/entity-framework-associated-table.html

Upvotes: 0

Amin Saqi
Amin Saqi

Reputation: 18977

Something like this may work:

var parents = db.Parents.ToList(); 
int count = parents.Childs.Where(c => db.Entry(c).State == EntityState.Added);

This approach solves your first problem. Because the .ToList() creates a List<Parent> into the memory and all queries on it won't perform any database query.

However I'm not sure that whether it also solves your second problem or not. The nearest EntityState to your need is EntityState.Added.

And indeed, it depends on your meaning from New state...

Upvotes: 0

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236328

Use eager loading of child entities to avoid multiple database queries:

var parents = db.Parents.Include(p => p.Child);

Also if you can't map enum property to database column (e.g. EF5 with .NET 3.5), then create integer property which will be mapped to your database column, and mark your enum property as not mapped:

[Column("Status")]
public int StatusInt { get; set; }
[NotMapped]
public StatusType Status
{
    get { return (StatusType)StatusInt; }
    set { StatusInt = (int)value; }
}

If you will use property mapped to database column, then all calculation will occur on server side:

db.Parents.Sum(p => p.Child.Where(c => c.StatusInt == (int)New).Count())

This will be translated into query like:

SELECT SUM([t2].[value]) AS [value]
FROM (
    SELECT (
        SELECT COUNT(*)
        FROM [Child] AS [t1]
        WHERE ([t1].[StatusInt] = @p0) AND ([t1].[ParentID] = [t0].[ParentID])
        ) AS [value]
    FROM [Parent] AS [t0]
    ) AS [t2]

Upvotes: 2

Related Questions