public IEnumerable <Team> ReadAll()
{
var teams = new List <Team>();
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var heroQuery = @"SELECT Id, TeamId
FROM Heroes";
var heroCommand = new SqlCommand(heroQuery, connection);
var teamIdsWithHeroIds = new Dictionary <Guid, List <Guid> >();
using (var reader = heroCommand.ExecuteReader())
{
while (reader.Read())
{
var heroId = (Guid)reader["Id"];
var teamId = reader["TeamId"] == DBNull.Value ? null : (Guid?)reader["TeamId"];
if (teamId.HasValue)
{
if (!teamIdsWithHeroIds.ContainsKey(teamId.Value))
{
teamIdsWithHeroIds[teamId.Value] = new List <Guid>();
}
teamIdsWithHeroIds[teamId.Value].Add(heroId);
}
}
}
var teamQueryString = "SELECT Id, Name FROM Teams";
var teamCommand = new SqlCommand(teamQueryString, connection);
using (var reader = teamCommand.ExecuteReader())
{
while (reader.Read())
{
var heroes = new List <Hero>();
var teamId = (Guid)reader["Id"];
var teamName = (string)reader["Name"];
foreach (var heroId in teamIdsWithHeroIds.GetValueOrDefault(teamId) ?? new List <Guid>())
{
var maybeHero = HeroAdapter.Read(heroId);
if (maybeHero.HasValue) // should always fire
{
heroes.Add(maybeHero.Value);
}
}
teams.Add(new Team(teamId, teamName, heroes));
}
}
}
return(teams);
}
public Hero?Read(Guid id)
{
var isPresent = Db.KeyExists($"hero:{id.ToString()}");
if (!isPresent)
{
return(null);
}
var heroDetails = Db.HashGet($"hero:{id.ToString()}", new RedisValue[] { "name", "location" });
var name = heroDetails[0];
var location = heroDetails[1];
var powers = new List <Power>();
var powerIds = Db.SetMembers($"heropowers:{id.ToString()}");
foreach (var powerId in powerIds)
{
var power = PowerAdapter.Read(Guid.Parse(powerId));
if (power.HasValue) // should always fire, if heropowers integrity is maintained
{
powers.Add(power.Value);
}
}
return(new Hero(id, name, location, powers));
}
public Team?Read(Guid id)
{
var isPresent = Db.KeyExists($"team:{id.ToString()}");
if (!isPresent)
{
return(null);
}
var name = Db.HashGet($"team:{id.ToString()}", "name");
var members = new List <Hero>();
var heroIds = Db.SetMembers($"teammembers:{id.ToString()}");
// n queries to load heroes; can we do better?
foreach (var heroId in heroIds)
{
var hero = HeroAdapter.Read(Guid.Parse(heroId));
if (hero.HasValue) // should always fire, assuming consistency of teammembers set
{
members.Add(hero.Value);
}
}
return(new Team(id, name, members));
}
