CODEWITHSUNDEEP
c#entity-framework-4linq-to-entities
Shan Plourde
Shan Plourde

Reputation: 8726

Entity Framework 4.0 - Returning List of Model objects + Count of Children Per Object

I'm running into a common need in my project to return collections of my model objects, plus a count of certain types of children within each, but I don't know if it is possible or how to model a "TotalCount" property in a Model class and populate it as part of on single Entity Framework query, preferably using LINQ queries. Is it possible to do this whilst being able to use the Entity Framework .Include("Object") and .Skip() and .Take()? I'm new to the Entity Framework so I may be missing tons of obvious stuff that can allow this...

I would like to be able to paginate on the dynamically constructed count properties as well. I'm thinking that the most scalable approach would be to store the counts as separate database properties and then simply query the count properties. But for cases where there are small row counts that I'm dealing with, I'd rather do the counts dynamically.

In a model like this:

Table: Class

Table: Professor

Table: Attendee

Table: ClassComment

I'd like to return a list of Class objects in the form of List, but I would also like the counts of Attendees and Class comments to be determined in a single query (LINQ preferred) and set in two Class properties called AttendeeCount and ClassCommentCount.

I have this thus far: 

var query = from u in context.Classes
            orderby tl.Name
            select u;

List<Class> topics = ((ObjectQuery<Class>)query)
    .Include("ClassComments")
    .Skip(startRecord).Take(recordsToReturn).ToList();

Any suggestions or alternative query approaches that can still allow the use of .Include() and pagination would be much much appreciated, in order to produce a single database query, if at all possible. Thank you for any suggestions!

Upvotes: 1

Views: 6168

Answers (2)

LukLed
LukLed

Reputation: 31882

Try this: 

public class ClassViewModel {
    public Class Class { get; set; }
    public int AttendeeCount { get; set; }
    public int ClassCommentCount { get; set; }
}


var viewModel = context.Classes.Select(clas => 
    new ClassViewModel { 
        Class = clas, 
        AttendeeCount = clas.ClassAttendes.Count, 
        ClassCommentCount = clas.ClassComments.Count}
).OrderBy(model => model.ClassCommentCount).Skip(startRecord).Take(recordsToReturn).ToList();

You don't have to include comments to get count.

Upvotes: 3

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364409

It will not work this way. The easiest approach is to use projection into anonymous (or custom) non entity type. I would try something like this:

var query = context.Classes
              .Include("ClassComments")  // Only add this if you want eager loading of all realted comments
              .OrderBy(c => c.Name)
              .Skip(startRecord)
              .Take(recordsToReturn)
              .Select(c => new 
                 {
                   Class = c,
                   AttendeeCount = c.Attendees.Count(),
                   ClassCommentCount = c.ClassComments.Count() // Not needed because you are loading all Class comments so you can call Count on loaded collection
                 });

The problem in your requirement are AttendeeCount and ClassCommentCount properties. You can't easily add them to your model because there is no corresponding column in database (unless you define one and in such case you don't need to manually count records). You can define them in partial Class implementation but in such case you can't use them in Linq-to-entities query.

The only way to map this in EF is to use DB view and create special read only entity to represent it in your applicaiton or to use DefiningQuery which is custom SQL command defined in SSDL instead of DB table or view.

Upvotes: 2

Related Questions