我需要将这个SQL语句转换为LINQ:
SELECT f.ID as IdFlight,
Tarif * 1 as Tarif,
f.Time, f.TimeOfArrival,
sl.Name as FromLoc,
sl.Country as FromCountry,
sl.Airport as FromAirport,
dl.Name as ToLoc,
dl.Country as ToCountry,
dl.Airport as ToAirport
FROM Flights as f
INNER JOIN Locations as sl ON sl.ID = f.ID_Source
INNER JOIN Locations as dl ON dl.ID = f.ID_Destination
INNER JOIN FlightsTarifs as ftf ON f.Id = ftf.IDFlight
WHERE f.ID_Destination =30005 AND f.Time <= DATEADD(day,4,'2018/05/24 00:00')
AND f.Time >= '2018/05/24 00:00' ORDER By f.Time, Tarif
我在 Linq 中的尝试:
IQueryable qinfo = from f in context.Flights
join sl in context.Locations on f.Id_Source equals sl.ID
join dl in context.Locations on f.Id_Destination equals dl.ID
join ftf in context.FlightsTarifs on f.ID equals ftf.IDFlight
where (f.Id_Source == aFormUser.FlightSrcID)
where (f.Id_Destination == aFormUser.FlightDestID)
where (f.Time.Date >= aFormUser.DepartureDate.Date)
where (f.Time.Date <= aFormUser.DepartureDate.Date.AddDays(4))
orderby f.Time, ftf.Tarif
select new {f.ID, ftf.Tarif, f.Time, f.TimeOfArrival,
sl.Name, sl.Country, sl.Airport,
dl.Name, dl.Country, dl.Airport };
我现在有一些问题需要解决:
- 由于我在将航班表与位置表两次连接,以获取出发地和目的地位置的名称,在 LinQ 中这样做会导致编译器错误。错误信息显示 dl.Name、dl.Country、dl.Airport 是匿名类型,并且它们将与 sl.Name、sl.Country、sl.Airport 相同。
- 我不能像 SQL 中那样使用 "As" 表达式,LinQ 中是否有等价的表达式?
- 在 LinQ 查询中无法将 Tarif 乘以旅客数量,因为它不允许我这样做。