使用嵌套选择语句的SQL查询到LINQ转换

Question

我想将以下查询转换为LINQ:

 SELECT LV.* FROM LowerVehicles LV
 INNER JOIN (Select VSerial,MAX(updatedOn) MaxUpdatedOn from LowerVehicles group by vserial) LVG
 ON LV.VSerial = LVG.VSerial AND LV.updatedOn = LVG.MaxUpdatedOn

Answer 1

不知道你的实体类，这里是一个近似。您可以使用查询语法或流畅语法。有时一种比另一种更可取，在连接和分组的情况下，我更喜欢使用查询语法。

查询语法

var query = from LV in LowerVehicles
        join LVG in (
        from r in LowerVehicles
        group r by r.vserial into g
        select new {VSerial = g.Key, MaxUpdatedOn = g.Max(t => t.updatedOn)})
        on LV.VSerial equals LVG.Vserial
        and LV.updatedOn equals LVG.MaxUpdatedOn
        select LV;

流利语法

var lvg = LowerVehicles.GroupBy(t => t.vserial)
                       .Select(g => new { 
                                           VSerial = g.Key, 
                                           MaxUpdatedOn = g.Max(t => t.updatedOn) 
                                        });
var query = LowerVehicles.Join( 
                                lvg, 
                                a => new { a.VSerial, a.updatedOn }, 
                                b => new { b.VSerial, b.MaxUpdatedOn }, 
                                (a, b) => new { LV = a, LVG = b}
                              )
                         .Select(t=> t.LV);

Answer 2

像这样?

Something.LowerVehicles
.Join(something.LowerVehicles.Select(y => new { y.VSerial, updatedOn = y.updatedOn.Max() }).GroupBy(z => z.VSerial), 
 x => new { x.VSerial, x.updatedOn }, 
 lvg => new { lvg.VSerial, lvg.updatedOn }, 
 (x, y) => x)