public static List <results> GetResultsInRace(int id, ModelF1 db)
{
var q = from res in db.results
where res.raceId == id
select res;
return(q.ToList());
}
public static List <races> GetRacesByConstructorId(int id, ModelF1 db)
{
return(db.races.Join(db.results,
ra => ra.raceId,
re => re.raceId,
(ra, re) => new { ra, re.constructorId })
.Where(x => x.constructorId == id).Select(x => x.ra).Distinct()
.OrderBy(x => x.year).ThenBy(x => x.round).ToList());
}
public static races GetRaceByID(int?id, ModelF1 db)
{
if (id == null)
{
return(null);
}
return(db.races.Where(x => x.raceId == id).FirstOrDefault());
}
public static List <drivers> GetDriversInRace(int id, ModelF1 db)
{
var q = from dri in db.drivers
join res in db.results
on dri.driverId equals res.driverId
where res.raceId == id
select dri;
return(q.ToList());
}
public static List <constructors> GetConstructorsInRace(int id, ModelF1 db)
{
var q = (from res in db.results
join con in db.constructors
on res.constructorId equals con.constructorId
where res.raceId == id
select con).Distinct();
return(q.ToList());
}
public static lapTimes GetTrackRecordCircuit(int id, ModelF1 db)
{
var q = from lap in db.lapTimes
join rac in db.races
on lap.raceId equals rac.raceId
join cir in db.circuits
on rac.circuitId equals cir.circuitId
where cir.circuitId == id
orderby lap.milliseconds
select lap;
return(q.FirstOrDefault());
}
public static int GetSeasonsCount(ModelF1 db, int id)
{
var s = String.Format(
@"select count(*)
from driverStandings
inner join (
select max(raceId) as raceId, year
from races
group by year
) as A on driverStandings.raceId = A.raceId
where driverId = {0}"
, id);
return(db.Database.SqlQuery <int>(s).FirstOrDefault());
}
public static drivers GetDriverChampionByYear(int year, ModelF1 db)
{
var s = String.Format(
@"select d.*
from driverStandings as ds
join (
select MAX(r.raceId) as max
from races as r
where r.year = {0}
) as s
on ds.raceId = s.max
join drivers as d
on ds.driverId = d.driverId
where ds.position = 1"
, year);
return(db.Database.SqlQuery <drivers>(s).FirstOrDefault());
}
public static int GetFastestLapCount(ModelF1 db, int id)
{
var s = String.Format(
@"select count(*)
from (
select MIN(time) as 'fastest', raceId
from lapTimes
where driverId = {0}
group by raceId
) as mine
inner join(
select MIN(time) as 'fastest', raceId
from lapTimes
group by raceId
) as whole
on mine.fastest = whole.fastest"
, id);
return(db.Database.SqlQuery <int>(s).FirstOrDefault());
}
public static int GetRacesCount(ModelF1 db, int driverId)
{
return(db.results.Where(x => x.driverId == driverId).Count());
}
public static seasons GetSeasonByYear(int year, ModelF1 db)
{
return(db.seasons.Where(x => x.year == year).FirstOrDefault());
}
public static List <qualifying> GetQualyByRaceId(int id, ModelF1 db)
{
return(db.qualifying.Where(x => x.raceId == id).OrderBy(x => x.position).ToList());
}
public static List <races> GetRacesByConstructorId(string id, ModelF1 db)
{
return(GetRacesByConstructorId(db.constructors.Where(x => x.constructorRef == id)
.Select(x => x.constructorId).FirstOrDefault(), db));
}
public static constructors GetConstructorsById(string reference, ModelF1 db)
{
return(db.constructors.Where(x => x.constructorRef == reference).FirstOrDefault());
}
public static List <status> GetStatusesInRace(List <results> results, int id, ModelF1 db)
{
var q = (from res in results
join sta in db.status
on res.statusId equals sta.statusId
where res.raceId == id
select sta).Distinct();
return(q.ToList());
}
