Concentrate many rows into list using a DataReader

Question

I have the following tables:

TEAMS:

ID          Name
------      ---------
1           Giants
2           Yankees
3           Cool Guys

PLAYERS:

ID          Name         Team      IQ        SomeOtherDetail
------      ---------    -------   ------    ----------------
1           Bob          1         100       Oklahoma
2           Joe          1         80        Who knows?
3           Sue          2         130       Who cares?
4           Fred         2         76        42
5           Ed           2         90        Yes, please.
6           Schultz      3         314       :-)

My code contains the flowing class:

public class Team
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Player> Players { get; set; }
}

Using good old stringy querying and a DataReader, I'm trying to get a list of all the teams, players included.

Is there a way to do this with a single query?

The solutions for this question gets pretty close;
I was considering getting list of players as single string and then splitting them, but it doesn't help because I need all the the players' details (names, IDs, etc.), and besides, it feels like a dirty trick.

Another thing I considered is querying like so:

select *
from TEAMS join PLAYERS
on TEAMS.ID = PLAYERS.Team

...(thus getting extra rows) and then concentrating results using linq, but I'm not sure how efficient this is.

So, any bright ideas?

Daniel Kelley · Accepted Answer · 2013-01-21T20:03:51.953

3

select t.ID as TeamID, t.Name as TeamName, p.ID as PlayerID, p.Name as PlayerName
     , p.Team as TeamName, p.IQ, p.SomeOtherDetail
from Team t
inner join Players p on t.ID = p.Team

That would give you the basic SQL. Then you'd just need to loop through the results, check if the team already exists in your list and add it if not, and then add the player to the list of players.

Does that answer your question?

edited Jan 21 '13 at 20:03

answered Jan 21 '13 at 19:57

Daniel Kelley

7,579
6
42
50

It is one of the options I considered. Is it truly the best option, or is there a better way? – Yehuda Shapira Jan 21 '13 at 19:59
2

It's the way I would do it. There are some very bright people on this site so maybe there is a better way. – Daniel Kelley Jan 21 '13 at 20:00

D Stanley · Answer 2 · 2013-01-21T20:29:37.740

Your query

SELECT *
FROM teams 
INNER JOIN players
    ON teams.ID = players.Team

is what you want. It does not give you extra rows, it gives you more columns. The columns from both tables would be in your results set. It will be much more efficient that trying to concatenate two queries yourself.

Your pseudo-code for generating a list of Teams could be:

while dr.Read()
   if (Teams.Contains(dr["Team"])
       add team to Teams
   find Team by ID
   add Player to Team

As an alternative, you could read the entire result set into a DataTable using DataTable.Load() and then use a Linq query with a group by clause to get the entire set in one shot.

Concentrate many rows into list using a DataReader

2 Answers2