A good(elegant) way to retrieve records with counts.
- by user93422
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.
