CODEWITHSUNDEEP
c#.netentity-framework
James
James

Reputation: 2999

How to set the count property of a related entity without having to perform an extra query or include unnecessary data

I have a class "Campaign" that has a navigation property "Students". The campaign class has an attribute "StudentsCount" to store the amount of students. I do not want to include all the students in the query results. How can I query for campaigns while attaching their respective student counts? I ideally do not want to iterate through all my campaigns after the initial query to grab the counts.

IQueryable<TEntity> query = this._objectSet.AsQueryable(); //this is my campaigns object set
query = query.Where(c => c.UserId == id);
query = query.Include("");
return query.ToArray();

Update: -- Please note that my initial query is grabbing more than one campaign

I'm thinking maybe I could do something with a select but I am not exactly sure how to accomplish this

Upvotes: 2

Views: 79

Answers (2)

James
James

Reputation: 2999

I ended up adding a computed column with sql onto the Campaign Table. 

    CREATE FUNCTION dbo.getStudentCount(@studentCount int)
RETURNS int
AS
BEGIN
    DECLARE @r int
    select @r = COUNT(*) from Student where CampaignId = @studentCount
    RETURN @r
END
GO

ALTER TABLE Campaign ADD StudentCount AS dbo.getStudentCount(Id)

this automatically sets the column to be a generated attribute in the EDMX.

Upvotes: 0

Matt Caton
Matt Caton

Reputation: 3503

Querying for counts without loading the collection of child items is called an extra lazy query if you need the term to allow you to Google around this.

In this case, you would do something like this:

var campaign = query.Single();
var studentsQuery = context.Entry(campaign).Collection(c => c.Students).Query();
var count = studentsQuery.Count();

This will materialise the count of entities without bringing them all back.

Upvotes: 1

Related Questions