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

Posted by user93422 on Stack Overflow See other posts from Stack Overflow or by user93422
Published on 2010-04-20T18:16:25Z Indexed on 2010/04/22 0:13 UTC
Read the original article Hit count: 134

Filed under:

linq-to-sql

|

data-access

|

data-access-layer

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.

© Stack Overflow or respective owner

Related posts about linq-to-sql

how can i page my linq to sql result set

as seen on Stack Overflow - Search for 'Stack Overflow'
Remembering MySQL could use the instruction "limit" to indicate where I was starting my result set and how many wanted to have included. Select * FROM Users Limit [start], [Length] How can I do this in LinqToSql >>> More
LINQ to SQL and missing Many to Many EntityRefs

as seen on West-Wind - Search for 'West-Wind'
Ran into an odd behavior today with a many to many mapping of one of my tables in LINQ to SQL. Many to many mappings aren’t transparent in LINQ to SQL and it maps the link table the same way the SQL schema has it when creating one. In other words LINQ to SQL isn’t smart about many to many mappings… >>> More
Comparing LINQ to SQL vs the classic SqlCommand

as seen on Dot net Slackers - Search for 'Dot net Slackers'
tweetmeme_url = 'http://alpascual.com/blog/comparing-linq-to-sql-vs-the-classic-sqlcommand/';tweetmeme_source = 'alpascual';When you are coming from using SqlCommand and SqlConnection is difficult to move to another library for your database needs. For those people still in the limbo to make the decision… >>> More
Comparing LINQ to SQL vs the classic SqlCommand

as seen on Dot net Slackers - Search for 'Dot net Slackers'
tweetmeme_url = 'http://alpascual.com/blog/comparing-linq-to-sql-vs-the-classic-sqlcommand/';tweetmeme_source = 'alpascual';When you are coming from using SqlCommand and SqlConnection is difficult to move to another library for your database needs. For those people still in the limbo to make the decision… >>> More
LINQ to SQL Profiler

as seen on Dot net Slackers - Search for 'Dot net Slackers'
In this article we will be taking a look at the new LINQ to SQL Profiler from HibernatingRhinos. This tool gives you a view into the goings on of LINQ to SQL. Not only does it allow you to see the SQL that is generated by your LINQ queries but it also shows you information about your connections… >>> More

Related posts about data-access

Data Access Layer with Enterprise Library in Mobile Applications

as seen on Stack Overflow - Search for 'Stack Overflow'
How can I use Enterprise Library to do a Data Access Layer in c# for Sql CE and .NET Compact Framework? I haven't found clear examples. I would like to learn Enterprise Library but any example will be appreciated. >>> More
Data access pattern

as seen on Stack Overflow - Search for 'Stack Overflow'
I need some advice on what kind of pattern(s) I should use for pushing/pulling data into my application. I'm writing a rule-engine that needs to hold quite a large amount of data in-memory in order to be efficient enough. I have some rather conflicting requirements; It is not acceptable for the… >>> More
Data access pattern, combining push and pull?

as seen on Stack Overflow - Search for 'Stack Overflow'
I need some advice on what kind of pattern(s) I should use for pushing/pulling data into my application. I'm writing a rule-engine that needs to hold quite a large amount of data in-memory in order to be efficient enough. I have some rather conflicting requirements; It is not acceptable for the… >>> More
How should I architect my Model and Data Access layer objects in my website?

as seen on Programmers - Search for 'Programmers'
I've been tasked with designing Data layer for a website at work, and I am very interested in architecture of code for the best flexibility, maintainability and readability. I am generally acutely aware of the value in completely separating out my actual Models from the Data Access layer, so that… >>> More
How to Use CslaGen to Generate CSLA Data Access Layer Code

as seen on Code Project - Search for 'Code Project'
This article shows how to quickly create the CSLA DAL files for a mother/daughter relationship (hierarchical data) using CslaGen. >>> More