Linq2sql count grouping vote entity query problems, can't get properties once grouped?

Question

I'm building a music voting application, similar to stack overflow almost.

I have 3 tables, Charts, ChartItems, Votes.

I'm trying to bring back a list of chartitems, which link to a single chart, with the number of votes for each chart item counted.

This is what I am trying currently

var firstList = from chartItem in db.ChartItems
                        join vote in db.Votes on chartItem.ixChartId equals vote.ixChartId into j1
                        where chartItem.ixChartId == id                            
                        from j2 in j1.DefaultIfEmpty()
                        group j2 by chartItem.ixChartItemId into grouped                   
                        select new ChartItemWithVotes
                        {                             
                          totalVotes =  grouped.Count(t => t.ixVoteId != null),

                        //CANT GET OTHER PROPS HERE

                        };

The problem is once I have performed the grouping, I can't get any of the other properties I need from the join to populate the model. For example each ChartItem should have a title, id etc...

I have created a ViewModel to hold all the properties that I need, called ChartItemWithVotes(includes all entity values + int totalVotes)

Can anyone help me with where I am going wrong.

In the end I am looking for this

Chart Name

Votes Name

20 - ChartItemname

15 - ChartItemname

12 - ChartITemName

Answer 1

I think you might want to create a query resulting in chartItemId and total votes and based on that select all chart items from db.ChartItems. Otherwise it might get unreadable quite quickly. It should not suffer performance-wise to do it like that either. Deferred execution in linq should make sure the db only takes a single hit.

Your design seems to be flawed in that your Votes table has a foreign key of Chart table while it should be pointing to ChartItem table instead. Then my suggestion would make even more sense and you'd be able to solve everything with a regular GroupBy clause.

It should be like Chart -> ChartItem -> Votes.

It looks like your current design is more like ChartItem <- Chart -> Votes

How about this approach:

var result = db.Votes
   .Where(v => v.chartItemId != null)
   .GroupBy(v => v.chartItemId)
   .Join(db.ChartItems, v => v.Key, c => c.chartItemId, (v, c) => new {Votes = v.Count(), c.Title, c.Id});

Answer 2

This may not be the answer you're looking for, but if the appropriate relationships are set up in SQL server, and therefore have been imported into the DBML, you should be able to do the following:

var chartvotes = from chartItem in db.ChartItems
    select new {
    ChartItem = chartItem,
    TotalVotes = chartItem.Votes.Count()
};

Answer 3

do nested linq queries they work well, I use them in my applications, and since your not using any a where clause thats directly related to your votes table in your main query it should work without any issues.

var firstList = from chartItem in db.ChartItems
                where chartItem.ixChartId == id                            
                select new ChartItemWithVotes
                {
                    chartItemName = chartItem.asName,
                    totalVotes =  (from votes in db.Votes
                                  where vote.ixChart == chartItem.ixChart
                                  select vote.ixVoteId).Count()
                };