A nice (elegant) way to get counts

Question

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

There is a schedMeetings table in the database. There is a one-to-many relationship: planned Marketing → meetingRegistration So you can register 10 people in a meeting. meetingRegistration has the Name and Gender fields (for example).

I have a “calendar view” on my site that displays all upcoming events, as well as the number of words for each event.

I am currently using Linq for Sql to output 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)
    });

(the actual request is half a page long) Since the use of the anonymous type occurs, I cannot extract it into the method (since I have several different options for presenting the calendar, each information is different, and I do not want to create a new class for each class).

Any suggestions for improving this? Is a database view the answer? Or do I need to continue and create named-type?

Any feedback / suggestions are welcome. My DataLayer is huge, I want to crop it, I just don’t know how.

Pointers to good reading will also be good.

Answer 1

I would expand your class Meetingsby adding 2 properties:

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

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

Delayed 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();

Registrations Meeting:

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

:

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
}