A good(elegant) way to retrieve records with counts.

Question

Context: ASP.NET MVC 2.0, C#, SQL Server 2007, IIS7

I have 'scheduledMeetings' table in the database. There is a one-to-many relationship: scheduledMeeting -> meetingRegistration So that you could have 10 people registered for a meeting. meetingRegistration has fields Name, and Gender (for example).

I have a "calendar view" on my site that shows all coming events, as well as gender count for each event.

At the moment I use Linq to Sql to pull the data:

var meetings = db.Meetings.Select(
    m => new {
        MeetingId = m.Id,
        Girls = m.Registrations.Count(r => r.Gender == 0),
        Boys = m.Registrations.Count(r=>r.Gender == 1)
    });

(actual query is half-a-page long) Because there is anonymous type use going on I cant extract it into a method (since I have several different flavors of calendar view, with different information on each, and I dont want to create new class for each).

Any suggestions on how to improve this? Is database view is the answer? Or should I go ahead and create named-type?

Any feedback/suggestions are welcome. My DataLayer is huge, I want to trim it, just dont know how.

Pointers to a good reading would be good too.

Answer 1

I'd extend your Meetings class by adding 2 properties:

public partial class Meeting
{
    #region Properties
    public int BoyCount { get; set; }

    public int GirlCount { get; set; }
    #endregion
}

With deferred loading:

var items = db.Meetings.Select(
    m => new {
        Meeting = m,
        Girls = m.Registrations.Count(r => r.Gender == 0),
        Boys = m.Registrations.Count(r = >r.Gender == 1)
    }).ToList();

items.ForEach(i =>
{
    i.Meeting.BoyCount = i.Boys;
    i.Meeting.GirlCount = i.Girl;
});

List<Meeting> = items
    .Select(i => i.Meeting)
    .ToList();

With eager loading, one of the solutions is to load Registrations with your Meeting entity:

DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Meeting>(m = > m.Registrations);
db.LoadOptions = loadOptions;

In this case the partial class properties above are became getters:

public partial class Meeting
{
    #region Properties
    public int BoyCount 
    { 
        get
        {
            return this.Registrations
                .Count(r => r.Gender == 1);
        }
    }

    public int GirlCount
    {
        get
        {
            return this.Registrations
                .Count(r = > r.Gender == 0);
        }
    }
    #endregion
}