public static IQueryable <OlympicEvent> FullOlympicEvents(this OfficeOlympicsDbEntities context)
{
return(context.OlympicEvents
.Include(obj => obj.EventType)
.Include(obj => obj.Records.Select(rec => rec.Competitor))
.Include(obj => obj.Records.Select(rec => rec.Witnesses.Select(wit => wit.Competitor))));
}
public Competitor GetCompetitorById(int competitorId)
{
using (var context = new OfficeOlympicsDbEntities())
{
return(context.Competitors.SingleOrDefault(obj => obj.Id == competitorId));
}
}
public void UpdateOlympicEvent(OlympicEvent olympicEvent)
{
using (var context = new OfficeOlympicsDbEntities())
{
var existingEvent = context.OlympicEvents.SingleOrDefault(obj => obj.Id == olympicEvent.Id);
if (existingEvent == null)
{
throw new InvalidOperationException($"Olympic Event '{olympicEvent.EventName}' doesn't exist.");
}
if (olympicEvent.Icon != null)
{
Func <string> generateIconPath = () => Path.Combine(_iconPath, existingEvent.IconFileName);
File.Delete(generateIconPath());
existingEvent.IconFileName = $"{Path.GetFileNameWithoutExtension(existingEvent.IconFileName)}{Path.GetExtension(olympicEvent.Icon.UploadedFileName)}";
File.WriteAllBytes(generateIconPath(), olympicEvent.Icon.Bytes);
}
existingEvent.EventName = olympicEvent.EventName;
existingEvent.EventTypeId = olympicEvent.EventTypeId;
existingEvent.Description = olympicEvent.Description;
existingEvent.Specification = olympicEvent.Specification;
existingEvent.IsActive = olympicEvent.IsActive;
context.SaveChanges();
}
}
public IEnumerable <EventType> GetEventTypes()
{
using (var context = new OfficeOlympicsDbEntities())
{
return(context.EventTypes.ToList());
}
}
public static IQueryable <Record> FullRecords(this OfficeOlympicsDbEntities context)
{
return(context.Records
.Include(obj => obj.OlympicEvent.EventType)
.Include(obj => obj.Competitor)
.Include(obj => obj.Witnesses.Select(wit => wit.Competitor)));
}
public bool NameIsUnique(OlympicEvent olympicEvent)
{
using (var context = new OfficeOlympicsDbEntities())
{
return(context.OlympicEvents.SingleOrDefault(obj => obj.EventName == olympicEvent.EventName && obj.Id != olympicEvent.Id) == null);
}
}
public void LogError(Exception ex)
{
using (var context = new OfficeOlympicsDbEntities())
{
LogErrorRecursive(context, ex);
context.SaveChanges();
}
}
public OlympicEvent GetOlympicEventById(int olympicEventId)
{
using (var context = new OfficeOlympicsDbEntities())
{
var olympicEvent = context.OlympicEvents.WithEventType().SingleOrDefault(obj => obj.Id == olympicEventId);
return(olympicEvent);
}
}
public void InsertCompetitor(Competitor competitor)
{
using (var context = new OfficeOlympicsDbEntities())
{
context.Competitors.Add(competitor);
context.SaveChanges();
}
}
public void InsertRecord(Record record)
{
using (var context = new OfficeOlympicsDbEntities())
{
context.Records.Add(record);
context.SaveChanges();
}
}
private int GetPageCount(OfficeOlympicsDbEntities context)
{
int totalErrors = context.Errors.Count();
return
(totalErrors % _pageSize == 0 ?
totalErrors / _pageSize :
totalErrors / _pageSize + 1);
}
public IEnumerable <Competitor> GetRecentlyAddedCompetitors()
{
using (var context = new OfficeOlympicsDbEntities())
{
return((from competitor in context.Competitors
where competitor.IsActive
orderby competitor.Id descending
select competitor).Take(5).ToList());
}
}
public IEnumerable <Competitor> GetCompetitors(bool includeDeleted)
{
using (var context = new OfficeOlympicsDbEntities())
{
return((from competitor in context.Competitors
where competitor.IsActive || includeDeleted
orderby competitor.IsActive descending,
competitor.LastName
select competitor).ToList());
}
}
public Quote GetRandomQuote()
{
using (var context = new OfficeOlympicsDbEntities())
{
int minQuoteId = context.Quotes.Min(obj => obj.Id);
int maxQuoteId = context.Quotes.Max(obj => obj.Id);
int randomQuoteId = _rng.Next(minQuoteId, maxQuoteId + 1);
return(context.Quotes.Single(obj => obj.Id == randomQuoteId));
}
}
public IEnumerable <OlympicEvent> GetRecentlyAddedOlympicEvents()
{
using (var context = new OfficeOlympicsDbEntities())
{
var olympicEvents = (from ev in context.OlympicEvents.WithEventType()
where ev.IsActive
orderby ev.DateAdded descending
select ev).Take(5).ToList();
return(olympicEvents);
}
}
public OlympicEvent GetRecordBoardEvent(int eventId)
{
using (var context = new OfficeOlympicsDbEntities())
{
context.Configuration.LazyLoadingEnabled = false;
var olympicEvent = context.OlympicEvents.Single(obj => obj.Id == eventId);
ExplicitLoadOlympicEvent(context, olympicEvent);
return(olympicEvent);
}
}
public IEnumerable <OlympicEvent> GetOlympicEvents(bool includeDeleted)
{
using (var context = new OfficeOlympicsDbEntities())
{
var olympicEvents = (from ev in context.OlympicEvents.WithEventType()
where ev.IsActive || includeDeleted
orderby ev.IsActive descending,
ev.EventTypeId,
ev.EventName
select ev).ToList();
return(olympicEvents);
}
}
public IEnumerable <Record> GetRecentRecords()
{
using (var context = new OfficeOlympicsDbEntities())
{
return((from record in context.FullRecords().AsParallel()
group record by record.OlympicEventId into groupedRecords
select
(from groupedRecord in groupedRecords
orderby groupedRecord.Score descending
select groupedRecord).FirstOrDefault() into bestScore
where bestScore.Competitor.IsActive && bestScore.OlympicEvent.IsActive
orderby bestScore.DateAchieved descending
select bestScore).Take(5).ToList());
}
}
private void ExplicitLoadOlympicEvent(OfficeOlympicsDbEntities context, OlympicEvent olympicEvent)
{
(from record in context.Entry(olympicEvent).Collection(e => e.Records).Query()
where record.Competitor.IsActive
group record by record.CompetitorId into groupedRecords
select groupedRecords.OrderByDescending(record => record.Score).FirstOrDefault() into record
orderby record.Score descending
select record).Take(3).Load();
context.Entry(olympicEvent).Reference(obj => obj.EventType).Load();
foreach (var record in olympicEvent.Records)
{
context.Entry(record).Reference(obj => obj.Competitor).Load();
}
}
public ErrorLog GetErrorLogPage(int pageNumber)
{
using (var context = new OfficeOlympicsDbEntities())
{
var errorLog = new ErrorLog();
errorLog.CurrentPage = pageNumber;
errorLog.TotalPages = GetPageCount(context);
errorLog.Errors = context.Errors
.OrderByDescending(error => error.Id)
.Skip((pageNumber - 1) * _pageSize)
.Take(_pageSize).ToList();
return(errorLog);
}
}
public IEnumerable <OlympicEvent> GetRecordBoard()
{
using (var context = new OfficeOlympicsDbEntities())
{
context.Configuration.LazyLoadingEnabled = false;
var olympicEvents = context.OlympicEvents.Where(obj => obj.IsActive).ToList();
foreach (var olympicEvent in olympicEvents)
{
ExplicitLoadOlympicEvent(context, olympicEvent);
}
return(olympicEvents.ToList());
}
}
public void DeleteOlympicEvent(OlympicEvent olympicEvent)
{
using (var context = new OfficeOlympicsDbEntities())
{
var existingEvent = context.OlympicEvents.SingleOrDefault(obj => obj.Id == olympicEvent.Id);
if (existingEvent == null)
{
throw new InvalidOperationException($"Olympic Event '{olympicEvent.EventName}' doesn't exist.");
}
existingEvent.IsActive = false;
context.SaveChanges();
}
}
public void DeleteCompetitor(Competitor competitor)
{
using (var context = new OfficeOlympicsDbEntities())
{
var existingCompetitor = context.Competitors.SingleOrDefault(obj => obj.Id == competitor.Id);
if (existingCompetitor == null)
{
throw new InvalidOperationException($"Competitor '{competitor.FirstName} {competitor.LastName}' doesn't exist.");
}
existingCompetitor.IsActive = false;
context.SaveChanges();
}
}
public void InsertOlympicEvent(OlympicEvent olympicEvent)
{
using (var context = new OfficeOlympicsDbEntities())
{
Guid iconFileNameGuid = Guid.NewGuid();
olympicEvent.IconFileName = $"{iconFileNameGuid}{Path.GetExtension(olympicEvent.Icon?.UploadedFileName ?? "default.png")}";
if (olympicEvent.Icon != null)
{
File.WriteAllBytes(Path.Combine(_iconPath, olympicEvent.IconFileName), olympicEvent.Icon.Bytes);
}
context.OlympicEvents.Add(olympicEvent);
context.SaveChanges();
}
}
private void LogErrorRecursive(OfficeOlympicsDbEntities context, Exception ex)
{
var error = new Error();
error.Type = ex.GetType().ToString();
error.HResult = ex.HResult;
error.Message = ex.Message;
error.Source = ex.Source;
error.StackTrace = ex.StackTrace;
error.TimeStamp = DateTime.Now;
context.Errors.Add(error);
if (ex.InnerException != null)
{
LogErrorRecursive(context, ex.InnerException);
}
}
public IEnumerable <Record> GetRecordsByEventId(int eventId, bool onlyBestForCompetitors)
{
using (var context = new OfficeOlympicsDbEntities())
{
IEnumerable <Record> records = (from record in context.FullRecords()
where record.OlympicEventId == eventId &&
record.OlympicEvent.IsActive &&
record.Competitor.IsActive
orderby record.Score descending,
record.DateAchieved
select record).ToList();
if (onlyBestForCompetitors)
{
records = records.UniqueConstraint(obj => obj.CompetitorId);
}
return(records);
}
}
public int?TopThreePositionOfNewScore(int eventId, int score, int competitorId)
{
using (var context = new OfficeOlympicsDbEntities())
{
var topThreeRecords = (from r in context.Records
where r.OlympicEventId == eventId &&
r.OlympicEvent.IsActive &&
r.Competitor.IsActive
orderby r.Score descending
select new
{
CompetitorId = r.CompetitorId,
Score = r.Score
}).UniqueConstraint(r => r.CompetitorId).Take(3);
int?scorePosition = topThreeRecords.FirstIndexWhere(r => r.CompetitorId == competitorId && r.Score == score) + 1;
int?scoreFuturePosition = topThreeRecords.FirstIndexWhere(r => score > r.Score) + 1;
return(scorePosition ?? scoreFuturePosition);
}
}
