LINQ query distinct count with joined tables

Topics: Writing modules
May 25, 2011 at 6:59 AM

Having a bit of a problem getting this to work properly and could use a little help.

I have two models/tables that have a relationship:

    public class TeamMembersRecord
    {
        public virtual int Id { get; set; }
        public virtual ShowRecord Show { get; set; }
        public virtual SeasonRecord Season { get; set; }
        public virtual EpisodeRecord Episode { get; set; }
        public virtual TeamRecord Team { get; set; }
        public virtual CastRecord Cast { get; set; }
    }

    public class TeamRecord
    {
        public virtual int Id { get; set; }
        public virtual ShowRecord Show { get; set; }
        public virtual SeasonRecord Season { get; set; }
        public virtual string TeamName { get; set; }
        public virtual string TeamColor { get; set; }
    }

The relationship I'm concerned about here is TeamRecord Team

I have a LINQ query where I'm trying to get a distinct count of TeamRecord based on the rows in TeamMembersRecord. There are 3 records in TeamMembersRecord that all have a Team_Id of 1 (mapping back to the one record that is in TeamRecord). However I try to do a distinct count of TeamRecord, I get 3 instead of 1.

Here are a couple of things I've tried

                    showViewModel.teamCount = (from cast in _teammembersRepository.Table
                                               where cast.Show.ShowName == showName && cast.Season.SeasonNumber == showViewModel.thisSeason.SeasonNumber && cast.Episode.EpisodeNumber == showViewModel.thisEpisode.EpisodeNumber
                                               select cast.Team.Id).Distinct().Count();


                    showViewModel.teamCount = (from cast in _teammembersRepository.Table
                                               where cast.Show.ShowName == showName && cast.Season.SeasonNumber == showViewModel.thisSeason.SeasonNumber && cast.Episode.EpisodeNumber == showViewModel.thisEpisode.EpisodeNumber
                                               group cast by cast.Team.Id into grp
                                               select grp.Select(p => p.Team.Id)).Distinct().Count();
any suggestions on how to setup this query properly? Thanks!

May 25, 2011 at 1:44 PM
Edited May 25, 2011 at 1:45 PM

Edit: Didn't see what the select was doing...

May 27, 2011 at 10:27 PM

Still can't seem to figure out how to get a distinct count. Anyone have any ideas?

May 27, 2011 at 11:32 PM

I think I came up with something that should work. I actually split it into 2 queries and used the result of one as part of the Where clause in the second

        public int GetTeamCount(string showName, int seasonNumber, int episodeNumber)
        {
            List<int> allteams = (from teammembers in _teammembersRepository.Table
                                  where teammembers.Show.ShowName == showName && teammembers.Season.SeasonNumber == seasonNumber && teammembers.Episode.EpisodeNumber == episodeNumber
                                  select teammembers.Team.Id).ToList();

            int teamCount = (from teams in _teamRepository.Table
                             where teams.Show.ShowName == showName && teams.Season.SeasonNumber == seasonNumber && allteams.Contains(teams.Id)
                             select teams.Id).Distinct().Count();

            return teamCount;
        }

This seems to get me the correct number...I'll keep testing though.