public static async Task <List <Game> > GetGamesByIds(this MlbDatabase db, IEnumerable <int> ids)
{
return(await db.Games
.FullIncludeGame()
.Where(g => ids.Contains(g.GameId))
.ToListAsync());
}
public static async Task <Game> GetTeamsLastGameBeforeDate(this MlbDatabase db, int teamId, DateTime before)
{
return(await db.Games.AsNoTracking()
.Where(g => g.Date < before && (g.Home.Team.TeamId == teamId || g.Away.Team.TeamId == teamId) && g.Status == GameStatus.COMPLETE)
.FullIncludeGame()
.OrderByDescending(g => g.Date)
.FirstOrDefaultAsync());
}
public static async Task <Team> GetTeamByName(this MlbDatabase db, string name)
{
string lower = name.ToLower();
return(await db.Teams.SingleOrDefaultAsync(b =>
b.Code.ToLower().Equals(lower) ||
b.FullName.ToLower().Contains(lower)));
}
public static void DeleteScoreboard(this MlbDatabase db, Scoreboard scoreboard)
{
// TODO: Add additional cleanup since cascades aren't enabled
db.BatterAppearances.RemoveRange(scoreboard.Games.SelectMany(p => p.Away.Batters));
db.BatterAppearances.RemoveRange(scoreboard.Games.SelectMany(p => p.Home.Batters));
db.PitcherAppearances.RemoveRange(scoreboard.Games.SelectMany(p => p.Away.Pitchers));
db.PitcherAppearances.RemoveRange(scoreboard.Games.SelectMany(p => p.Home.Pitchers));
db.TeamAppearances.RemoveRange(scoreboard.Games.Select(g => g.Away));
db.TeamAppearances.RemoveRange(scoreboard.Games.Select(g => g.Home));
db.Games.RemoveRange(scoreboard.Games);
db.Scoreboards.Remove(scoreboard);
}
public static async Task <Scoreboard> GetScoreboardById(this MlbDatabase db, int id)
{
var scoreboard = await db.Scoreboards
.Include(s => s.Games)
.SingleOrDefaultAsync(s => s.ScoreboardId == id);
if (scoreboard != null)
{
scoreboard.Games = await db.GetGamesByIds(scoreboard.Games.Select(g => g.GameId));
}
return(scoreboard);
}
public static async Task <RoadData> GetRoadData(this MlbDatabase db, int teamId, DateTime ending)
{
int numGames = 20;
int bullpenGames = 5;
var lastN = await db.Games.Where(g =>
(g.Home.Team.TeamId == teamId || g.Away.Team.TeamId == teamId) &&
(g.Date < ending && g.Date.Year == ending.Year))
.Include(g => g.Home.Team)
.Include(g => g.Home.Pitchers.Select(p => p.StatLine))
.Include(g => g.Away.Pitchers.Select(p => p.StatLine))
.OrderByDescending(g => g.Date)
.Take(numGames)
.ToListAsync();
int count = 0;
int inARow = 0;
int totalAway = 0;
int bullpenOuts = 0;
bool streakBroken = false;
foreach (var game in lastN.Where(g => g?.Home?.Team != null))
{
if (game.Home.Team.TeamId == teamId)
{
streakBroken = true;
if (count < bullpenGames)
{
bullpenOuts += game.Home.BullpenOuts;
}
}
else
{
if (!streakBroken)
{
inARow++;
}
totalAway++;
if (count < bullpenGames)
{
bullpenOuts += game.Away.BullpenOuts;
}
}
count++;
}
return(new RoadData()
{
LastN = numGames,
DaysInARow = inARow,
PercentageOfLast = (double)totalAway / (double)numGames,
RecentBullpenOuts = bullpenOuts,
});
}
public static async Task <Scoreboard> GetScoreboardByDate(this MlbDatabase db, DateTime date)
{
var scoreboard = await db.Scoreboards
.Include(s => s.Games)
.SingleOrDefaultAsync(s => s.Date.Day == date.Day &&
s.Date.Month == date.Month &&
s.Date.Year == date.Year);
if (scoreboard != null)
{
scoreboard.Games = await db.GetGamesByIds(scoreboard.Games.Select(g => g.GameId));
}
return(scoreboard);
}
public static async Task <PitcherAppearance> GetPitcherAppearanceById(this MlbDatabase db, int id)
{
return(await db.PitcherAppearances
.Include(p => p.Pitcher)
.Include(p => p.StatLine)
.Include(p => p.PreGameSnapshot.BasesEmpty)
.Include(p => p.PreGameSnapshot.BasesLoaded)
.Include(p => p.PreGameSnapshot.Career)
.Include(p => p.PreGameSnapshot.Season)
.Include(p => p.PreGameSnapshot.vsLefties)
.Include(p => p.PreGameSnapshot.vsRighties)
.Include(p => p.PreGameSnapshot.Month)
.Include(p => p.PreGameSnapshot.MenOnBase)
.Include(p => p.PreGameSnapshot.RunnersInScoringPosition)
.Include(p => p.PreGameSnapshot.BasesLoaded)
.SingleOrDefaultAsync(p => p.PitcherAppearanceId == id));
}
public static async Task <PitcherAppearance> GetPitchersLastAppearance(this MlbDatabase db, int pitcherId, DateTime beforeDate)
{
return(await db.PitcherAppearances.AsNoTracking()
.Where(p => p.Pitcher.PitcherId == pitcherId && p.Date < beforeDate && p.Date.Year == beforeDate.Year)
.OrderByDescending(p => p.Date).ThenByDescending(p => p.PitcherAppearanceId)
.Include(p => p.Pitcher)
.Include(p => p.StatLine)
.Include(p => p.PreGameSnapshot.BasesEmpty)
.Include(p => p.PreGameSnapshot.BasesLoaded)
.Include(p => p.PreGameSnapshot.Career)
.Include(p => p.PreGameSnapshot.Season)
.Include(p => p.PreGameSnapshot.vsLefties)
.Include(p => p.PreGameSnapshot.vsRighties)
.Include(p => p.PreGameSnapshot.Month)
.Include(p => p.PreGameSnapshot.MenOnBase)
.Include(p => p.PreGameSnapshot.RunnersInScoringPosition)
.Include(p => p.PreGameSnapshot.BasesLoaded)
.FirstOrDefaultAsync());
}
public static async Task CreateScoreboard(this MlbDatabase db, Scoreboard scoreboard)
{
// Remove any games with same gameID just in case
var gameIds = scoreboard.Games.Select(g => g.GameId);
db.Games.RemoveRange(await db.Games.Where(g => gameIds.Contains(g.GameId)).ToListAsync());
// Avoid edge case where player plays two different teams in same day
var allBatters = scoreboard.Games.SelectMany(s => s.Away.Batters).Union(scoreboard.Games.SelectMany(s => s.Home.Batters))
.GroupBy(b => b.Batter.BatterId).ToDictionary(g => g.First().Batter.BatterId, g => g.First().Batter);
var allPitchers = scoreboard.Games.SelectMany(s => s.Away.Pitchers).Union(scoreboard.Games.SelectMany(s => s.Home.Pitchers))
.GroupBy(b => b.Pitcher.PitcherId).ToDictionary(g => g.First().Pitcher.PitcherId, g => g.First().Pitcher);
// Check if there are existing players we can use
foreach (var game in scoreboard.Games)
{
await ReplaceDuplicates(db, game.Home, allBatters, allPitchers);
await ReplaceDuplicates(db, game.Away, allBatters, allPitchers);
db.Games.Add(game);
}
db.Scoreboards.Add(scoreboard);
}
public static async Task <Pitcher> GetPitcherById(this MlbDatabase db, int id)
{
return(await db.Pitchers
.SingleOrDefaultAsync(p => p.PitcherId == id));
}
public static async Task <Batter> GetBatterById(this MlbDatabase db, int id)
{
return(await db.Batters
.SingleOrDefaultAsync(b => b.BatterId == id));
}
private static async Task ReplaceDuplicates(this MlbDatabase db, TeamAppearance appearance, Dictionary <int, Batter> batterSet, Dictionary <int, Pitcher> pitcherSet)
{
// Replace player entries
appearance.Batters = appearance.Batters.GroupBy(b => b.Batter.BatterId).Select(g => g.First()).ToList();
var batterIDs = appearance.Batters.Select(b => b.Batter.BatterId);
var existingBatters = (await db.Batters.Where(b => batterIDs.Contains(b.BatterId))
.ToListAsync())
.ToDictionary(b => b.BatterId, b => b);
foreach (var batter in appearance.Batters)
{
batter.Batter = batterSet[batter.Batter.BatterId];
if (existingBatters.ContainsKey(batter.Batter.BatterId))
{
var existingBatter = existingBatters[batter.Batter.BatterId];
if ((existingBatter.HeightInches <= 0 || existingBatter.WeightPounds <= 0) &&
(batter.Batter.HeightInches > 0 && batter.Batter.WeightPounds > 0))
{
existingBatter.Bats = batter.Batter.Bats;
existingBatter.FirstName = batter.Batter.FirstName;
existingBatter.LastName = batter.Batter.LastName;
existingBatter.HeightInches = batter.Batter.HeightInches;
existingBatter.JerseyNumber = batter.Batter.JerseyNumber;
existingBatter.NaturalPosition = batter.Batter.NaturalPosition;
existingBatter.Throws = batter.Batter.Throws;
existingBatter.WeightPounds = batter.Batter.WeightPounds;
}
batter.Batter = existingBatter;
}
}
appearance.Pitchers = appearance.Pitchers.GroupBy(p => p.Pitcher.PitcherId).Select(g => g.First()).ToList();
var pitcherIDs = appearance.Pitchers.Select(p => p.Pitcher.PitcherId);
var existingPitchers = (await db.Pitchers.Where(p => pitcherIDs.Contains(p.PitcherId))
.ToListAsync())
.ToDictionary(p => p.PitcherId, p => p);
foreach (var pitcher in appearance.Pitchers)
{
pitcher.Pitcher = pitcherSet[pitcher.Pitcher.PitcherId];
if (existingPitchers.ContainsKey(pitcher.Pitcher.PitcherId))
{
var existingPitcher = existingPitchers[pitcher.Pitcher.PitcherId];
if ((existingPitcher.HeightInches <= 0 || existingPitcher.WeightPounds <= 0) &&
(pitcher.Pitcher.HeightInches > 0 && pitcher.Pitcher.WeightPounds > 0))
{
existingPitcher.FirstName = pitcher.Pitcher.FirstName;
existingPitcher.LastName = pitcher.Pitcher.LastName;
existingPitcher.HeightInches = pitcher.Pitcher.HeightInches;
existingPitcher.JerseyNumber = pitcher.Pitcher.JerseyNumber;
existingPitcher.Throws = pitcher.Pitcher.Throws;
existingPitcher.WeightPounds = pitcher.Pitcher.WeightPounds;
}
pitcher.Pitcher = existingPitcher;
}
}
// Replace team entry
var existingTeam = await db.Teams.SingleOrDefaultAsync(t => t.TeamId == appearance.Team.TeamId);
if (existingTeam != null)
{
appearance.Team = existingTeam;
}
}
public static async Task <Game> GetGameById(this MlbDatabase db, int id)
{
return(await db.Games
.FullIncludeGame()
.SingleOrDefaultAsync(g => g.GameId == id));
}
public static async Task <Standings> GetStandingsById(this MlbDatabase db, int id)
{
return(await db.Standings
.SingleOrDefaultAsync(s => s.StandingsId == id));
}
public static async Task <TeamAppearance> GetTeamAppearanceById(this MlbDatabase db, int id)
{
return(await db.TeamAppearances.SingleOrDefaultAsync(b => b.TeamAppearanceId == id));
}