SQL query to LINQ INNER JOIN

Question

I need to translate SQL query to LINQ and have no idea how. I have two tables: Bins and DataFromBins. DataFromBins contains column BinId which refers to Bins.Id What my query does is selecting most recent row for each BinId from DataFromBins and joining some data from Bins for these BinIds.

Please help :(

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

I guess you are seeking the code below. The trick is to split your subqueries ;)

suppose _db is your context.

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
            }