CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-6entity-relationship
Michal Ciechan
Michal Ciechan

Reputation: 13898

EF6 Getting Count In Single Call

Try to do a single database call to get an entity, as well as the count of related child entities.

I know I can retrieve the count using

var count = Context.MyChildEntitySet.Where(....).Count();

or even MyEntity.ListNavigationProperty.Count()

But That means getting the entity first, followed by another call in order to get the count or use an Include which would retrieve the whole list of related entities.

I am wondering is it possible to add a "Computed" column in SQL Server to return the Count of related rows in another table?

If not how do I ask EF to retrieve the related count for each entity in once call?

I am thinking of possibly using Join with GroupBy, but this seems an Ugly solution/hack.

public class MyEntity
{
    public uint NumberOfVotes{ get; private set; }
}

which ideally woudl generate SQL Similar to:

SELECT 
     *, 
     (SELECT Count(*) FROM ChildTable c WHERE c.ParentId = p.Id) NumberOfVotes
FROM ParentTable p

Upvotes: 2

Views: 822

Answers (1)

Phil Wright
Phil Wright

Reputation: 22956

UPDATED

You can always drop down to using actual SQL in the following way...

string query = "SELECT *, 
                       (SELECT Count(*) FROM ChildTable c 
                       WHERE c.ParentId = p.Id) as NumberOfVotes
                FROM ParentTable p";

RowShape[] rows = ctx.Database.SqlQuery<RowShape>(query, new object[] { }).ToArray();

I realize this is not ideal because then you are taking a dependency on the SQL dialect of your target database. If you moved form SQL Server to something else then you would need to check and maybe modify your string.

The RowShape class is defined with the properties that match the ParentTable along with an extra property called NumberOfVotes that has the calculated count.

Still, a possible workaround.

Upvotes: 1

Related Questions