SQL query to LINQ INNER JOIN

Question

我需要将SQL查询转换为LINQ，但不知道如何。我有两个表：Bins和DataFromBins。DataFromBins 包含列 BinId，它指的是 Bins.Id我的查询所做的是从 DataFromBins 中选择每个 BinId 的最新行，并为这些 BinId 连接来自 Bin 的一些数据。

请帮忙:(

SELECT BinId, Address, Lon, Lat, MaxFillLevel, Distance
FROM (
    SELECT DataFromBins.*
    FROM (
        SELECT DataFromBins.BinId, MAX(DataFromBins.Date) AS Date
        FROM DataFromBins
        GROUP BY DataFromBins.BinId
    ) AS latest_records
        INNER JOIN DataFromBins ON DataFromBins.BinId = latest_records.BinId
            AND DataFromBins.Date = latest_records.Date
) AS most_recent
    INNER JOIN Bins ON most_recent.BinId = Bins.Id

Answer 1

我猜你正在寻找下面的代码。诀窍是将子查询拆分;)

假设_db是你的上下文。

var latestRecords = from t in _db.DataFromBins
               group t by t.BinId into g
                    select new
                    {
                        BinId = g.Key,
                        Date = (from t2 in g select t2.Date).Max()
                    };
var mostRecents = from itm in latestRecords
            join bin in _db.DataFromBins on new {BinId = itm.BinId,  Date =itm.Date  } equals new {BinId = bin.BinId ,  Date =bin.Date}
            select bin;
var finalQuery = from recent in mostRecents
            join bin _db.Bins on recent.BinId  equals bin.Id
            select new {
            bin.BinId,bin.Address, bin.Lon, bin.Lat, bin.MaxFillLevel, bin.Distance
            }