public IEnumerable <PlayerStint> GetBattingStintByLastNameAndYear(string lastName, int year)
{
using (var db = new FantasyBaseballDbContext())
{
var q = (from b in db.BattingStint.AsNoTracking()
where b.Year == year
join p in db.Person.AsNoTracking()
on b.PersonId equals p.PersonId
where p.LastName.Contains(lastName)
join t in db.Team.AsNoTracking()
on b.TeamId equals t.TeamId
join f in db.FieldingStint.AsNoTracking()
on new { b.PersonId, b.Stint, b.Year }
equals new { f.PersonId, f.Stint, f.Year }
into fs
from f in fs.DefaultIfEmpty()
select(new
{
BattingStint = b,
FieldingStint = f,
Person = p,
Team = t
})).ToList();
return(q.GroupBy(x => x.BattingStint.BattingStintId).Select(x => new PlayerStint
{
BattingStint = x.First().BattingStint,
Person = x.First().Person,
Team = x.First().Team,
FieldingStints = x.Select(y => y.FieldingStint)
}).ToList());
}
}
public PlayerStint GetRandomAllStarPitchingStintByMinimumGamesStartedAndMinimumInningsPitchedOuts(int minimumGamesStarted, int minimumInningsPitchedOuts)
{
using (var db = new FantasyBaseballDbContext())
{
var q1 = (from a in db.AllStar.AsNoTracking()
join pi in db.PitchingStint.AsNoTracking()
on new { a.PersonId, a.Year, a.TeamId }
equals new { pi.PersonId, pi.Year, pi.TeamId }
where pi.GamesStarted >= minimumGamesStarted && pi.InningsPitchedOuts >= minimumInningsPitchedOuts
select pi);
var count = q1.Count();
var index = new Random().Next(count);
var pitchingStint = q1.Skip(index).FirstOrDefault();
if (pitchingStint == null)
{
return(null);
}
var q2 = (from pi in db.PitchingStint.AsNoTracking()
where pi.PitchingStintId == pitchingStint.PitchingStintId
join pe in db.Person.AsNoTracking()
on pi.PersonId equals pe.PersonId
join t in db.Team.AsNoTracking()
on pi.TeamId equals t.TeamId
join b in db.BattingStint.AsNoTracking()
on new { pi.PersonId, pi.Stint }
equals new { b.PersonId, b.Stint }
join f in db.FieldingStint.AsNoTracking()
on new { pi.PersonId, pi.Stint, pi.Year }
equals new { f.PersonId, f.Stint, f.Year }
into fs
from f in fs.DefaultIfEmpty()
select(new
{
BattingStint = b,
FieldingStint = f,
Person = pe,
PitchingStint = pi,
Team = t
})).ToList();
return(new PlayerStint
{
BattingStint = q2.First().BattingStint,
Person = q2.First().Person,
PitchingStint = q2.First().PitchingStint,
Team = q2.First().Team,
FieldingStints = q2.GroupBy(q => q.FieldingStint.FieldingStintId).Select(y => y.First().FieldingStint)
});
}
}
public PlayerStint GetRandomAllStarBattingStintByPositionMinimumGamesPlayedAndMinimumAtBats(string position, int minimumGamesPlayed, int minimumAtBats)
{
using (var db = new FantasyBaseballDbContext())
{
var q1 = (from a in db.AllStar.AsNoTracking()
join f in db.FieldingStint.AsNoTracking()
on new { a.PersonId, a.Year, a.TeamId }
equals new { f.PersonId, f.Year, f.TeamId }
where f.Position == position && f.Games >= minimumGamesPlayed
join b in db.BattingStint.AsNoTracking()
on new { f.PersonId, f.Stint, f.Year }
equals new { b.PersonId, b.Stint, b.Year }
where b.AtBats >= minimumAtBats
select b);
var count = q1.Count();
var index = new Random().Next(count);
var battingStint = q1.Skip(index).FirstOrDefault();
if (battingStint == null)
{
return(null);
}
var q2 = (from b in db.BattingStint.AsNoTracking()
where b.BattingStintId == battingStint.BattingStintId
join p in db.Person.AsNoTracking()
on b.PersonId equals p.PersonId
join t in db.Team.AsNoTracking()
on b.TeamId equals t.TeamId
join fi in db.FieldingStint.AsNoTracking()
on new { b.PersonId, b.Stint, b.Year }
equals new { fi.PersonId, fi.Stint, fi.Year }
into fs
from fi in fs.DefaultIfEmpty()
select(new
{
BattingStint = b,
FieldingStint = fi,
Person = p,
Team = t
})).ToList();
return(new PlayerStint
{
BattingStint = q2.First().BattingStint,
Person = q2.First().Person,
Team = q2.First().Team,
FieldingStints = q2.Select(y => y.FieldingStint)
});
}
}
public IEnumerable <Franchise> GetActiveFranchiseNames()
{
using (var db = new FantasyBaseballDbContext())
{
return((from f in db.Franchise.AsNoTracking()
where f.Active == true
select new Franchise
{
FranchiseId = f.FranchiseId,
FranchiseName = f.FranchiseName
}).ToList());
}
}
public IEnumerable <Team> GetAllTeamNamesAndYears()
{
using (var db = new FantasyBaseballDbContext())
{
return((from t in db.Team.AsNoTracking()
select new Team
{
Name = t.Name,
TeamId = t.TeamId,
Year = t.Year
}).ToList());
}
}
public Team GetTeamWithPlayers(int teamId)
{
using (var db = new FantasyBaseballDbContext())
{
var q = (from t in db.Team.AsNoTracking()
where t.TeamId == teamId
join pi in db.PitchingStint.AsNoTracking()
on t.TeamId equals pi.TeamId
into pis
from pi in pis.DefaultIfEmpty()
join b in db.BattingStint.AsNoTracking()
on t.TeamId equals b.TeamId
into bs
from b in bs.DefaultIfEmpty()
join f in db.FieldingStint.AsNoTracking()
on t.TeamId equals f.TeamId
into fs
from f in fs.DefaultIfEmpty()
join pe in db.Person.AsNoTracking()
on f.PersonId equals pe.PersonId
into pes
from pe in pes.DefaultIfEmpty()
select(new
{
BattingStint = b,
FieldingStint = f,
Person = pe,
PitchingStint = pi,
Team = t
})).ToList();
var team = q.First().Team;
team.BattingStint = q.GroupBy(s => s.BattingStint.BattingStintId).Select(s => s.First().BattingStint).ToList();
team.PitchingStint = q.GroupBy(s => s.PitchingStint.PitchingStintId).Select(s => s.First().PitchingStint).ToList();
team.FieldingStint = q.GroupBy(s => s.FieldingStint.FieldingStintId).Select(s => s.First().FieldingStint).ToList();
team.PlayerStints = q.GroupBy(p => p.Person.PersonId).Select(p => p.First()).Select(p => new PlayerStint
{
BattingStint = team.BattingStint.Where(s => s.PersonId == p.Person.PersonId).CombineSamePersonTeamAndYearBattingStints(),
Person = p.Person,
PitchingStint = team.PitchingStint.Where(s => s.PersonId == p.Person.PersonId).CombineSamePersonTeamAndYearPitchingStints(),
FieldingStints = team.FieldingStint.Where(s => s.PersonId == p.Person.PersonId).CombineSamePersonTeamAndYearFieldingStints().ToList(),
Team = team
}).ToList();
return(team);
}
}
public IEnumerable <PlayerStint> GetBestPitchingStintsByPositionFranchiseMinimumGamesPlayedAndMinimumAtBats(int count, int franchiseId, int minimumGamesStarted, int minimumInningsPitchedOuts)
{
using (var db = new FantasyBaseballDbContext())
{
var q1 = (from pi in db.PitchingStint.AsNoTracking()
where pi.GamesStarted >= minimumGamesStarted && pi.InningsPitchedOuts >= minimumInningsPitchedOuts
join t in db.Team.AsNoTracking()
on pi.TeamId equals t.TeamId
join fr in db.Franchise.AsNoTracking()
on t.FranchiseId equals fr.FranchiseId
where fr.FranchiseId == franchiseId
orderby pi.WalksAndHitsPerInningsPitchedPlusEarnedRunAverage
select pi).Take(count * 3).ToList();
var pitchers = q1.GroupBy(q => q.PersonId).Select(g => g.First()).Take(count);
var q2 = (from pi in pitchers
join pe in db.Person.AsNoTracking()
on pi.PersonId equals pe.PersonId
join t in db.Team.AsNoTracking()
on pi.TeamId equals t.TeamId
join b in db.BattingStint.AsNoTracking()
on new { pi.PersonId, pi.Stint, pi.Year }
equals new { b.PersonId, b.Stint, b.Year }
join f in db.FieldingStint.AsNoTracking()
on new { pi.PersonId, pi.Stint, pi.Year }
equals new { f.PersonId, f.Stint, f.Year }
into fs
from f in fs.DefaultIfEmpty()
select(new
{
BattingStint = b,
FieldingStint = f,
Person = pe,
PitchingStint = pi,
Team = t
})).ToList();
return(q2.GroupBy(q => q.PitchingStint.PitchingStintId).Select(q => new PlayerStint
{
BattingStint = q.First().BattingStint,
Person = q.First().Person,
PitchingStint = q.First().PitchingStint,
Team = q.First().Team,
FieldingStints = q.Select(y => y.FieldingStint)
}));
}
}
public IEnumerable <PlayerStint> GetBestOPSBattingStintsByPositionFranchiseMinimumGamesPlayedAndMinimumAtBats(int count, string position, int franchiseId, int minimumGamesPlayed, int minimumAtBats)
{
using (var db = new FantasyBaseballDbContext())
{
var q1 = (from f in db.FieldingStint.AsNoTracking()
where f.Position == position && f.Games >= minimumGamesPlayed
join t in db.Team.AsNoTracking()
on f.TeamId equals t.TeamId
join fr in db.Franchise.AsNoTracking()
on t.FranchiseId equals fr.FranchiseId
where fr.FranchiseId == franchiseId
join b in db.BattingStint.AsNoTracking()
on new { f.PersonId, f.Stint, f.Year }
equals new { b.PersonId, b.Stint, b.Year }
where b.AtBats >= minimumAtBats
orderby b.OnBasePlusSlugging descending
select b).Take(count * 5).ToList();
var batters = q1.GroupBy(q => q.PersonId).Select(g => g.First()).Take(count);
var q2 = (from b in batters
join p in db.Person.AsNoTracking()
on b.PersonId equals p.PersonId
join t in db.Team.AsNoTracking()
on b.TeamId equals t.TeamId
join fi in db.FieldingStint.AsNoTracking()
on new { b.PersonId, b.Stint, b.Year }
equals new { fi.PersonId, fi.Stint, fi.Year }
into fs
from fi in fs.DefaultIfEmpty()
select(new
{
BattingStint = b,
FieldingStint = fi,
Person = p,
Team = t
})).ToList();
return(q2.GroupBy(q => q.BattingStint.BattingStintId).Select(q => new PlayerStint
{
BattingStint = q.First().BattingStint,
Person = q.First().Person,
Team = q.First().Team,
FieldingStints = q.Select(y => y.FieldingStint)
}));
}
}
public void LoadSingleGame()
{
using (var db = new FantasyBaseballDbContext())
{
// uncomment to recreate and fill db every time
//db.Database.EnsureDeleted();
db.Database.EnsureCreated();
if (db.BattingStint.Any())
{
return;
}
}
//AddEntities<Park>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\ParkData.txt");
AddEntities <Franchise>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\FranchiseData.txt");
AddEntities <League>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\LeagueData.txt");
AddEntities <Division>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\DivisionData.txt");
AddEntities <Team>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\TeamData.txt");
AddEntities <Person>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\PersonData.txt");
//AddEntities<School>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\SchoolData.txt");
AddEntities <HallOfFameMember>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\HallOfFameMemberData.txt");
//AddEntities<PostseasonSeries>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\PostseasonSeriesData.txt");
//AddEntities<Salary>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\SalaryData.txt");
//AddEntities<ParkStint>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\ParkStintData.txt");
//AddEntities<ManagerStint>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\ManagerStintData.txt");
//AddEntities<CollegeStint>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\CollegeStintData.txt");
AddEntities <BattingStint>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\BattingStintData.txt");
//AddEntities<BattingPostseasonRound>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\BattingPostseasonRoundData.txt");
AddEntities <PitchingStint>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\PitchingStintData.txt");
//AddEntities<PitchingPostseasonRound>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\PitchingPostseasonRoundData.txt");
AddEntities <FieldingStint>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\FieldingStintData.txt");
//AddEntities<FieldingOutfieldStint>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\FieldingOutfieldStintData.txt");
//AddEntities<FieldingPostseasonRound>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\FieldingPostseasonRoundData.txt");
//AddEntities<Award>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\AwardData.txt");
//AddEntities<AwardVoting>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\AwardVotingData.txt");
//AddEntities<Appearances>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\AppearancesData.txt");
AddEntities <AllStar>("C:\\Users\\320079761\\source\\repos\\FantasyBaseballSimulator\\FantasyBaseball.Repository\\DatabaseLoaderFiles\\AllStarData.txt");
}
private void AddEntities <T>(string filePath)
{
var count = 1;
string line;
var file = new StreamReader(filePath);
var db = new FantasyBaseballDbContext();
while ((line = file.ReadLine()) != null)
{
var entity = line.CreateEntity <T>();
db.Add(entity);
if (count % 600 == 0)
{
db.Database.OpenConnection();
db.Database.ExecuteSqlRaw($"SET IDENTITY_INSERT {typeof(T).FullName.Replace("FantasyBaseball.Entities.Models.", "")} ON");
db.SaveChanges();
db.Database.ExecuteSqlRaw($"SET IDENTITY_INSERT {typeof(T).FullName.Replace("FantasyBaseball.Entities.Models.", "")} OFF");
db.Database.CloseConnection();
db.Dispose();
db = new FantasyBaseballDbContext();
}
count++;
}
db.Database.OpenConnection();
db.Database.ExecuteSqlRaw($"SET IDENTITY_INSERT {typeof(T).FullName.Replace("FantasyBaseball.Entities.Models.", "")} ON");
db.SaveChanges();
db.Database.ExecuteSqlRaw($"SET IDENTITY_INSERT {typeof(T).FullName.Replace("FantasyBaseball.Entities.Models.", "")} OFF");
db.Database.CloseConnection();
db.Dispose();
file.Close();
}
