Linq中此SQL语句的等效项是什么? [英] What is the equivalent of this SQL statement in Linq?

问题描述

我需要将此SQL语句移植到LINQ:

I need to port this SQL statement to 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中的尝试:

My attempt in 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  };

我现在要解决一些问题:

I have some problems to solve now:

1. 由于我要两次将具有表位置的表航班加入，为了获取源位置和目标位置的名称，因此在LinQ中执行此操作会导致编译器错误，即dl.Name，dl.Country，dl，机场是匿名类型，它们的结尾将与其他名称相同.sl.Name，sl.Country，sl.Airport.
2. 我不能像在Sql中那样使用"As"表达式，或者在Linq中是否有任何等效表达式?
3. 在linq查询中，我无法将Tarif乘以乘客人数，但不允许这样做.

推荐答案

您可以将new对象初始化程序的别名与下面的代码一起使用，该代码还将支持将tarif乘以

You can use the aliases with the new object initializer with the code below, which will also support multiplying the tarif:

select new {
    f.ID,
    Tarif = ftf.Tarif * 1, // Alias and multiply by your number
    f.Time,
    f.TimeOfArrival,
    SourceName = sl.Name, // Alias
    SourceCountry = sl.Country, // Alias
    SourceAirport = sl.Airport, // Alias
    DestName = dl.Name, // Alias
    DestCountry = dl.Country, // Alias
    DestAirport = dl.Airport // Alias
};

只是提供更多细节，以防其他人偶然发现，根本原因是代码使用new关键字定义了一个带有对象初始值设定项的匿名类型，该初始值设定项遇到了尝试定义匿名数的多个冲突类(具有相同推断名称的多个属性，然后在乘以tarif后无法从表达式中命名属性).

Just to provide a few more details in case others stumble on this, the root cause is that the code was using the new keyword to define an anonymous type with an object initializer that ran into multiple conflicts trying to define the anonymous class (multiple properties with same inferred name, and then unable to name property from expression when tarif was multiplied).

通过显式命名具有冲突的属性，编译器不再需要推断产生冲突的命名.

By explicitly naming the properties with conflicts, the compiler no longer had to infer the naming that generated the conflicts.

更多: http://geekswithblogs.net/BlackRabbitCoder/archive/2012/06/21/c.net-little-wonders-the-joy-of-anonymous-types.aspx

上面的链接提供了一些其他示例，说明如何将对象初始化器与匿名类型一起使用.

The link above has some additional examples on how to use the object initializer with anonymous types.

这篇关于Linq中此SQL语句的等效项是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！